

The new XLOOKUP function in Excel not only offers great advanced features, but can be also used for 2D XLOOKUPs. Before XLOOKUP, the most common way for searching in rows and columns at the same time was INDEX/MATCH/MATCH. A combination of XLOOKUP and XLOOKUP can do the same. Let’s see how it works and explore the advantages and disadvantages vs. INDEX/MATCH/MATCH.
As mentioned in the previous parts of this series, XLOOKUP returns arrays (which is a range of cells, not only a single value). VLOOKUP, on the other hand, only returns single values. The basic idea is to use one XLOOKUP to return a range of cells (for example a column or row). A second XLOOKUP wrapped around then returns a single item from this array.
Contents
A 2D XLOOKUP has two parts – two nested XLOOKUP functions. There are countless versions of 2D XLOOKUPs possible. In the following, we will concentrate on the most universal version and at the same time easiest to reproduce.
As you can see from the structure above, we nest two XLOOKUP functions. The numbers in the list below refer to the structure above.
What happens here? The inner XLOOKUP function returns an entire column of values. With the outer XLOOKUP function you pick just one value from this.
Alright, that is very theoretical. Let’s put it to work.
Our example: Based on the input in cells I3 and I4, we want to return the correct value from the table on the left-hand side. So, with the current inputs, we search for the Employee ID “10578” in column B and for “Last Name” in the rows. The return value in this example should be the Last Name of employee 10578 which is “Kane”.
For 2D XLOOKUPs there are multiple versions leading to the correct result. It’s all about using matching cell ranges in the arguments.
The approach is to use a XLOOKUP to return a range of cells containing with all the last names. From this, we pick the correct one, belonging to ID 10578.
That’s it already. The 2D XLOOKUP looks like this:
=XLOOKUP(I3,B3:B13,XLOOKUP(I4,B3:F3,B3:F13))
Please note: In the download (see below), this solution is labeled “XLOOKUP 1”. You can also find 4 more versions and the respective INDEX/MATCH/MATCH function.
Before we continue with the next example, let’s inspect what happens here. If we just type the inner XLOOKUP, we receive the following result.
The return value is spilled down over many cells. As you can see, it returns all last names.
This is special about the XLOOKUP function: It can not only return a single value, but also a range of cells. We use this to wrap around the next XLOOKUP, to search and return the correct name from it.
Our second example has a minor to our first one: We insert the 2D XLOOKUPs to a complete column. However, the basic structure and the arguments of the XLOOKUP functions remain more or less the same.
The goal: Fill in the correct values in column J by using two nested XLOOKUP functions on the Data table. For example, cell J9 should say “Dafna” because it’s the “First Name” of employee with ID “10580” (indicated by the orange arrow). In the following, we will use this cell (J9) to set up the 2D XLOOKUP function.
So, how do we start? Actually, it’s the same as before, applying the structure as described above.
=XLOOKUP(H9,$B$3:$B$100003,XLOOKUP(I9,$B$3:$F$3,$B$3:$F$100003))
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
The 2D XLOOKUP function is quite open for variations.
Don’t understand me wrong, of course the sequence of arguments matters in general. But you can switch the meaning of the inner and outer XLOOKUP. So, the inner XLOOKUP doesn’t necessarily have to return a vertical range of cells. It could also return a horizontal range.
Let’s illustrate that a little bit. The function of our first example was:
=XLOOKUP(I3,B3:B13,XLOOKUP(I4,B3:F3,B3:F13))
You can change it to:
=XLOOKUP(I4,C3:F3,XLOOKUP(I3,B4:B13,C4:F13))
Or easier to read:
Often it makes sense not only to refer to the exact same ranges. For example, when you permanently add data. In such case, it’s helpful to refer to entire columns or row.
Also within the 2D XLOOKUP function, you can refer to entire columns as shown in the screenshot below.
You can also refer to entire columns. But you can’t refer to both, entire rows and columns at the same time.
INDEX/MATCH/MATCH is the classic approach for a 2D lookup in Excel. Two MATCH functions are nested into the INDEX function. Before we explore the advantages and disadvantages, let’s start with a similarity: Both functions are not easy to set up for beginners. But after some trial and error – and a guide like this 😉 – both should be manageable. Plus, they require the same basic inputs just in a different structure.
That said: For basic usage, they both work. So, please feel free to use either one – maybe it’s time now to try the new, 2D XLOOKUP?
Please download the examples from this article here. They not only include the versions of 2D XLOOKUPs from in this article, but also more variation.
And even more: Besides the examples above, the download has an exercise sheet on which you can practice the 2D XLOOKUP. When you are done – or stuck – check the solution.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example