

The king of lookups in Excel: The 3 way- or 3D lookup. Imagine this scenario: You got several Excel tables, each has rows and columns. Depending on your input values, you want to get the data from a specific cell from the right table, row and column. Such lookups are called 3D lookups or 3 way lookups. In this article we explore 6 methods of how to conduct 3D lookups in Excel.
Contents
The 3D lookups are advanced lookups in Excel. The basics are the same like any lookup, e.g. VLOOKUP: You search for a specific value. But the VLOOKUP formula is (in it’s basic version) just 1-dimensional (you only search within rows). 3D lookups don’t only search in rows, but also in columns (well, until here you only got 2 dimensions). The third dimension is added by the search table itself: 3D lookups search in rows, columns and tables at the same time.
The following methods are based on 4 basic formulas in Excel. Each formula we’ve described before, so please refer to these articles if you need assistance:
Also, you should get yourself familiar with the 2D lookup, as 3D lookups build up on this. Furthermore – but not necessary – you could take a look at this article: It describes, when to use which lookup formula.
Please feel free to download the example file here. It contains all the examples described in this article.
As this article is quite long, we provide you an overview of the 6 methods. That way, you can scroll directly to your best method.
Comparison of the introduced methods. You can scroll directly to the suitable one for you.
Example: We got four similar tables and want to look up one specific value.
Before we jump in with the four methods for 3D lookups, let’s take a look at our underlying example first.
You got the four tables as shown in the image on the right hand side.
The task: Depending on the type, region and year you want to get a specific value. For example, you want to know the number of phones sold in 2016 in Europe.
Please note: There are countless approaches and methods. In this article you learn 6 different versions. You can of course – based on your data – change or combine these methods.
Method 1: Change the structure and use any lookup method – e.g. SUMIFS.
The first method is actually not really a 3D lookup but oftentimes the best and fastest solution. You change the structure of your data so that you can conduct a “normal” 2D lookup. In our example it’s easily possible as all the four tables got the same structure with the same rows and columns.
Start by copying the tables underneath each other. Then you add one column, containing the type. As you can see in column G, it always says “Computers” and “Phones” and so on. This column will be part of the lookup.
Now you use the SUMIFS formula (if you are not sure if the SUMIFS formula is possible in your case, please refer to this article).
The solution looks something like this:
The basic formula is the SUMIFS formula in this method.
The SUMIFS formula has got two crucial parts in this example:
The complete formula is:
=SUMIFS(IF(C4=2013,C9:C34,IF(C4=2014,D9:D34,E9:E34)),B9:B34,C5,G9:G34,C3)
You should use this method if
For the 2nd method you create two support tables.
The next method of 3D lookups is based on the VLOOKUP formula. Unfortunately, the VLOOKUP formula is by default not made for advanced lookups. That’s why we start by creating two support tables:
This method is based on the VLOOKUP formula.
Now you can start setting up the VLOOKUP (if you need more assistance with the VLOOKUP formula, please refer to this article). The idea is, that you use a VLOOKUP searching for the country. But instead of having fixed search tables and return columns, you make them variables too.
The complete formula: =VLOOKUP(C5,INDIRECT(VLOOKUP(C3,L12:M15,2,FALSE)),VLOOKUP(C4,O12:P14,2,FALSE),FALSE)
You should use this method if
Please note: If your data is located on different worksheets, just add the sheet name to the first support table. Instead of B11:E15 you would write ‘Sheet 1’!B11:E15.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
For the third method you need a simple support table.
The third method of 3D lookups is similar to the second method: It’s based on the VLOOKUP formula. But in this example, you only need one support table. This time, the support table only contains all the types, e.g. computers, phones. You’ll use this with the MATCH formula.
Now you can start putting the VLOOKUP formula together. The difference to the second method: You don’t use the INDIRECT method for switching the lookup table. Instead you use the CHOOSE formula. Furthermore, you take a shortcut for the lookup column with the MATCH formula instead of another support table.
The third method is also based on the VLOOKUP formula and uses CHOOSE and MATCH.
The four parts of the VLOOKUP formula:
What if your tables don’t have the same structure? E.g. one table misses the column 2014? In such case, you have to modify the third part of the formula. The MATCH formula must get the return column from the correct table. You could either use another CHOOSE formula within the second part of the MATCH formula or the INDIRECT formula, similar to the description of method 2.
You should use this method if
For the third and fourth methods you need a simple support table.
The 4th method is probably the most elegant approach: A combination of INDEX and 3 MATCH formulas. Again, you need one support table (it’s the same of method 3). It only contains a list of the input tables, in your case “Computers”, “Phones” and so on.
Having this, we can start setting up the INDEX formula.
The 4th method uses the INDEX/MATCH/MATCH/MATCH approach.
You should use this method if
Example with different input data.
Unfortunately, in many cases the input tables don’t have the same structure. That’s why we take a look at another example of 3D lookups with different input tables. In order to keep it simple, we just go with 2 different input tables as shown in the table on the right hand side.
The difference: The lower table on the right hand side has a new column C with the year 2013. In this case we modify the 2nd method from above.
Support tables for the version with different columns in the input tables.
The basic formula is still a VLOOKUP but you use two support tables. These support tables are quite similar and we use them with INDIRECT formulas, so that they contain ranges.
The first support table contains the complete ranges, in which you want to search either for “Computers” or “Phones”. It’s important, that the leftmost column (here: B) is included as we search within this column.
The second support table has the ranges of the headlines of each table. In these headlines we will search for the return column.
Example for a 3 way lookup with different input tables.
The VLOOKUP formula has always four parts:
Example: Search in several tables for a specific value.
Let’s take a look at one more version of 3D lookups: Search through several tables for a specific item. That means, you don’t specify, in which table your data is located. You rather let Excel go through all of them until found.
The example is quite simple: You got the 2 tables as shown on the right hand side. You search for a specific country and year combination.
The solution is actually quite simple: Do you remember the (comparatively easy) 2D lookup? Please refer to this article – we will use the 4th method from that article without going further into detail.
The trick: Use two 2D lookups, one for each table. Then you combine them with the IFNA formula. The complete formula looks like this:
=IFNA(INDEX(B8:E10,MATCH(C4,B8:B10,0),MATCH(C3,B8:E8,0)),INDEX(B13:F15,MATCH(C4,B13:B15,0),MATCH(C3,B13:F13,0)))
You’ve learned 6 different methods in this article. All methods have advantages and disadvantages. Now it’s your task to pick the most suitable one and taylor it to your needs. The methods usually require some practice and trial-and-error. So if they don’t work instantly, please be patient and check for the errors. With time going by you will feel more comfortable.
Please also feel free to download the example file here. It contains all the examples described in this article.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.