XLOOKUP
Look a value up in one column and read the answer from another.
Look a value up in one column and read the answer from another
The value from the return column in the row that matched, or your fallback
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Required | What goes here |
|---|---|---|
lookup_value | Required | The value to find, like the account code in A2. |
lookup_array | Required | The one column holding the values to search, like $E$2:$E$40. |
return_array | Required | The column the answer comes from. Same rows, different column. |
if_not_found | Optional | What to show when there is no match, like "Not on file". |
match_mode | Optional | Leave it out for an exact match. |
search_mode | Optional | 1 searches from the top, -1 from the bottom. |
How to use XLOOKUP
XLOOKUP takes what to look for, the column to look in, and the column to read from. The two columns are named separately rather than being counted inside one block.
That fixes the two things that make VLOOKUP fragile. Nothing is counted, so inserting a column cannot break it, and the column you read from can be anywhere, including to the left of the one you matched on.
It matches exactly by default, so there is no fourth argument to remember and no approximate match waiting to catch you out.
The fourth argument is what to show when nothing matched, which does the job IFERROR was being used for, without also hiding your own mistakes.
It is the function a student meets first in any current install, and the one worth reaching for unless the file has to open in an old version.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Invoice | Supplier | Status | Days overdue | Amount | Supplier | Terms (days) | |
| 2 | INV-8801 | Brightwell Paper | Overdue | 12 | 940 | Ashgrove Catering | 30 | |
| 3 | INV-8802 | Calder Utilities | Queried | 47 | 1180 | Brightwell Paper | 30 | |
| 4 | INV-8803 | Meridian Freight | Overdue | 63 | 2450 | Calder Utilities | 14 | |
| 5 | INV-8804 | Ashgrove Catering | On terms | 0 | 405 | Meridian Freight | 45 | |
| 6 | INV-8805 | Brightwell Paper | Overdue | 38 | 760 | Thornbury Print | 60 | |
| 7 | INV-8806 | Thornbury Print | Overdue | 91 | 1320 | |||
| 8 | INV-8807 | Meridian Freight | On terms | 0 | 580 | |||
| 9 | INV-8808 | Calder Utilities | Overdue | 24 | 215 |
| Formula | Result |
|---|---|
=XLOOKUP(B2,G2:G6,H2:H6)Look in the supplier column, read from the terms column. No column number anywhere. | 30 |
=XLOOKUP("Harbour Ltd",G2:G6,H2:H6,"Not on file")The fourth argument answers when nothing matched, and unlike IFERROR it does not also swallow a mistake in the formula itself. | Not on file |
=XLOOKUP(2450,E2:E9,A2:A9)Reading leftwards, from an amount back to its invoice number. VLOOKUP cannot do this at all. | INV-8803 |
=XLOOKUP(MAX(E2:E9),E2:E9,B2:B9)Which supplier the largest invoice belongs to, in one formula. | Meridian Freight |
Notes
- XLOOKUP matches exactly unless you tell it otherwise, which is the opposite of VLOOKUP's default and the safer way round.
- The lookup column and the return column must be the same length.
- It is not in versions before Excel 2021 or a current Microsoft 365. INDEX with MATCH is the version that opens anywhere.
Related
A reference tells you what it does. A unit makes you use it.