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 then read it first to get an understanding of how a VLOOKUP works.

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

Excel VLOOKUP - Sorted List

First let’s set the scene:

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.

VLOOKUP syntax:

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 version 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 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 G3:I10, 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.

How can we make this formula even better?

As with our 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.

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.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

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. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Comments

Thanks for sharing the use full with all of us,………
It is very helpful. it’s very helpful in office work & i can proud for it that i knows batter this other then my friends group, now i will be regular visitor of your site,,,,, Thanks Again !!!!!!!!

Love your examples…make it easy for new users like me.
Q. I have a workbook with 27 tabs(sheets)I could use help with, please!
I have a tab for each letter of the alphabet that lists names, addresses, etc. and a column with conditional formatting returning a ‘Y’ if true. I would like the 27th tab (called “Mail List”) to be a list automatically created with the ‘Y’ value rows from the other 26 tabs.
At this point I’ve got one tab set up but it returns the ‘Y’ value row to the corresponding row on the “Mail List”(i.e. if the 9th row on tab “A” has the ‘Y’ value, it copies the info to the 9th row on the “Mail List”.) I need the “Mail List” to fill in from top to bottom no matter what line or tab it came from.
Is this possible? Is it easy enough for a new user like me?

In your clarification #2 you say “table H2:I9”. Did you mean table G3:I10 as specified in the formula “$G$3:$I$10”?

Also, I do like your use of the “so in English our formula will read” sections, along with the colorization “alignment” of the parts of the formula, carried from the Excel formula pop-up, to the English, to the actual formula. They help shed more light on what many people will find is lawyer-eze (any legal or financial document when read by a real person).

hey Dear Mynda Treacy,,, Thanks alot for this wonderful contribution to my life ,it’s vry helpful in office work & i can proud for it that i knows batter this other then my office staff & my freinds group ,,,,now i will be regular visiter of your site,,,,,Again thanks alot Have fun !!!!!!!!

Q; I like to have a summary sheet that calculates totals from several tabs in the schedule. The tabs are named: one, two, three, etc. Is there a way to use the name of the tab in formulas like vlookup and sumif? That way I can put the tab names in the summary and use it as a reference in the summary tab formula.

An excerpt from the sheet, SUMIF: =SUMPRODUCT(SUMIF(INDIRECT(“‘”&tabs&”‘!A3:A8”),$A6,INDIRECT(“‘”&tabs&”‘!E3:E8”))) .

Note: the tab names are contained in the named range ‘tabs’.

However, I don’t know how you would like to do it in a VLookup. Although it’s still possible to use indirect function together
with a VLookup function but it doesn’t make any difference at all. You may want to use this syntax/formula in the same sheet of
the downloadable file above:

Just wanted to comment that your online training hub is really helping me in reviewing excel. Thanks so much for the simple explainations and the downloads to practice. And keeping it for free. Joe for Michigan.

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?