Tutorial: How to use a vlookup where the range_lookup is true (rather than looking for an exact value, looking for a range)

By | March 14, 2015

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


vlookup range

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.

sales bonus list vlookup example

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

Leave a Reply

Your email address will not be published. Required fields are marked *