Excel VLOOKUP – Approximate Match

Excel’s VLOOKUP function is a very powerful tool that allows users to combine and match two columns in separate datasets.  When performing a VLOOKUP, a given value is matched in a separate range of values within a specific column, returning a value (the desired lookup value) specified in the column index of the row of the match.  The V in VLOOKUP stands for “Vertical” column.  A good example of how the VLOOKUP function operates is looking up a number in a phone book, or scanning a menu to determine the price of a food item.  First, you scan through the list of items to find the value (phone number or food item) you are looking for (top to bottom), then you move your finger to the right to find the associated value (phone number or price) of the item you were looking for.

In this post, I will provide a detailed example of how the Excel VLOOKUP function operates, using the APPROXIMATE match criteria.

To view how the VLOOKUP EXACT match works, you can go to the following post:     Excel VLOOKUP – EXACT Match

Excel VLOOKUP – Approximate Match

Let’s say you have the following table, which shows the cost per product based on different volumes of purchase:

Table A

Excel VLOOKUP - Approximate Match

Now you want to determine the cost per product, based on different purchase order quantities.  Here is where the VLOOKUP Approximate match comes into play.

Your formula would look like this:

In Cell E2, enter:      =VLOOKUP(D2,$A$1:$B$7,2,TRUE)

Excel VLOOKUP - Approximate Match

Here is a breakdown of the formula:

  • D2 – This is the value the formula should use to lookup information in the second table (i.e. lookup Order Qty “5” in Table A, Column A)
  • $A$1:$B$7 – This is the range the formula will scan for the Order Qty value “5”, and it will lookup this value in Column A
    • Important Note:  In the lookup table (Table A above), the column you are looking for the match should always be the LEFT most column (Column A in this case)
  • 2 – This is the column index of the lookup table (Table A above) that will be returned in the formula result, once a match has been made (in this example, we want the “Cost per Product”)
  • TRUE – This tells the formula to use an “APPROXIMATE Match” – searches for the value that is closest (approximate) to the lookup value. This is the default method if you don’t specify one.

Note:  If you want to get accurate results using the Approximate Match in a VLOOKUP formula, you must sort the lookup column in ascending order (whether the values are in text or numeric format).  The reason for this is when Excel performs the Approximate Match, it is looking for the value in the lookup table (row) that is less than the value being looked up.  At the same time, it will go one row further and if that value happens to be greater than the value being lookup up, it feels the previous values is the correct “Approximate” match and will return the column index of the matched row.

Absolute Range:  In argument #2 above, you will notice “$” signs around each cell reference.  This creates an absolute cell range, which will not change as you copy the formula.  to add an absolute range, highlight the original lookup range (i.e. highlight the characters “A1:B7”) in the formula editor and press the F4 key.

Formula Result:

Excel VLOOKUP - Approximate Match

In cell E2, the result of the VLOOKUP is $20.  Since the Order Qty was 5 items, the formula scanned the Purchase Volume (Column A) in the lookup table (Table A), looking for the first value that is less than the value being looked up (Order Qty of 5 is being looked up).  The approximate match returns the next largest value that is less than your specific lookup value, then returns the column index (in our example, the column index in the formula was “2”, so the formula returns the “Cost per Product” (Column B) in the lookup table (Table A).

Now, copy the formula down and your resulting values look like this:

Excel VLOOKUP - Approximate Match

 

Example File:

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment