How to build XLOOKUP in Excel
- The first argument is the lookup value
Only one cell is expected
- Where to search the lookup value
Select only one column
- Where is the value to return
Select only one column
XLOOKUP was released only for Excel 365 in 2020. This function simplifies the way to look up data in a table.
Why the name XLOOKUP? There is a reason.
For many years, two Excel functions could look up inside a table
But both functions have their pros and cons.
| Functions | Pros | Cons |
|---|---|---|
| VLOOKUP | Fairly simple to build | The lookup column is always the first one |
| INDEX | Lookup in any direction - horizontal - vertical - and in any column, even before the lookup column | Lookup by position value (3rd row, 5th column) and not by value |
XLOOKUP keeps the pros of these two functions and removes the cons.
And also XLOOKUP name combines the two other functions' names.
- X comes from the indeX function
- LOOKUP comes from vLOOKUP function
How to lookup for value with XLOOKUP?
For instance, we want to return the area according to the country name. The data are inserted in a Table. For that, the reference of the formula will display the column name of the Table. It's easier to read
=XLOOKUP(C11,tbl_Country[Country],tbl_Country[Area])
Lookup to the left
Next, you want to return the Capital name. But the Capital column is on the left of the Calling Code (the lookup column).
Performing a lookup to the left is impossible with VLOOKUP but with XLOOKUP, it's very easy to do.
=XLOOKUP(C11,tbl_Country[Calling code],tbl_Country[Capital]))
The improvement over other functions.
Return more than 1 column.
In Excel 365, the calculation engine can now return the result in multiple cells. In this context, the XLOOKUP function can
- Perform a multi-column search
- Either return multiple columns
For instance, according to the country name, we return to the Area and the Population.
Manage #N/A (Unknown value)
When an unknown value is a lookup, VLOOKUP and INDEX return #N/A. But now, XLOOKUP can return a custom result if the value is unknown.
In this situation, you must fill the 4th argument of the XLOOKUP function of Excel.
And with the 4th argument
