3D VLOOKUP – Perform VLOOKUP from more than one table

Pls have your 3D glasses ready… only if you are going to watch a 3D movie.  You don’t need it for a 3D VLOOKUP.

We talked about how to do a 2D VLOOKUP by using MATCH together with VLOOKUP.  With that technique, it’s not difficult to solve the problem below:


However, what if we have one more variable (Market)? i.e. more than one table to look at like the example below:


It is not uncommon that we have the same set of data for different markets, normally stored at different worksheets (one sheet for one market).  So how do we do the VLOOKUP in this case?

Relax, there will be at least 3 different ways to accomplish it.

  3. Using INDEX, MATCH


The three different formula yields the same result.

  • =INDEX((CN,JP,UK,US),MATCH(B2,A9:A12,0),MATCH(B3,A9:D9,0),MATCH(B1,{“CN”,”JP”,”UK”,”US”},0))

They look complicated.  Let’s explore them step by step.

First, let’s see how we do it if we have only one table.  Let’s say only JP market, the formula should be:

=VLOOKUP(B2,A16:D19,MATCH(B3,A16:D16,0),FALSE) ‘where B2 is the lookup value (i.e. product to lookup), A16:D19 is Table Array (where the data locates), B3 is the column_index (Month we want to look at).  Note:  MATCH is used to identify the corresponding column for VLOOKUP.

The above formula is quite straight forward for VLOOKUP.  Where both lookup value and lookup column are made “Dynamic” depending on the value on B2 and B3.  When one more dimension (Market on B1) is required, the trick is all about making the Table Array a dynamic one.

Make sense?

Wait? What are those “CN”, “JP”, “US”, “UK” in the formula?  These are the Names defined to different ranges and they facilitate the making of Dynamic Table Array.

To define Name to the four different Tables,

Highlight the Range of the table (A8:D11) in the example below.  Go to the Name Box on the left of the formula bar, type directly the name you want, let’s say US.


Repeat the same step to the rest of three tables.


Note: CN, JP, UK, US are the names defined in our example.

To test that you have correctly named the tables, click the drop down icon of the Name Box.  You should see the four names defined.


Select one of them will highlight the corresponding range.


Note: For more information about defining name, I recommend the following site by Debra Dalgleish : http://www.contextures.com/xlNames01.html

Now we are ready to make a dynamic Table Array by using


which returns the reference specified by a text string.

=INDIRECT(B1) thus returns the range specific in B1.  E.g. If B1 is JP, Excel reads it as =INDIRECT(“JP”) that basically is the range defined before; and the range defined is A16:D19.

If B1 is CN, Excel reads it as =INDIRECT(“CN”) that refers to range F9:I12.

Now the Table Array is dynamic depends on the value on B1.  This is exactly how the following formula works:




which uses index_num to return a value from the list of value arguments.  The value arguments can be range references as well as single values.

In our example, we want to choose a range from 4 different ranges: CN, JP, UK and US.

  • CHOOSE(1,CN,JP,UK,US) means CN, where CN refers to range F9:I12 as defined
  • CHOOSE(2,CN,JP,UK,US) menas JP, where CP refers to range A16:D19 as defined
  • etc.

To make the Range dynamic based on the value in B1 (as we are not going to input 1 to 4 in it), MATCH is used.

MATCH(B1,{“CN”,”JP”,”UK”,”US”},0) ‘Pls note the sequence in the array for MATCH should be the same as the sequence put for the value arguments used in CHOOSE

Finally, we have the VLOOKUP formula with the desired dynamic Table Array as below:



The 3rd way – INDEX itself worths a detailed discussion, which I am not going to go into details here.  The reason for showing that is again to show you the beauty of Excel – There are so many different ways to achieve the same goal.

Anyway, if you wish to know how it works, please free to leave your comments.

Other topics about vlookup:

  1. The basic of vlookup
  2. Tips in constructing vlookup
  3. vlookup with Match 
  4. vlookup options – True or False?
  5. Advanced vlookup – Text vs. Number
  6. Advanced vlookup – Wildcard Characters “?” and “*”
  7. Alternative to vlookup – Index and Match
  8. Three different ways to do case-sensitive lookup
This entry was posted in Formula and tagged , , , . Bookmark the permalink.

3 Responses to 3D VLOOKUP – Perform VLOOKUP from more than one table

  1. Great items from you, man. I have remember your
    stuff previous to and you’re simply extremely magnificent.
    I really like what you’ve acquired right here, really like
    what you’re stating and the way in which in which you
    are saying it. You’re making it enjoyable and you continue
    to care for to keep it smart. I can not wait to read much more from you.
    That is actually a tremendous website.


  2. Pingback: index / match on multips tables (or vlookup)

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s