How to Use VLOOKUP with Approximate Match in Excel

Assign sales reps to bonus tiers with VLOOKUP TRUE, calculate their bonus, and chain an exact and an approximate lookup.

You will learn to

• Return a tier name with VLOOKUP’s TRUE mode
• Calculate a bonus from an approximate lookup
• Nest an exact VLOOKUP inside an approximate one

How it works

With TRUE (or the fourth argument left out), VLOOKUP does an approximate match: it finds the largest value in the first column that is less than or equal to the lookup value. In a tier table that starts at 0, 10,000, 25,000 and so on, =VLOOKUP(18400,E2:G6,2,TRUE) returns the tier that begins at 10,000.

The first column must be sorted ascending, or VLOOKUP can silently return the wrong tier. To find a rep’s tier by name, nest an exact VLOOKUP (FALSE) for their sales inside the approximate one.

=VLOOKUP(B2,E2:G6,2,TRUE)