Quick Links
For many, the INDEX-XMATCH combination in Excel is the go-to method for retrieving a value from a dataset. However, you may 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 theINDEXandXMATCHfunctions, continue reading. However, if you already know how to use INDEX and XMATCH for one-way lookups, jump straight to the section headedUsing 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:
where
For example, typing:
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 ofhardcoding the row and column numbers, you may reference cells containing them, making the formula more flexible. Here, typing:
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 inExcel for the weband on the Excel tablet and mobile apps.
Microsoft 365 Personal
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
You may be familiar with theMATCH 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 anduse wildcard characters for partial matches—both of which you can’t do with MATCH.
In this example, typing:
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 argumentscanddaren’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, argumentacan be a reference to a cell containing the lookup value, meaning you may easily change the lookup value without editing the formula:
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.
Instead of typingstructured table referencesmanually, click a column header to select the whole column.
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.
If your column headers contain numerical values, like dates, you may see an#N/A errorwhen 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 anduse data validation to create drop-down menusin 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 youcan’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, orname the rangesand reference those instead.
