Featured image
XLOOKUP in Excel
XLOOKUP searches one range for a value and returns the matching entry from a second range: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]). Because the two ranges are given separately it can look in either direction, it defaults to an exact match, and the fourth argument replaces the usual IFERROR wrapper.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
XLOOKUP is what VLOOKUP would have been if it had been designed after thirty years of watching people use VLOOKUP. The two structural problems it fixes are the ones that cause most lookup failures: the answer is no longer addressed by counting columns, and the thing you search is no longer required to sit to the left of the thing you want.
It is available in Microsoft 365 and Excel 2021 onwards. In anything older, INDEX with MATCH does the same job and is worth learning for that reason alone.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- What you already know. Usually a reference to a cell rather than a value typed into the formula.
- The single column, or row, to search in. Not a whole table: just the one strip of cells the lookup value should be found in.
- The strip of cells the answer comes out of, the same height as lookup_array. Nothing requires it to be to the right, which is the whole point.
- optional
- What to show when nothing matches. Supply it and XLOOKUP stops returning #N/A, with no IFERROR wrapper and no risk of hiding a different error at the same time.
- optional
- 0 for exact, the default and almost always what you want. The other modes handle next-smaller, next-larger and wildcard matching, which are the banded-lookup cases VLOOKUP's TRUE covers.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
Find one person's revenue
=XLOOKUP("Dara", A2:A7, D2:D7)Returns 1200
Search column A, return the matching cell from column D. There is no column number to count and no table range that has to start at the search column, so inserting a column between A and D cannot change the answer.
Look leftwards, which VLOOKUP cannot do
=XLOOKUP(7600, D2:D7, A2:A7)Returns Chen
The same formula with the two ranges swapped: search the revenue column, return the name. VLOOKUP has no way to express this because it always searches the first column of its table and reads rightwards.
Say something useful when there is no match
=XLOOKUP("Gus", A2:A7, D2:D7, "Not on the list")Returns Not on the list
The fourth argument is only used when the search fails. Note what it does not do: a #REF! or a #VALUE! caused by something genuinely wrong with the formula still comes through, which is exactly the behaviour you want and exactly what IFERROR takes away.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
XLOOKUP compared with VLOOKUP
- Direction. VLOOKUP searches the first column of its table and reads rightwards. XLOOKUP takes two independent ranges and does not care which is further left.
- Addressing the answer. VLOOKUP counts columns, so inserting a column inside the table silently changes what the formula returns. XLOOKUP points at the return range directly, so an inserted column moves the range with it.
- The default. VLOOKUP defaults to approximate match, the mode most likely to be wrong. XLOOKUP defaults to exact.
- Not found. VLOOKUP returns #N/A and needs IFNA around it. XLOOKUP has an argument for it.
- Availability. VLOOKUP is in every version of Excel. XLOOKUP needs Microsoft 365 or Excel 2021.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=XLOOKUP("Gus", A2:A7, D2:D7)WhyNothing in lookup_array equals the lookup value and no if_not_found argument was supplied.
FixEither the value genuinely is not there, in which case add a fourth argument such as "Not found", or the two values differ invisibly. Trailing spaces and numbers stored as text are the two usual culprits; TRIM and ISNUMBER find both.
=XLOOKUP("Dara", A2:A7, D2:D6)Whylookup_array and return_array are different heights. Six rows are being searched and five are available to answer from.
FixMake both ranges end on the same row. A2:A7 against D2:D6 cannot work: there is no sensible row for a match in the sixth position to map to. This is the failure a stray extra row picked up during a drag selection produces.
What goes wrong
These are the mistakes that come up again and again with XLOOKUP. Each one is reasonable. That is why people make it.
Passing a whole table as lookup_array, out of VLOOKUP habit.
WhyVLOOKUP's second argument is the entire table, so the hand types one.
Insteadlookup_array is one column. The table as a whole never appears in an XLOOKUP at all.
Ranges of different heights.
WhyThe two ranges are selected separately, in two drags, and it is easy for the second to run a row further.
InsteadCheck that both end on the same row. A2:A7 and D2:D7, not A2:A7 and D2:D8.
Wrapping XLOOKUP in IFERROR.
WhyIt is the reflex built up over years of VLOOKUP.
InsteadUse the fourth argument. IFERROR would also swallow the errors that mean the formula itself is wrong, which is the one kind of error worth seeing.
Common questions
Is XLOOKUP better than VLOOKUP?
For writing something new, yes: exact match by default, no column counting, searches in either direction, and a built-in not-found message. For reading an existing workbook, VLOOKUP is still what you will find in it.
Why does my XLOOKUP say #N/A?
Nothing in the lookup array equals the lookup value. Usually that is a trailing space or a number stored as text rather than a genuinely missing row. Supplying the fourth argument turns the error into a message, but it does not make the match happen.
Can XLOOKUP return more than one column?
In Excel it can, by giving it a multi-column return array, and the result spills across adjacent cells. The grid on this site does not implement spill ranges, so the examples here return a single value.
Which versions of Excel have XLOOKUP?
Microsoft 365 and Excel 2021 onwards. In Excel 2019 and earlier, use INDEX with MATCH.