XLOOKUP (CERCA.X in Italian) is the function Microsoft introduced to replace VLOOKUP and HLOOKUP. It is available in Excel for Microsoft 365 and the most recent versions, in Excel for the web, in Google Sheets and in recent versions of LibreOffice Calc. If the spreadsheet must also open in older versions, weigh that first; otherwise it is worth adopting as your default choice.
The core idea
VLOOKUP thinks in terms of a table and a column number. XLOOKUP thinks in terms of two separate ranges: where to look and what to return. This seemingly small difference removes most of the problems.
=XLOOKUP(value, lookup_array, return_array)
With a price list holding codes in A and prices in D:
=XLOOKUP(A2, PriceList!$A$2:$A$500, PriceList!$D$2:$D$500)
There is no index to count. If someone inserts a column in the price list, the ranges shift on their own and the formula keeps returning the price.
The six arguments
The full syntax is longer, but the arguments after the third are optional:
| Argument | Meaning | Default |
|---|---|---|
| value | what you are looking for | required |
| lookup_array | where to look for it | required |
| return_array | what to return | required |
| if_not_found | text or value when missing | #N/A error |
| match_mode | exact, smaller, larger, wildcards | exact (0) |
| search_mode | from the start, from the end, binary | from the start (1) |
Exact match as the default behaviour is already a big step forward: the classic VLOOKUP mistake, forgetting FALSE and getting wrong data, simply cannot happen here.
Practical examples
Looking to the left
You have a staff list with the employee number in A and the surname in C. You want to find the number starting from the surname. With VLOOKUP this is impossible without rearranging the columns; with XLOOKUP you just swap the ranges:
=XLOOKUP("Rossi", $C$2:$C$200, $A$2:$A$200)
A clear message when the value is missing
The fourth argument replaces IFERROR and, unlike it, only catches the "not found" case:
=XLOOKUP(A2, Codes, Prices, "Code missing")
Here Codes and Prices are named ranges, which make the formula much easier to read.
The last occurrence
In a transaction log the same customer appears many times and you want the date of their latest order. Setting the search mode to -1 makes the function start from the bottom:
=XLOOKUP(F2, $B$2:$B$5000, $A$2:$A$5000, "No orders", 0, -1)
If the log is in chronological order, you get the most recent order without complicated formulas.
Bands and tiers without sorting
To assign a discount band based on quantity you use match mode -1, meaning "exact match or the next smaller value":
=XLOOKUP(B2, $F$2:$F$5, $G$2:$G$5, , -1)
Unlike VLOOKUP's approximate match, the threshold table does not have to be sorted. Sorting it is still good practice, because people reading the sheet understand it better.
Returning several columns at once
If the return array spans several columns, XLOOKUP returns the whole row. For example:
=XLOOKUP(A2, PriceList!$A$2:$A$500, PriceList!$B$2:$D$500)
returns description, category and price in three adjacent cells. In versions with dynamic arrays the result "spills" to the right automatically. If the destination cells are not empty you get the #SPILL! error (#ESPANSIONE! in Italian): just clear them.
Two-way lookup
By nesting two XLOOKUPs you can find a value at the intersection of a row and a column, for example the sales of a product in a given month:
=XLOOKUP(H2, $A$2:$A$50, XLOOKUP(H3, $B$1:$M$1, $B$2:$M$50))
The inner function returns the whole column for the month given in H3; the outer one looks up the product in H2 and takes the matching value from that column.
Practical cautions
- The ranges must be the same height. If the lookup array runs from row 2 to 500 and the return array from 2 to 499, you get a #VALUE! error.
- Compatibility. A file using XLOOKUP opened in an Excel version that does not support it shows errors instead of results. If the sheet circulates in a company with mixed installations, check first.
- Wildcards. With match mode 2 you can use
*and?in the lookup value, for example"Ross*". Without that mode the asterisk is searched for as an ordinary character. - Spaces and text versus number. As with any lookup, values that look the same but are not remain the first suspect when #N/A appears.
In short
- XLOOKUP uses two separate ranges, so there are no column indexes to count.
- Exact match is the default behaviour.
- The fourth argument handles missing values without hiding other errors.
- With search mode -1 you find the last occurrence of a value.
- Before adopting it, make sure everyone receiving the file has a compatible version.
