XLOOKUP in Excel: what it does and how to use it
XLOOKUP takes a value, looks for it in one column, and returns the matching item from another. Unlike VLOOKUP the two columns can be anywhere, in any order, and you can say what should appear when nothing matches.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
When to use it
Use it for any lookup at all if your Excel has it. It replaces both VLOOKUP and the INDEX and MATCH pairing, and it is exact by default, so the commonest lookup mistake is simply not possible.
What trips people up
It needs Microsoft 365 or Excel 2021. On anything older the formula comes back as #NAME?, and VLOOKUP or INDEX with MATCH is the way round it.
Watch it built, step by step
Questions
What do the optional arguments do?
The fourth sets what appears when nothing matches, such as "Not found". The fifth sets the match type: 0 for exact, which is the default, -1 or 1 for the next smaller or larger value, 2 for wildcards. The sixth sets the direction, and -1 searches from the bottom up, which finds the most recent entry.
Can XLOOKUP return more than one column?
Yes. Give it a return range several columns wide and the results spill across into the neighbouring cells.
Does XLOOKUP work in Google Sheets?
Yes. Google Sheets has XLOOKUP with the same arguments, so a formula written for Excel works there unchanged.
Related functions
- VLOOKUP — VLOOKUP searches down the first column of a range for a value, and returns something from a column further to the right on the same row.
- INDEX and MATCH — MATCH finds which position a value sits at in a list, and INDEX returns whatever is at a given position.
- IFERROR — IFERROR runs a formula and, if it comes back as an error, shows what you asked for instead.
