Lookup formula page

VLOOKUP Approximate Match Formula

Use approximate VLOOKUP only when the first column contains sorted breakpoints and you want the nearest lower match.

Best for

Build a VLOOKUP approximate match formula for sorted tiers.

What it returns

If F2 is 850 and the first column contains sorted tiers, the formula returns the matching tier label.

Copy formulas

Excel formula

=VLOOKUP(F2, A2:B100, 2, TRUE)

Google Sheets formula

=VLOOKUP(F2, A2:B100, 2, TRUE)

Excel / Google Sheets difference

Excel and Google Sheets use the same TRUE approximate-match syntax. In both apps, the first table column must be sorted from smallest to largest.

What it returns

If F2 is 850 and the first column contains sorted tiers, the formula returns the matching tier label.

F2 is compared against the first sorted column.
TRUE enables approximate match.
The formula returns the value from the second column for the nearest lower breakpoint.

Syntax piece	Role in the formula
F2	The score or amount to classify against the ordered breakpoints.
A2:B100	A two-column tier table whose first column is sorted in ascending order.
2	Returns the tier label from the second column of the table.
TRUE	Returns the row for the largest breakpoint that is less than or equal to F2.

Score between two breakpoints

=VLOOKUP(F2, A2:B100, 2, TRUE)

Excel: Enter 850 in F2; the ordered minimum scores are 0, 500, 800, and 1000. Returns: Premium

Score below the second breakpoint

=VLOOKUP(F2, A2:B100, 2, TRUE)

Google Sheets: Enter 200 in F2 while the first two breakpoints are 0 and 500. Returns: Basic

Issue	Likely cause	Fix
A score receives the wrong tier	The breakpoint column is not sorted ascending, so approximate matching cannot find a reliable boundary.	Sort the Minimum Score column from low to high before using TRUE.
A low score returns #N/A	The score is lower than the smallest breakpoint in the table.	Add a genuine minimum row such as 0, or handle below-minimum values with IFERROR when that rule is intended.
A product ID returns a nearby product	Approximate match was used for a key that requires exact equality.	Use FALSE for SKUs, IDs, names, and other unsorted identifiers.

Do not use approximate VLOOKUP for unsorted identifiers or whenever only an exact key is acceptable.

Alternative	When to use it
VLOOKUP Exact Match Formula	Use for IDs or labels that must match one row exactly.
XLOOKUP Formula Builder	Use when you need explicit exact, next-smaller, or next-larger match behavior.

Does an approximate VLOOKUP require an exact score?

No. A score of 850 uses the 800 breakpoint and returns Premium because 800 is the largest minimum not greater than 850.

Why must the first column be sorted?

TRUE relies on ordered breakpoints to find the nearest lower boundary. An unsorted first column can return an incorrect tier.