• Approximate Matches With VLOOKUP

    The purpose of the VLOOKUP function is simple: it looks up data in tables and returns results from a different column. So if you have a table of products, for example, you could ask VLOOKUP to return the price for an item given the ID of the product.

    But VLOOKUP is more than just that; it is the gateway to real Excel knowledge. The VLOOKUP function contains everything that a function can throw at you: multiple required parameters, optional parameters with defaults, and needs both ranges and numeric data in its input strings. If you can master this function, you can master ANY other function in Excel.

    The VLOOKUP function syntax is as follows:

    And here’s what it means:
    Lookup_value: The value you want to look up. This can be either a value, text string or a cell range (the cells’ value will be used).
    Table_array: The table you would like to look the value up in. Be aware that VLOOKUP will check the first column in the table looking for the value you have provided.
    Col_index_num: What column of the table (not the worksheet) you’d like to return the value from when a match is found.
    [range_lookup]: Would you like to return an approximate match (TRUE) or an exact match (FALSE)? An approximate match returns what Excel interprets as the closest result to what you have looked up. An exact match returns #N/A unless the exact thing you’re searching for can be found. The default is true (approximate) if you do not specifically declare this parameter.

    Have a look at the following spreadsheet:



    Notice that we have a table of marginal tax rates here. Wouldn’t it be nice if we could feed the spreadsheet a tax balance, an income level and return the appropriate amount of tax to pay? Well, of course we can do this; accountants do it all the time. Here’s how we do it with VLOOKUP:

    Build the following formulas into the worksheet (And hey! Do it in the example above, it's a live Excel worksheet right in this page!):
    • F7 =VLOOKUP($F$5,$A$4:$C$10,1,TRUE)
    • F8 =VLOOKUP($F$5,$A$4:$C$10,2,TRUE)
    • F9 =VLOOKUP($F$5,$A$4:$C$10,3,TRUE)
    • F11 =F9+(F5-F7)*F8


    Notice the similarities and differences in the formulas:
    • In each case are looking up $F$5 (in this case 10,000)
    • All results are being looked up in the table covering $A$4:$C$10
    • Each formula ends with TRUE, indicating that we want the closest match
    • Only the third argument is different!


    The third argument tells us which column we want the value returned from for the match in column A. So in the first case, we find 10,000 on row 6, and we want the value from the first column in that row (i.e. 10,000). In the second formula, we’re looking at the next column over (column 2), which contains 10%. And naturally the third column contains 0.

    Try changing the value in F5 to 9,000 and you’ll see that all of the results change to 0. Why?

    With the TRUE argument at the end of the VLOOKUP function, the approximate match is discerned as the closest value without going over. (Kind of like the Price is Right!) In this case it makes sense, as there would be no tax payable if you made less than the 10,000 threshold. Change F5 to $15,000 thousand though, and you’ll see that it picks 10,000 as the base to look up.

    This highlights and important point when using approximate matches: Make sure that your data table is sorted in ascending order or the value returned may not be correct!

    One final word on using VLOOKUP to find an approximate match: you can omit the ,TRUE from the formula since it will default to TRUE. Don’t. Get in the habit of declaring the TRUE or FALSE so that you know your formula is doing what you intended.

    Now that you’ve made the approximate match work, change the value in F5 back to 10,000, then modify each of the VLOOKUP formulas to use FALSE instead of TRUE for their final argument. If you did it correctly, you shouldn’t see any difference.

    Now go to F5 and change the value to 9,000. All the VLOOKUPs recalculate with #N/A!

    The benefit of FALSE is that it tells you when a true match cannot be found. While not helpful for a tax table like we have here, this can be very handy when working with product or member lists. Think about a scenario where you put in a customer number to get their A/R balance. In this case, you’d far rather have a #N/A show up in your cell than to give the customer the wrong balance!

    VLOOKUP formulas can be tricky, especially when you are working with exact matches. If you’re having issues, review Microsoft’s quick reference card. It’s an excellent resource to help diagnose VLOOKUP errors!

    And, of course, you can always ask in our forums as well!

    This article was originally contributed to CMA Update Magazine - Spring 2011, and was republished here in anticipation of Mr Excel's:

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

  • MVP Logo
  • Recent Forum Posts

    Eflats1

    Auto fill web form

    Here is a copy of what the code looks like and also a screen shot




    Warranty/Guarantee...

    Eflats1 Yesterday, 01:59 PM Go to last post
    Eflats1

    Auto fill web form

    I am close to completing the macro I need to auto fill a web form. I understand the basics of the code and how they reference the html elements but I...

    Eflats1 Yesterday, 01:25 PM Go to last post
    alansidman

    Pivot table and date groupings

    No toe stepping here. I am at the early stages also. And as we already know, there are many ways to accomplish the same result in Excel. Glad to see...

    alansidman 2019-05-18, 10:32 PM Go to last post
    p45cal

    Pivot table and date groupings

    I don't mean to stand on other's toes - I'm getting to grips with Power Query etc. and wouldn't mind some feedback.
    I experimented and ended up...

    p45cal 2019-05-18, 08:14 PM Go to last post
    p45cal

    Hiccups in exporting data to Excel from Notepad

    …and I did it with a macro. See attached with a button which runs the code below. It adds a new sheet to the workbook.

    Code:
    Sub blah()
    Dim Headers()
    ...

    p45cal 2019-05-17, 07:26 PM Go to last post