Excel VLOOKUP – Sorted List Explained

In my previous Excel VLOOKUP tutorial I told you that there are two ways you can use a VLOOKUP but most people know one way or the other, and only a few know both.

If you haven’t read the first article in the VLOOKUP series then read it first to get an understanding of how a VLOOKUP works.

As promised here’s the second way to use a VLOOKUP, and I call it the Sorted List version as it relies on the data in the table you are referencing being sorted.

Let’s s set the scene so we can understand how this type of VLOOKUP can help us.

In the list below we want to calculate a commission in column E for each builder.  Our commission percentage is based on where the ‘Total $k’ figure falls into the ranges in our table in columns G-I.

Microsoft Excel describes the VLOOKUP formula as:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

And to translate it into English it would read:

VLOOKUP(find this value, in that table, return the value in column x of the table)

You’ll notice I haven’t translated the last part of the formula ‘range_lookup’.  Unlike the VLOOKUP Exact Match formula where we put ‘False’ in this position, with the Sorted List version we want Excel to find the next best option in our table, and so we leave ‘range_lookup’ blank.

Let’s make it even clearer by applying it to row 3 in our example:

Remember we want Excel to find the Commission % Rate and enter it in cell E3, so in English our formula will read:

VLOOKUP(find where Total $k amount $3,112, falls in the Commission Rates table G3:I10, return the value in column 3 of the table)

And to enter it in our spreadsheet our VLOOKUP formula in column E for the above example would be:

=VLOOKUP(D3,$G$3:$I$10,3)

Let me clarify some points:

1)      ‘find where Total $k amount $3,112, falls in the Commission Rates table’ – Excel doesn’t actually take into consideration column H in our table.  I have simply put it there to help understand the commission ranges.  Excel is in fact looking for the exact amount $3,112 in our Commission Rates table, and when it can’t find it, it finds the next best lower amount and returns the value in column 3.

2)      ‘Return the value in column 3 of the table’ is referring to the column number in the table H2:I9, not the column number of the spreadsheet.  The information we want returned is the percentage rate, and it is in the third column of the Commission Rates table.

3)      If we had duplicates in our Commission Rates table Excel will find the last instance of the value and return the result in column 3.  For example, if instead of the amount $4001 in cell G8, you had $3001 again.  Excel would return the value of 6% as it’s finding the last best match for our amount.  The tip here is to remove any duplicates or you’ll end up with erroneous results.

4)      Unlike the VLOOKUP Exact Match version of the formula, this version requires the list to be sorted in ascending order.  Just like with duplicates explained above, if it’s not sorted you will end up with erroneous results.

You’ll notice in the formula bar above there are ‘$’ signs around the reference to the table.  This is called an absolute reference and it allows us to copy the formula down column E without Excel dynamically updating the table range as we copy.  To understand how to use shortcuts like absolute references you can get our Microsoft Office online training here.

How can we make this VLOOKUP formula even better?

As with our VLOOKUP Exact Match example let’s assume the end result of our example exercise is to calculate the commission $ amount.  Again we can do this in one step in column E.

Let’s say our commission is calculated as Total $k x Commission %, our formula in cell F3 would read:

=VLOOKUP(D3,$G$3:$I$10,3)*D3

Hopefully now you can begin to appreciate why the VLOOKUP is my favourite formula.  The concept behind it is pretty basic but it has amazing POWER.  Especially when you team it up with other formulas like IF statements, SUMIF and so on.

Download the Excel workbook used in this example so you can copy and practice the different VLOOKUP formulas to make sure you really get it and can take advantage of their power.

Check out my previous tutorial for VLOOKUP Rules & Common Mistakes!

Tell us how you use VLOOKUP formulas in your work by entering a comment below.

Did you find this useful?  Why not share the love and let your friends know.  Click the icons below for Twitter and Facebook.

Want more? Sign up for our newsletter below and receive weekly tips & tricks to your inbox, plus you’ll get our 100 Excel Tips & Tricks e-book.

Tagged as: vlookup