5 Advanced Excel VLOOKUP tricks you MUST know!

 5 Advanced Excel VLOOKUP tricks you MUST know!

1. Use VLOOKUP instead of nested IF statement

If nested IF functions appear as puke-in-puke to you then VLOOKUP’s approximate match can help you get rid of heinously long IF statements. The only condition is to have the data in ascending order otherwise it might not work properly.

Have a look at the following data:

The formula used to fetch grades is this: =VLOOKUP(G5,B2:C10,2,TRUE)

We asked Excel to:

1. lookup for value in cell G5 which is 87
2. in first column of specified range B2:C10 which starts from cell B2 till B10.
3. To find an approximate match as we have TRUE argument in place. Now that we don’t have 87 in our case, Excel will select the smallest among possible values which is 85.
4. Once selected jump to second column of the specified range which is C and fetch the corresponding value against 85 which is “A”.

The reason it works is that we are using approximate match. VLOOKUP will start the matching process from the first entry in the specified column and settle with the smallest value close to lookup value. Lets repeat the process with 76 as percentage marks:

Again, VLOOKUP looked for 76 in column B. As there is no 76 in the column, approximate match possible is either 75 or 80. Between these two, Excel will settle with the smallest i.e. 75 and thus fetch the corresponding grade i.e. B+ from column C.

2. Make lookup values go WILD

Just like FIND function of Excel you can use wildcards in lookup value argument of VLOOKUP function as well. It makes lookup much more easy in certain situations especially if your lookup requirement is part of string.

Have a look at this situation where data extracted from database has somehow mingled the date, product and type.

If we want to fetch the price of apple then we can use the formulas fallows:

=VLOOKUP(“*apple*”,B5:C14,2,FALSE)

Though above approach works, but we have hard coded the “apple” in the formula. It will be easy if we can use reference to specific cell so that we change the product in the cell to fetch the relevant price instead of changing the formula every time. To do this we need to arrange things a little different way as following:

=VLOOKUP(“*”&G8&”*”,B5:C14,2,FALSE)

3. Perform Two-dimensional lookups

Under usual circumstances while using VLOOKUP we hardcode the column number from which Excel should get the result. Consider the following formula:

=VLOOKUP(G5,B2:C10,2)

“2” is actually coloumn number that we mentioned. But we may have situations where our data is spread in different columns, and we need to get the right column first based on criteria and then we perform the lookup. This is called 2D lookup or two-dimensional or 2 criteria lookup. It goes with many names.

Consider the following situation:

What we want is to extract the price of mentioned product AND size. Now as the sizes and their prices are mentioned in different columns, we will involve another lookup function named MATCH to pair up with VLOOKUP.

Here is the formula:

=VLOOKUP(I8,B5:E14,MATCH(I9,$B$5:$E$5,0),FALSE)

MATCH function is helping us identify the right column. In our case “M” size is second column. MATCH function will return “2” by looking up the size in B5:E5 range. Once we have the column number VLOOKUP will fetch the correct price for us.

4. Perform 3D three-dimensional lookups

In 2D vlookups we learnt how to use MATCH to find the right column to fetch the right value.

If we have different data ranges and each have different columns then it adds one additional layer of conditions or dimensions thus called 3D lookup.

Suppose we have similar products in different size on sale at different markets. Each market has its own pricing. In such situation if we want to fetch the price of a product we not only have to know the size of the product but also the market. Take the following scenario as example:

First thing we need to do is to give each data range relevant name. I used “east” for the East market and “west” for the West market. Its a simple process. Simple select the range and using name box you can define names. Following animation will walk you through this step:

Now that names are defined we can use mashup of VLOOKUP, INDIRECT and MATCH functions to make a formula as follows:

=VLOOKUP(F18,INDIRECT(F20),MATCH(F19,$B$6:$E$6,0),FALSE)

Starting from the innermost function:

MATCH is doing the exact same job as in 2D lookup i.e. to get us column number by matching the size in cell F19.

INDIRECT function holds cell F20 that contains the market name which is either East or West. As the name I defined are east for East and west for West therefore the market mentioned in the cell will act as range name as well and using INDIRECT function we are asking Excel to bring that range.

VLOOKUP is doing the job to use the lookup value mentioned and bring the result.

Bonus: Different size/types for each market.

Suppose West market has XL size too. Our data will look like this:

In this case, if we use the above formula, it won’t work as we want a formula that is dynamic and can accommodate the changing range to perform MATCH function. There are number of ways to do it but I love Excel table the most.

Simply select each range, turn into table and then give them names. I named East as “east” and West as “west”. Make sure you delete the old names east/west, if you have any, otherwise Excel won’t let you use these names for tables.

Now to get the price following formula is used:

=VLOOKUP(F18,INDIRECT(F20),MATCH(F19,INDIRECT(F20&”[#Headers]”),0),FALSE)

By using INDIRECT inside MATCH function, Excel’s table headers are used as range reference. Now even if you add more types to existing table or delete few, it should not break. Here is the result:

5. Fetch values from multiple columns in one go

So far we have been fetching result from one specified column that is either manually hard-punched by us in VLOOKUP formula or we determined it using MATCH function. But we can fetch values from multiple columns at once against single a lookup value.

Here is one situation where products have different sizes and you want to know the prices of all sizes of selected product.

This requires fetching the prices from all the columns at once using array formula. It requires two main steps:

1. we need to pre-select the range of cells where we want the prices for each size for specified product
2. we need to punch in the formula using CTRL+SHIFT+ENTER combination instead of simple Enter key press.

The formula used is following:

{=VLOOKUP(B20,B7:F15,{2,3,4,5},FALSE)}

Remember the curly brackets are not inserted by me rather they represent that formula is entered as an array formula using CTRL+SHIFT+ENTER combination.

Following illustration walk you through the process:

Loved it? Pin it!