• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel VLOOKUP – Sorted List Explained

You are here: Home / Excel Formulas / Excel VLOOKUP – Sorted List Explained
vlookup sorted list formula
September 22, 2010 by Mynda Treacy

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.
Please enter a valid email address.

Download the Excel workbook used in this example so you can practice to make sure you really get it and can take advantage of its power. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

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

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

VLOOKUP Multiple Values in Multiple Columns

Excel VLOOKUP Multiple Sheets

Excel VLOOKUP Multiple Sheets

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

VLOOKUP Multiple Criteria

VLOOKUP Multiple Criteria

Excel VLOOKUP Multiple Values

Excel VLOOKUP Multiple Values

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Excel Wildcards in your SUMIF, COUNTIF and VLOOKUP

Excel VLOOKUP with Dynamic Column Reference

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

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

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

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

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

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

New Array Shaping Excel Functions

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

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

Excel VSTACK and HSTACK Functions

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

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

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.


Category: Excel FormulasTag: vlookup
Previous Post:vlookup formulaExcel VLOOKUP Formulas Explained
Next Post:Excel Absolute References $ – The Missing Link

Reader Interactions

Comments

  1. Rod Leung

    November 30, 2019 at 2:39 pm

    The tutorials are very helpful.

    Reply
    • Mynda Treacy

      November 30, 2019 at 8:11 pm

      Glad we can help, Rod 🙂

      Reply
  2. John Brewster

    April 20, 2019 at 9:48 pm

    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
  3. Rick Chrysler

    June 26, 2018 at 1:30 am

    Very helpful

    Reply
  4. Diya Rao

    April 15, 2018 at 2:38 am

    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

      April 15, 2018 at 7:27 am

      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

    September 12, 2015 at 11:40 pm

    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
    • Mynda Treacy

      September 13, 2015 at 10:32 am

      Glad you found it helpful, Manish.

      Reply
  6. Cindy

    May 20, 2014 at 6:29 am

    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

      May 20, 2014 at 1:46 pm

      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

    April 9, 2014 at 11:57 pm

    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

      April 10, 2014 at 5:16 am

      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

    July 17, 2013 at 2:44 pm

    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
    • Mynda Treacy

      July 17, 2013 at 4:49 pm

      You’re welcome, Jay 🙂

      Reply
  9. Patsiltri

    June 14, 2013 at 5:15 am

    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

      June 14, 2013 at 6:27 pm

      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
  10. Enoch

    May 22, 2013 at 7:12 pm

    Wow….this is new discovery for me.
    Thanks

    Reply
    • Mynda Treacy

      May 22, 2013 at 7:48 pm

      Fantastic!

      Reply
  11. Dave

    April 26, 2013 at 3:50 am

    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
    • Carlo Estopia

      April 26, 2013 at 10:18 am

      Hi Dave,

      I don’t think this can be done by a Vlookup function.

      Cheers,

      CarloE

      Reply
  12. sartaj

    February 2, 2013 at 7:41 pm

    Thanks for sharing the usefull with all of us,………
    It is very helpful.
    Thanks once again.

    Reply
    • Mynda Treacy

      February 2, 2013 at 8:32 pm

      Thanks, Sartaj 🙂

      Reply
  13. Joseph Horling

    January 16, 2013 at 4:20 am

    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
    • Philip Treacy

      January 16, 2013 at 9:21 am

      Thanks. Glad you like it, Joseph 🙂

      Reply
  14. Brian Nyanzi

    September 26, 2012 at 9:40 pm

    it is a good resource

    Reply
    • Mynda Treacy

      September 26, 2012 at 10:13 pm

      Cheers, Brian 🙂

      Reply
  15. taimoor

    September 15, 2012 at 10:38 pm

    Best of luck dear. Your this addition is helpful to me. as my job is relating to such condition VLOOKUP

    Reply
    • Mynda Treacy

      September 15, 2012 at 10:41 pm

      Thanks, Taimoor.

      Reply
  16. Eskinder Haile

    August 18, 2012 at 2:31 am

    This is a good tutorial for those of us who want to understand Vlookup

    Reply
    • Mynda Treacy

      August 19, 2012 at 9:41 pm

      Thanks, Eskinder 🙂

      Reply
  17. Eric Juliana

    August 14, 2012 at 3:33 am

    Hopi bon,

    Very good!!!!

    Reply
    • Mynda Treacy

      August 14, 2012 at 9:03 am

      🙂 Cheers, Eric.

      Reply
  18. ASGHAR

    June 23, 2012 at 8:08 pm

    I am a regular reader and as usual I find this article bearing much and useful information

    Reply
    • Mynda Treacy

      June 24, 2012 at 11:29 am

      Thanks, Asghar 🙂

      Reply
  19. niyaz

    April 24, 2012 at 6:18 pm

    good

    Reply
  20. Roy Lachica

    October 23, 2011 at 1:59 pm

    This is such a good reference / resource for my office work which involve large data… thanks…

    Reply
    • Mynda Treacy

      October 23, 2011 at 9:42 pm

      Thanks Roy. Glad to help.

      Reply
  21. dtg

    December 12, 2010 at 8:11 am

    this site is fast

    Reply
  22. Fawaz

    November 14, 2010 at 11:18 pm

    Finally i found it as a great method to learn excel here.

    Thanks a lot to the organizers.

    Reply
  23. Craig Forrester

    November 12, 2010 at 3:39 pm

    thanks, most useful to me

    Reply
  24. Andy G

    October 28, 2010 at 2:46 pm

    great stuff. I think I understand vlookup now 🙂

    Reply
  25. roclafamilia

    October 21, 2010 at 11:01 pm

    Helpful blog, bookmarked the website with hopes to read more!

    Reply
  26. hmm...?

    October 13, 2010 at 11:21 pm

    thanks

    Reply
  27. Reg

    October 5, 2010 at 1:50 am

    This is such a great resource that you are providing

    Reply
    • Philip Treacy

      October 5, 2010 at 12:37 pm

      Thanks Reg. we really do hope people get a lot from our training. Spread the word 🙂

      Reply

Trackbacks

  1. Talking Glucose Meter says:
    September 28, 2010 at 12:41 pm

    […] Excel VLOOKUP Sorted List […]

    Reply
  2. Excel VLOOKUP formulas explained says:
    September 22, 2010 at 12:58 pm

    […] Update: The second way to use a VLOOKUP formula is what I call the Sorted List method.  Click here to go to my tutorial on the VLOOKUP Sorted List Method. […]

    Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. 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.