Approximate vs exact match in Google Sheets - Compared

Introduction

When you look up data in Google Sheets, you can choose to find an exact match or an approximate match. This helps you get the right result whether you want a perfect match or the closest one.

When you want to find a product price exactly matching a product code.

When you want to find the closest grade for a test score in a grading scale.

When you want to match a name exactly in a list of employees.

When you want to find a tax rate based on income ranges using approximate match.

When you want to check if a specific date exists in a list of holidays.

Steps

Step 1: Click

- cell where you want the lookup result

The cell is selected and ready for formula input

Step 2: Type

- the selected cell

Formula input starts

💡 Start with an equal sign (=) to enter a formula

Step 3: Enter

- the formula bar or cell

Formula is typed, for example: =VLOOKUP(A2, B2:C10, 2, FALSE)

💡 Use FALSE for exact match, TRUE or omit for approximate match

Step 4: Press

- Enter key

The formula runs and shows the lookup result in the cell

Step 5: Change

- the last argument in the formula (TRUE or FALSE)

The lookup switches between approximate and exact match

Before vs After

Before

Formula =VLOOKUP(1001, A2:B10, 2) returns price for closest product code less than or equal to 1001

After

Formula =VLOOKUP(1001, A2:B10, 2, FALSE) returns price only if product code 1001 exists exactly, otherwise #N/A

Settings Reference

Range_lookup argument

📍 Last argument in VLOOKUP or HLOOKUP formula

Determines if the lookup finds an approximate match (TRUE) or exact match (FALSE)

Default: TRUE

Common Mistakes

Using approximate match (TRUE) without sorting the lookup column

Approximate match requires the lookup column to be sorted ascending to work correctly

Sort the lookup column ascending before using approximate match or use exact match (FALSE) if sorting is not possible

Omitting the last argument in VLOOKUP and expecting exact match

Omitting the last argument defaults to approximate match, which may return wrong results if data is not sorted

Always specify FALSE for exact match to avoid unexpected results

Summary

Approximate match finds the closest value less than or equal to the lookup value and needs sorted data.

Exact match finds the value exactly equal to the lookup value and returns an error if not found.

Specify TRUE for approximate match or FALSE for exact match in the last argument of lookup formulas.