Tutorial: A vlookup for a range of values compared with looking for an exact value
Importantly, the lookup table (where you’re searching for the value) must be sorted in ascending order based on the leftmost column as in below
In this vlookup example, we have some thresholds on which salespeople’s bonuses will be based. For example, if the salesperson sells between $10,000 and $19,999 in the period then they’ll get a 10% bonus. Any less than $10,000 and they get no bonus.
In the yellow table (see below) we have the sales results for each salesperson at the end of a period and rather than having to work out what % bonus each person should get manually, we’re going to use a vlookup to save time.
It’s worth noting how the thresholds work – look at Chieko and Robyn in the vlookup example above. Chieko just missed the $10,000 mark by a dollar and got 0% while Robyn just made it ($10,001) and got 10%
Once again, I’ll just stress that the (blue) lookup table must be sorted from smallest to largest so that the vlookup can work!
Download the vlookup range example