How to do a two-way lookup in Excel with XLOOKUP()

Download a PDF version of this post below:

A two-way or two-dimensional lookup in Excel allows users to search for a value at the intersection of a specific row and column within a table. This method is useful for retrieving data based on two lookup values: one identifying the row and the other specifying the column. For example, a two-way lookup on this dataset could be used to find the sales figures for Jungle Beat at the Regal Dome theater.

Traditionally, Excel users would accomplish a two-way lookup using the INDEX() and MATCH() functions together, where MATCH() would find the row and column indexes, and INDEX() would retrieve the value at that intersection. Another method combined VLOOKUP() with MATCH(), using VLOOKUP() to search for the row and MATCH() to specify the column index within that row.

With the introduction of XLOOKUP(), the approach changes slightly because XLOOKUP() does not inherently handle column positions. Let’s take a look in this blog post. You can follow along with the exercise file below:

The first step is to search vertically down the rows of the first column to find a match for Jungle Beat. Since XLOOKUP() does not use index number positions, we specify the location of an array rather than its position.

By looking up the entire table, we retrieve a dynamic array containing all the data in the matched row:

=XLOOKUP(H3, films[Movie], films)

From this point, we need to search for the relevant column by its name, in this case, Regal Dome. This can be achieved with a nested XLOOKUP(), which adds a few steps to our initial example.

I will search the headers to find a match for Regal Dome, and then I can look up the corresponding movie data by row:

=XLOOKUP(H2, films[#Headers], XLOOKUP(H3, films[Movie], films))

What do you think of this solution? Although it’s a bit unintuitive and XLOOKUP() doesn’t significantly enhance older methods for two-dimensional lookups, it does compute faster and offers the ability to return a dynamic array instead of just a single cell.

Do you have any questions? Let me know in the comments. And if you’re interested in getting started with dynamic array functions in Excel, check out my book, Modern Data Analytics in Excel: