XLOOKUP has been released, only for Microsoft 365. This has changed the way to find data in document.
Merger of VLOOKUP and INDEX
The functions VLOOKUP and INDEX return a value from a reference table. But both functions have there pros and cons.
| VLOOKUP | INDEX | |
| Pros | Easy to build | Research to the left |
| Cons | Exact match isn't the default research | Need the position in the range |
XLOOKUP gather the pros of these 2 functions and remove the cons 😍😀👍.
- X comes from the indeX function
- LOOKUP comes from vLOOKUP function
How to build XLOOKUP?
XLOOKUP is easier to build that VLOOKUP or INDEX.
- First, the value to search
- Second, the column where to search the value
- Third, the column to return
Examples of research with XLOOKUP
For instance here, we want to return the city according to the ID, the email. I have put my data in a Table to visualise the column name in the formula.
=XLOOKUP(C2,TbClient[EmailAddress],TbClient[City])
But now, If I want to return a value from a column on the left of my pivot column, I just have to change the 3rd argument and select the new column
The location of the columns has no impact on the research (better than VLOOKUP 😉)
=XLOOKUP(C2,TbClient[EmailAddress],TbClient[GivenName])
Return more than 1 column
XLOOKUP is available only with Microsoft 365 and Excel Online and they manage dynamic arrays.
A dynamic arrays means that a formula can return a result in more than 1 cell. So, if you fill a range of cells for the 3rd argument, then the function will return the result in more than 1 column.
Manage #N/A (Unknown value)
In Excel, when a desired value doesn't exist in the reference table, the functions VLOOKUP or INDEX return #N/A. In some situation, like compare 2 columns, #N/A is the result that we want to find.
With XLOOKUP, it's the same. Here, the email is unknown so the result is #N/A.
But, if you fill the 4th argument of the function, you can specify a result in case of error in the search.
