My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel Formulas / Excel VLOOKUP – Sorted List Explained
vlookup sorted list formula
by

In my previous Excel VLOOKUP formula tutorial I mentioned 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.

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.

Note: If you haven’t read the first tutorial, then I recommend you first watch the video below from the beginning to get an understanding of how VLOOKUP works.

Excel VLOOKUP Formula Video Tutorial

Subscribe YouTube

Download the Workbook

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.

Excel VLOOKUP - Approximate Match on a Sorted List

First let’s set the scene:

In the image below we want to lookup the Commission Rates table in cells G6:I13, and find the rate in column I based on the sales values in column D, and return the result to column E.

vlookup sample data

Excel VLOOKUP Function 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 the nth column of the table,  find an exact match if you can, but if not, find the next lowest match)

Note: with the Sorted List version we want Excel to find the next closest option in our table, i.e. an approximate match. To specify this we can leave the 'range_lookup' argument blank, or enter TRUE, or 1.

Excel VLOOKUP approximate match formula example:

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

=VLOOKUP(find where the Sales amount $3,112, falls in the Commission Rates table G6:I13, return the value in column 3 of the table,  if there isn't an exact match, find the next closest value)

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

=VLOOKUP(D6,$G$6:$I$13,3, TRUE)

Excel VLOOKUP approximate match formula

Let me clarify some points:

1)      ‘find where Sales 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 G6:I13, 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 consecutive 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 G11, 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. 

Want More?

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

vlookup sorted list formula

More Vlookup Posts

vlookup in power query using list functions
Use List functions to do VLOOKUPS in Power Query as an alternative to merging tables. Sample data and file available to download
VLOOKUP Multiple Values in Multiple Columns
Excel VLOOKUP Multiple Sheets
Excel Factor 17 Lookup and Return Multiple Matches
Excel Factor 16 Dynamic Lookup
VLOOKUP Multiple Criteria
Excel VLOOKUP Multiple Values
Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP
Excel VLOOKUP with Dynamic Column Reference
Excel VLOOKUP with Dynamic Column Reference allows you to copy the formula across columns and have the column reference automatically update.
Excel VLOOKUP to the Left Using CHOOSE
VLOOKUP to the left with this clever use of CHOOSE to trick Excel into looking up a column to the left.

More Excel Formulas Posts

ai-aided excel formula editor
Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts
Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment
Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips
Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays
The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do
Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function
The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files
New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel
How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions
TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Tag:

Reader Interactions

Comments

  2. John Brewster

    I have downloaded the Excel workbook, I haven’t practised on it as yet as I have not had the time but I will do and I look forward to practising on the workbook. I am waiting to go back to college and your tutorials will extend my skills from what I already know from the college, skills like Power Query which I find easier than complicated formulas like VBA. I will get back to you for a comment though.

    Reply

  4. Diya Rao

    Loved it. Well explained, way to go. I would like to know ore about data extraction for approximate matches using lookup function.

    Reply

    • Mynda Treacy

      Hi Diya,

      Glad you found it useful.

      If you have a specific example of your approximate match question please post it on our Excel forum along with your sample Excel file where we can help you further.

      Kind regards,

      Mynda

      Reply

  5. Manish Mandal

    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 !!!!!!!!

    Reply

  6. Cindy

    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?

    Reply

    • Catalin Bombea

      Hi Cindy,
      Yes, of course it’s possible, if you get stucked in this process, let us know and we’ll help you. You can upload a sample workbook on our Help Desk.
      Catalin

      Reply

  7. Phil Reinemann

    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).

    Good job!

    Reply

    • Mynda Treacy

      Cheers, Phil. Glad you like the English translations 🙂

      Well spotted; H2:I9 should be G3:H10, I’ve fixed it now. Thanks for pointing it out.

      Kind regards,

      Mynda.

      Reply

  8. Jay Sadhu

    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 !!!!!!!!

    Reply

  9. Patsiltri

    I love this site, use it lots.

    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.

    Reply

    • Carlo Estopia

      Hi Patsiltri,

      Precisely, you can use indirect function to materialize this.

      Try to download the workbook in this particular blog and look for the SUMIF part:

      3D SUMIF Across Multiple Workbooks

      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:

       =VLOOKUP(A4,INDIRECT("'"&tabs&"'!A2:B2"),2,FALSE)

      Cheers,

      CarloE

      Reply

  11. Dave

    I need to create a vlookup (or “If” / “Or” statement) that only looks for values that are >229.0 or (but) 870 but less than 876.00. A sorting function.

    Thanks

    Reply

  13. Joseph Horling

    Hi,

    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.

    Reply

Trackbacks

Leave a Reply

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

Current ye@r *