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.

## No comments:

## Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.