Return an exact value via the LOOKUP() function

{ 8 Comments }

By Ashish Mathur · · · Leave a comment

Assume a two column database in range A3:B6.  Headings are in A2:B2.  In range A3:A6 are numbers, say 60,30,20 and 10.  In range B3:B6 are some names, say Ashish, Sameer, Gautam and Sanjay.

In cell A7, enter any name, say Sameer.  The task is to extract the corresponding number from the range (A3:B6) into cell B7.  Needless to say that VLOOKUP() will not work here because the lookup_value i.e. Sameer is not in the first column of the table_array i.e. in range A3:A6.  To get the answer, one may write the following INDEX() and MATCH() combination of functions in cell B7

=INDEX($A$3:$A$6,MATCH($A7,$B$3:$B$6,0),1)

So with Sameer in cell A7, the result in cell B7 will be 30.  One may further wrap this arround the IFERROR() function to trap errors.

One may also try the LOOKUP function in cell C7:

=LOOKUP(A7,$B$3:$B$6,$A$3:$A$6)

The answer would be 30 which is correct.  However, if you type Gautam in cell A7, the result will change to 60 in cell C7 (which is wrong!!!).  The answer in cell B7 will be correct i.e. 20.

The reason for the LOOKUP() function anomaly is that range B3:B6 is not sorted in ascending order.  The Help menu on the LOOKUP() function states that the lookup_vector of the LOOKUP() function should be sorted in ascending order.  Therefore, if you now select A2:B6 and sort the name column in ascending order, the result in cell C7 will become 20.

So one difference between the INDEX(), MATCH() combination of funtions and LOOKUP() function is that for the LOOKUP() function to work, the lookup_vector should be sorted in ascending order.  There is no requirement for sorting when using the INDEX() and MATCH() combination of functions.

Now type Samir (not Sameer) in cell A8.  When you copy B7:C7 to cell B8, the result in cell B8 will be #N/A and in C8 will be 30.  The result should ideally be #N/A because there is no Samir in range B3:B8.  Surprisingly, even though the name is misspelt, the LOOKUP returns a result (not an error).  This is happening because if the LOOKUP() function does not find an exact match, it finds for the largest value less than the lookup_value (provided the lookup_vector is sorted in ascending order).  The largest value less than Samir in range B3:B6 is Sameer.  Likewise, if you enter Ashwin in cell A9 and copy B8:C8 to cell B9, the result will be #N/A and 60.

The names entered in range A8:A9 would suggest that the LOOKUP() function can take care of spelling mistakes in the lookup_value.  Well this is not true because if you enter Asheesh as the lookup_value in cell A10, the result will be #N/A in B10:C10.  This is because since it cannot find Asheesh in range B3:B6, it searches for values less than Asheesh which do not Exist – Ashish > Asheesh.

From the discussion above, one may therefore conclude that the LOOKUP() function is not the appropriate function to use for exact matches especially when dealing with text lookups.

However, with a small tweak in the LOOKUP() function, one can coerce an exact search:

=LOOKUP(2,1/($B$3:$B$6=$A7),$A$3:$A$6)

If you try this formula in cell D7 and copy down, the result will be the same as the INDEX() and MATCH combination of functions which you wrote in B7:B10.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

    • Hi,

      Sure. Here goes the explanation

      The ($B$3:$B$6=$A7), will generate a virtual array for TRUE’s and FALSE’s. 1/($B$3:$B$6=$A7), will yield either an #DIV/0! or 1. Since the lookup_value of 2 will never be found in the the result of 1/($B$3:$B$6=$A7), it will start searching right to left. In doing a reverse search, it will ignore blanks, text values and errors. It will consider only the number and return the value from $A$3:$A$6.

      Hope that clarifies.

      Reply

  • Is there any other way of going about getting the results that the lookup function obtains without the list havng to be in ascending order? I have a pretty large database of company’s names and when I make a new record and input the country I want the values for postal code, subsequent postal cost, best time to call, hour difference, etc, to all be pre-populated based on previuos records for that country. But my excel sheet will be subject to new ordering a lot, so Lookup will not work. I’ve found some formulas for IF but I have more than 7 conditions. Thank you to anyone who may be able to help me.

    Reply

  • Hi! thanks for sharing this formula! I really find it useful since i use this for attendance checking ( we extract raw data and paste to excel ) I need the Lookup formula for this since Lookup always gives the last matching data ( multiple login logout of agents ) however there’s no tur or false option and thanks to your formula.
    However I noticed that it made my template slower and it loads up a little. I guess it’s just something minor.

    Reply