Sometimes you do not actually want an exact match on a VLOOKUP….really?….

I know most times when using VLOOKUPS we do, want that exact match, but what if we want to look up the correct commission rate or tax rate, based upon what earnings or commissions band a particular value falls into?.

This is easily handled by the VLOOKUP formula, once you understand the last optional argument.

The syntax of the VLOOKUP is

=VLOOLKUP(lookupvalue,lookuprange,columntoreturn,TRUE or FALSE)

Most times we automatically hit FALSE or 0, whichever you prefer to get an exact match. But this method does not work if we want to lookup the correct percentage sales commission as the rate of commission changes depending on the size of the income figures, so how do we calculate what percentage rate to use.

Of course we could use IF statements, but you would need to amend every time time the bands changed and why not use 1 formula- VLOOKUP?

First we set up the sales commission bands. 0 to 14999 pays out 5%, 15000 to 29999 pays out 10% 30000 and above pays out 20%. Banding of earnings in the left columns and % commission in the right.

Set up the list in ascending order of earnings. See below.

We can now go ahead and wrote our VLOOKUP formula, and when we get to set the final argument, set it to TRUE or you can also leave this blank.

Excel will scan the lookup to find the highest earnings value that is still less than the lookup value. Job Done.

Want to Learn More Formulas?

Join the Excel Formulas Crash CourseIf you want to learn SUMIFS, SUMPRODUCT, OFFSET and 40 other day to day formulas, then consider Chandoo’s Excel Formula Crash Course.

It has 31 lessons split in to 6 modules and makes you awesome in Excel formulas.