VLOOKUP without limits (case-sensitive): using IF+ INDIRECT+MATCH+EXACT+INDEX functions

Here is a small data table, as an example of Excel table, created for the purpose of this exercise:

What if you are working with a real very big table, let's say 20 columns and 20000 rows, and want to retrieve quickly some specific information from such table, from any row and any column. The following table provides some examples of lookups - based on my small table - you'd might want to do:

It's somewhat self-explanatory. The first column says what our lookup is about (reference) and the third column displays information (result) - we're looking for - about that reference. But how exactly do we get the result we want to?

The answer is in formulas sitting in the background of the 'Results' column. All of them are the array formulas, so REMEMBER to enter them using the keyboard keys CTRL+SHIFT+ENTER, after you type them in.

Here they are, case by case:

1. This formula looks for the price of 038Rvc4T product in col. B of the data table:

 =IF(INDIRECT("A"&(1+MATCH(TRUE, EXACT(A$2:A$7, $E2), 0)))<>"", INDEX(B$2:B$7, MATCH(TRUE, EXACT(A$2:A$7, $E2), 0)), "") 

Surprisingly, in response we get question marks only...

2. So, I'm using another formula to check the Comment column in the data table:

=IF(INDIRECT("A"&(1+MATCH(TRUE, EXACT(A$2:A$7,$E2), 0)))<>"", INDEX(C$2:C$7, MATCH(TRUE, EXACT(A$2:A$7, $E2), 0)), "")

This time I get info saying that 'Price not set yet', so it's clarifies the question marks.

3. Now I want to get price of 038Rvci3u item, so I'm using this formula:

=IF(INDIRECT("A"&(1+MATCH(TRUE, EXACT(A$2:A$7, $E4), 0)))<>"", INDEX(B$2:B$7, MATCH(TRUE, EXACT(A$2:A$7, $E4), 0)), "")

 This time I get the result = £90.00 .

4. And I wonder now if there is any Comment for the same Item:

=IF(INDIRECT("A"&(1+MATCH(TRUE, EXACT(A$2:A$7,$E4), 0)))<>"", INDEX(C$2:C$7, MATCH(TRUE, EXACT(A$2:A$7, $E4), 0)), "")

 The result is 'No comment'. That's fine.

5. Next I want to know which of the products (Items) has the lowest price, so I'm using this formula:

=IF(INDIRECT("C"&(1+MATCH(TRUE, EXACT(C$2:C$7, $E6), 0)))<>"", INDEX(A$2:A$7, MATCH(TRUE, EXACT(C$2:C$7, $E6), 0)), "")

 and getting this result: 038Rvc5V (cell B7).

6. How about the price of the product (038Rvc4t) that temporarily is out of stock? Well, the following formula allows me to find it: 

=IF(INDIRECT("C"&(1+MATCH(TRUE, EXACT(C$2:C$7, $E7), 0)))<>"", INDEX(B$2:B$7, MATCH(TRUE, EXACT(C$2:C$7, $E7), 0)), "")

The price is £155.00 (cell B2).

7. And what's the price of the product with the Comment saying 'The lowest price'. To find out I'm using this formula:

=IF(INDIRECT("C"&(1+MATCH(TRUE, EXACT(C$2:C$7, $E8), 0)))<>"", INDEX(B$2:B$7, MATCH(TRUE, EXACT(C$2:C$7, $E8), 0)), "")

£28.00 (cell B7) is the answer.

As you can see, using this type of formula structure, we can get all kinds of answers, without limitations related to the VLOOKUP function.

Although the formulas look a bit complex, in fact they contain only two main parts. The first one checks if our table contains the string or other value we are referring to, then - if that's true - the second part finds the value (result) we are looking for.

Reminder, all of the presented formulas are array formulas! (use CTRL+SHIFT+ENTER to enter them).

************************

For better understanding of the formula structure, here is some basic information about all Excel functions involved:

IF function can have two results. E.g. IF(A2=”Y”,1,0) returns 1 if  'Y' is True, otherwise returns 0.

INDIRECT function returns reference specified by a text string. We use it to change reference to a cell without changing the formula itself.

Syntax: INDIRECT(ref_text,[a1]). 

E.g., if ref_text is 'A3' and cell A3 contains Reference to the cell B3, then INDIRECT(A3) returns the value sitting in cell B3.

MATCH function returns the position of the item in a range. We use it to provide e.g. a value for row_num of the INDEX function.

Syntax: MATCH(lookup_value,lookup_array,[match_type]).

E.g., if the range C2:C4 contains 10,20 and 30, the formula MATCH(20,C2:C4,0) returns 2, because 20 is the 2nd item in the range.

EXACT function returns TRUE if two compared strings are exactly the same; FALSE otherwise. It's case-sensitive!

Syntax: EXACT(text1, text2)

INDEX function returns a cell reference from a given range/array. We combine it usually with the MATCH function (which provides the position of data point in a range) to get the resulting value from a table/range.

There are two versions of the function available:

Array form syntax is: INDEX(array,row_num,[column_num]),  e.g.: INDEX(A2:A30,8,2) returns the value of 8th row and 2nd column, i.e. of cell B8.

Reference form syntax is: INDEX(reference, row_num,[column_num],[area_num]),  e.g.: INDEX(A2:C30,F2:M30),5,8,2) returns the value of cell M6 in the second area. If area_num is omitted, the function returns result for the first area listed.

The array form of INDEX function is used in the examples above.

Remember, this is just basic information about the functions. There are more detailed descriptions available from the Excel Help, and from elsewhere.