5 Advanced Excel VLOOKUP tricks you MUST know!

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: