Microsoft Excel’s XLOOKUP function is best known for retrieving a value from a certain row or column in a dataset. However, you can also use this powerful tool to perform two-way lookups, returning a value at the intersection of a specified row and column.
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 access the XLOOKUP function. It’s also readily available inExcel for the weband 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.
Using XLOOKUP for One-Way Lookups in Excel
The key to understanding how XLOOKUP can be used to perform two-way lookups is to first get your head around how this function works in one-dimensional scenarios.
where
If argumentdis omitted, Excel returns zero if the lookup value is not found in the lookup array. If argumentseandfare left out, the default option is applied (an exact match for argumente, and a top-to-bottom or left-to-right search for argumentf).
into cell G2 returns a score of 48.
This formula takes the ID in cell F2 (argumenta), looks for it in cells A2 to A100 (argumentb), and returns the corresponding value in cells D2 to D100 (argumentc). If the ID in cell F2 isn’t listed in cells A2 to A100, the lookup returns the term “Invalid ID” (argumentd). The fifth argument tells Excel to return exact matches only (argumente), and the sixth ensures the lookup runs from the top of the range to the bottom of the range (argumentf).
Using XLOOKUP for Two-Way Lookups in Excel
While one-way lookups are useful in many scenarios, they don’t allow you to change the variable you’re looking up. Indeed, the formula above only returns a score based on the specified ID, but what if you wanted to see the age or gender instead? This is where a two-way lookup (also known as a two-dimensional or matrix lookup) comes into play.
To do this, you need to nest one XLOOKUP inside the other:
You can also add argumentsd,e, andffor each XLOOKUP in exactly the same way as you would when performing a one-way lookup. However, to keep things simple, I’ve omitted these from the syntax above and examples below.
This is because Excel first searches for the specified ID (argumenta¹) in cells A2 to A100 (argumentb¹), then it searches for the specified variable (argumenta²) in cells B1 to D1 (argumentb²), and returns the result at the intersection of these two lookups in cells B2 to D100 (argumentc).
As a result, if you change the variable in cell G1 to “Age,” you get a result of 41.
Likewise, if you change it to “Gender,” you get M as the result.
In two-dimensional XLOOKUP formulas, it doesn’t matter which way around the XLOOKUPs go. In this example, the ID lookup is first in the formula, and the column header lookup is nested. If these were switched, you’d get the same result.
Pro Tip: Add Drop-Down Lists to the Lookup Cells
Now that your two-way lookup is complete, you could go one step further by adding a drop-down list to the cells containing the lookup values to speed up the data retrieval process.
To do this, select one of the lookup cells (in this case, cell G1), and in the Data tab on the ribbon, click “Data Validation.”
Next, in the Allow field of the Data Validation dialog box, select “List.” Finally, activate the “Source” field, and select the cells containing the values that you want to appear in the drop-down list. In this case, it’s the column headers in cells B1 to D1.
When you click “OK,” you’ll see a drop-down button in cell G1 which, when clicked, reveals the variable options.
Now, repeat the process for cell F2, the other lookup cell.
Notice how, in the Source field, I’ve specified a range that extends beyond the size of my dataset to allow for growth (my dataset contains 100 rows, but I’ve extended the source to cell A1000). The period (also known as atrim ref operatorin this context) after the colon tells Excel to trim any blank cells at the end of the source range.
Alternatively, you could use a formula that counts the number of cells containing values in column A and returns that number of options in the drop-down menu.
Now, you can select an ID from the drop-down list in cell F2.
You can also perform two-way lookups in Excel usingINDEX and MATCH. This alternative option is particularly useful if you’re using Excel 2019 or older, as these functions were introduced to the program before XLOOKUP.
