VLOOKUP - Exact Match & Approximate Match

Excel Tip #013

VLOOKUP - Exact Match & Approximate Match

In the previous tip #012 (VLOOKUP & INDEX + MATCH), we learnt about VLOOKUP, it's syntax and how to use it. We also learnt about its drawback and how INDEX & MATCH can be helpful.

Like said in the previous tip, there are 2 match option available in VLOOKUP - Exact & Approximate Match. Let us first refresh the syntax of VLOOKUP.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
In simple terms,
= VLOOKUP(value/data that is used as reference, data table, the column # where our resultant data resides, exact (or) approximate match)

Let's have a closer look at the last argument you supply to the Excel VLOOKUP function - range_lookup. You are likely to get different results in the same formula depending on whether you enter TRUE or FALSE (Approximate or Exact).
  • If range_lookup is set to FALSE, the formula searches for exact match, i.e. for the lookup value exactly as you've entered it in the first parameter (lookup_value )
  • If there are two or more values in the first column of table_array that match the lookup value, the 1st value found is returned. If an exact match is not found, the #N/A error is returned
  • For example, if you use the formula =VLOOKUP(4, A2:B15,2,FALSE), but your data does not contain value 4 in cells A2 through A15, the formula will return #N/A
  • If range_lookup is set to TRUE or omitted, the formula searches for approximate match. More precisely, your VLOOKUP formula will search for an exact match first and if an exact match is not found, it will return an approximate match. An approximate match is the next largest value that is less than lookup_value.
Note: If you enter TRUE or omit the range_lookup argument, the values in the first column of the lookup range must be sorted in ascending order, i.e. from smallest to largest. Otherwise, the Excel VLOOKUP function may not find the correct value.

VLOOKUP with approximate match:

Consider the below example, Rank and Sales Rep.
Example - VLOOKUP, Approximate match
The first thing you need to do is sort the first column in your lookup range in ascending order. This is very important because your VLOOKUP formula is going to return the next largest value for the lookup value you specify and then stop searching.

If you neglect to sort your data properly, you will end up having really strange results or #N/A error.
Sort data to use Approximate match in VLOOKUP
Below is an example to see the closest rank to rank 10.
Use of Approximate match, rank closes to rank 10
You can see rank 11 is available but the resultant shows rank 7. This is because the VLOOKUP function with approximate match returns the next largest value that is less than the lookup value.

Some more examples:
Some more examples - VLOOKUP with Approximate Match
Did you the second question, to find the Sales rep who ranked 13? As highlighted in the image, there are 2 Sales Rep who are ranked 13, Bessie and Horace but the final result shows Horace. Why?

This is because when we use Approximate match, VLOOKUP will look for all matches of ranked 13 and will fill in the later part by overwriting if it has found already.

If using Exact match for rank 13, you will see Bessie as the result. This is because Exact match will display the first found solution.

This is a difference when using Exact Match & Approximate Match in VLOOKUP.

Comments

Post a Comment

Popular posts from this blog

Row 1 Is Missing. How to unhide?

Image
Excel Tip #030 Row 1 Is Missing. How to unhide? As you know, hiding and unhiding rows in Excel is simple as selecting them, right-clicking and choosing Hide or Unhide . The trick to selecting hidden rows, is to select a row above or below and then drag to included the hidden rows. For example, if you accidentally hide row 1 of your worksheet or inherit someone else's worksheet with one or more of the top rows hidden, you may be wondering how to unhide them since apparently you cannot select them. Row 1 Hidden There is a really easy solution. 1) Point to the row 2 header , press the left mouse button and drag upward to the top of the worksheet. Although you cannot see it, row 2 and row 1 are now selected 2) Right click anywhere on row 2 3) Choose Unhide from the right-click menu Unhiding hidden Row 1 NOTE : This also works for multiple hidden rows. There is another method of doing the same if Row 1 is hidden. 1) Go to the top left corner of the sheet abov
Read more

Search Backwards in Excel

Excel Tip #022 Search Backwards in Excel Sometimes we take some things for granted in Excel and assume that everyone else knows too. Here's a tip which lot of Excel users don't know about it. Many Excel users know that the quickest way to find something in a large worksheet is to use Excel's Find command. On the Home tab, in the Editing group, click Find & Select , then Find or, preferrably, use the keyboard shortcut CTRL+F instead. Each time you click Find Next you will be taken to the next matching cell, starting from the active cell and going down the sheet until you finally come to the last one. But what if you want to find the 'last' matching value in a sheet and there are dozens or even hundreds of matching values? You don't want to click through each one until you finally reach the end. Here's a simple trick that will save you some time. Select a cell below the range you want to search ( CTRL+END is a shortcut you can use to quick
Read more