For many, the INDEX-XMATCH combination in Excel is the go-to method for retrieving a value from a dataset. However, you can also use this dynamic duo to perform two-way lookups, returning a value at the intersection of a specified row and column.
This article is aimed at readers across all levels of Excel expertise, increasing in complexity as it progresses. If you’re unfamiliar with the INDEX and XMATCH functions, continue reading. However, if you already know how to use INDEX and XMATCH for one-way lookups, jump straight to the section headed Using INDEX With XMATCH for Two-Way Lookups.
How Excel’s INDEX and XMATCH Functions Work
Before we look at how these two functions can be used together in one formula, let’s take a moment to explore how they work in isolation.
The INDEX Function
The INDEX function returns a value in a range according to the row number and column number you specify. The syntax is as follows:
=INDEX(a,b,c)
where
- a is the range,
- b is the row number, and
- c is the column number.
For example, typing:
=INDEX(T_Profit,3,6)
into cell H2 returns the value in the cell that sits at the intersection of the second row and the third column of the T_Profit table.
What’s more, instead of hardcoding the row and column numbers, you can reference cells containing them, making the formula more flexible. Here, typing:
=INDEX(T_Profit,I2,I3)
into cell I5 pulls the row number from cell I2 and the column number from cell I3.
The XMATCH Function
The XMATCH function searches for an item in a range and returns its position.
If you’re working in the Excel desktop app on a PC or Mac, you need to be using Excel 2021 or later (including Excel for Microsoft 365) to use the XMATCH function. It’s also readily available in Excel for the web and on the Excel tablet and mobile apps.
Here’s the syntax:
=XMATCH(a,b,c,d)
where
- a is the item to look up,
- b is the range to search,
- c is the match type (0 = exact match (default); -1 = exact match or next smallest item; 1 = exact match or next largest item; 2 = a wildcard match), and
- d is the search mode (1 = first to last (default), -1 = last to first, 2 = binary search where b is in ascending order, -2 = binary search where b is in descending order).
You may be familiar with the MATCH function, which is the predecessor to the more modern XMATCH function. They work in similar ways, though the default arguments in the XMATCH syntax are more intuitive than those in the MATCH syntax, favoring an exact match over an approximate one. Also, XMATCH lets you search in either direction and use wildcard characters for partial matches—both of which you can’t do with MATCH.
In this example, typing:
=XMATCH(1927,T_Profit[Employee])
into cell H2 returns 3, because the employee ID number 1927 is the third value in the Employee column of the T_Profit table.
Notice how arguments c and d aren’t required in this scenario, because we want an exact match in a search that runs from the top of the table to the bottom, and these are the default settings for this function.
Similarly, argument a can be a reference to a cell containing the lookup value, meaning you can easily change the lookup value without editing the formula:
=XMATCH(I2,T_Profit[Employee])
where cell I2 contains the employee ID to look up in the Player column of the T_Scores table.
Using INDEX With XMATCH for One-Way Lookups
While the INDEX and XMATCH functions can be useful on their own, their true potential is realized when used together. The key to understanding how the INDEX-XMATCH combination can be used to perform two-way lookups is to first get your head around how it works in one-dimensional situations.
Let’s say you want to view the total profit generated by an employee when you type their ID into cell I2.
To do this, in cell I2, type:
=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),6)
where
- T_Profit is the name of the table where the value will be found,
- XMATCH(I2,T_Profit[Employee]) tells the INDEX function which row of the Employee column to look in, based on the value in cell I2, and
- 6 tells the INDEX function to return the value in the sixth column of that row.
In this scenario, you don’t need to enter the match type or search mode arguments for the XMATCH part of the formula, as the default settings return an exact match and look from top to bottom.
But what if you want to return the value from another column, such as an employee’s profit in a given year? That’s where two-way lookups come in handy.
Using INDEX With XMATCH for Two-Way Lookups
The benefit of using INDEX with XMATCH for two-way lookups is that you can change the parameters for your search without editing the formula. This is because XMATCH identifies both the row number and the column number, meaning you don’t have to hardcode them into the formula.
Suppose you want to quickly find out how much profit employee 1191 made in 2021.
To do this, in cell I4, type:
=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers]))
where
- T_Profit is the name of the table where the value will be found,
- XMATCH(I2,T_Profit[Employee]) tells the INDEX function which row of the Employee column to look in, based on the value in cell I2, and
- XMATCH(I3,T_Profit[#Headers]) tells the INDEX function which column of the T_Profit table to look in, based on the value in cell I3.
If your column headers contain numerical values, like dates, you may see an #N/A error when you press Enter.
This is because Excel actually stores column headers as text, even if they appear numeric. So, to ensure you get a like-for-like match between the lookup value and the lookup array, select the cell containing the corresponding lookup value (which, in this example, is cell I3), and in the Home tab on the ribbon, click “Text” in the number format drop-down menu.
Then, select the cell containing the column lookup value (I3), press F2 to activate cell edit mode, and press Enter. Now, Excel sees both the lookup value and the lookup array as having the same number format, so the formula correctly returns the expected value.
Now, type different lookup parameters into cells I2 and I3, and see the formula return the corresponding result.
Now that you know how to use INDEX and XMATCH to perform two-way lookups, you could go one step further and use data validation to create drop-down menus in the cells containing the lookup values, further speeding up the lookup process and ensuring you don’t accidentally enter an invalid value.
However, bear in mind that you can’t use column headers as the source of a data validation list. To overcome this hurdle, enter direct cell references into the Source field of the Data Validation dialog box, or name the ranges and reference those instead.
