If you’re using Excel and you’ve already learned how to use INDEX MATCH, you’re well on your way to becoming proficient with Excel lookups. What INDEX MATCH MATCH offers you is a more powerful version of the formula. Instead of just a vertical lookup, INDEX MATCH MATCH allows you to perform a matrix lookup, which is also known as a two-way lookup. This combination formula may initially seem complex because of its three individual formulas, but after you understand each component and how they interact, using this tool will become second nature to you. INDEX MATCH MATCH is one of several lookup formulas you should learn to become adept in database theory.
Objective and When to Use
There’s really just one key condition that needs to be met before you can use INDEX MATCH MATCH:
A matrix lookup can only work if your data table has lookup values on both the top and left hand side.
Basically, your data needs to be in a matrix format. People usually create matrixes, with lookup values both vertically and horizontally, to cross reference two different fields. In the example below, we are cross referencing the field State with the field Year and showing the relevant data point for Sales.
Creating a matrix saves you space within your spreadsheet and is more visually appealing. However, most data sets are not organized in this fashion. In fact, if you follow proper database theory, your data actually should not have lookup values going both vertically and across your table. A properly formatted table would look like the example below:
Before moving forward, ensure that you are using the proper formula for your data set. There are several other lookup options you can choose from if your data does not fit the requirements for INDEX MATCH MATCH. For example, if you only have lookup values on the top of your data set, you should consider using HLOOKUP. If you only have lookup values on the very left hand column of your data set, you should consider using VLOOKUP or INDEX MATCH.
Below is the syntax for using this formula combination. Don’t worry if it doesn’t make sense now; the rest of this post will provide context for each component and we’ll review a more practical version of the syntax that’s easier to remember.
= INDEX ( array , MATCH ( lookup_value , lookup_array , 0 ) , MATCH ( lookup_value , lookup_array , 0 ) )
Not surprisingly, INDEX MATCH MATCH is based on the INDEX and MATCH formulas, which we will now go through in detail.
The INDEX Formula
The INDEX formula asks you to specify a reference within a range and returns a value. In its simplest form, you just indicate either a row or column as your range, specify a reference point, and the value that matches that reference point is returned. For example, if we were to select the left hand column of this table, and specify the reference “6”, the INDEX formula would return the value “WA”.
Now instead of using just selecting a single row or column, what you can also do with the INDEX formula is select an entire matrix, with multiple rows and columns, as your array. The key difference here is that, instead of just specifying a single appearance order as a reference, you must now provide both a vertical and horizontal reference to return your value. (Please note that the INDEX formula always takes the vertical reference first) Using the INDEX formula with a matrix reference represents the foundation of utilizing INDEX MATCH MATCH. The syntax for the INDEX formula by itself is as follows:
= INDEX ( array , row_number , column_number )
For example, let’s say we selected the entire sales data table, and then specified “6” as the row number and “4” as the column number. The INDEX formula performs the intuitive action of going down 6 rows and over 4 columns with the range we selected to return the value of “$261.04″.
The MATCH Formula
The MATCH formula asks you to specify a value within a range and returns a reference. The MATCH formula is basically the reverse of the INDEX formula. The two formulas have the exact same components, but the inputs and outputs are rearranged.
= MATCH ( lookup_value , lookup_array , 0 )
To give you an example of the MATCH formula, if we were to select the entire left hand column and then specify “WA” as our lookup value, the MATCH formula would return the number “6”. Please note that you have to put in a “0” as the last argument to ensure that the MATCH formula looks for an exact match.
How it Works
As mentioned before, when using the INDEX formula across a matrix it requires both a horizontal and vertical reference. The only additional complexity that INDEX MATCH MATCH adds is that the vertical and horizontal references are turned into MATCH formulas.
Putting it Together
Below is a simplified version of the syntax describing the inputs with the appropriate context for our goal. In case you get lost in the individual steps, you can always refer back to this notation.
= INDEX ( entire matrix , MATCH ( vertical lookup value, entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )
Step 1: Start writing your INDEX formula and select the entire table as your array
Step 2: When you get to the row number entry, input the MATCH formula and select your vertical lookup value for the lookup value input
Step 3: For the lookup array, select the entire left hand lookup column; please note that the height of this column selection should be exactly the same height as the array for the INDEX formula
Step 4: For the final argument in the MATCH formula, input 0 to perform an exact match and close out the MATCH formula
Step 5: Now that we’ve arrived at the column number entry of the INDEX formula, input another MATCH formula but this time select your horizontal lookup value for the lookup value input
Step 6: For this lookup array, select the entire top header row of the original grid you selected for the INDEX formula
Step 7: Repeating what we did for the previous MATCH formula, input “0” for an exact match and close both the MATCH formula and the INDEX formula with parentheses
What Excel Does
Excel must first calculate the result of the two MATCH formulas embedded within the INDEX formula. Since we know that “WA” is the sixth value down in the left hand column, and “2004” is the fourth value across in the top header row, those formulas become the values of 6 and 4 respectively. Once we’ve simplified those components, Excel essentially performs the exact same INDEX lookup that we demonstrated before; it goes down 6 rows and over 4 columns to pull the correct value of “$261.04″.
INDEX MATCH MATCH probably won’t be a formula you use often. Most of the time when dealing with databases and data tables, you’ll be using vertical lookups to query results. However, in situations where you absolutely do need to perform a matrix lookup, INDEX MATCH MATCH is the best option you have.