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.