Look up a value, with a fallback
The lookup that replaced VLOOKUP: exact match by default, any direction, and a built-in answer for when the value isn't found.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →The fourth argument is the whole upgrade: VLOOKUP returns #N/A on a miss and forces an IFERROR wrap around everything; XLOOKUP takes the fallback as part of the lookup itself.
- →Search and return are separate ranges, so the return column can sit LEFT of the search column — the restriction that made VLOOKUP layouts contort for years is simply gone.
- →Exact match is the default. VLOOKUP's silent default was approximate match, which is the classic source of confidently wrong lookups on unsorted data.
The fine print
- →Needs Excel 2021/365 or Google Sheets; older Excel wants INDEX+MATCH instead (that item is in this collection too).
- →Whole-column references are convenient but slow on very large sheets — bound the ranges (A2:A5000) when a workbook starts to crawl.
- →The two ranges must be the same length or the formula returns #VALUE!.
Part by part
- =XLOOKUP(
- One function replacing INDEX/MATCH and VLOOKUP. It defaults to an exact match, which removes the most common source of silently wrong lookups.
- {{lookup_range}}, {{return_range}}
- Two separate ranges, so the returned column may sit anywhere — left of the lookup column included, which VLOOKUP cannot do.
- {{if_missing}}
- The not-found value, built in. It is why this needs no IFERROR wrapper, and why it does not hide unrelated errors the way one would.
Common mistakes
=IFERROR(XLOOKUP(A2, D:D, E:E), "")Wrapping in IFERROR hides #REF! from a deleted column and #NAME? from a typo alongside the missing lookup. The fourth argument catches only not-found, which is the one you meant.
=XLOOKUP(A2, D:D, E:E, "")=XLOOKUP(A2, D2:D100, E2:E500)Mismatched range heights return the wrong row or an error. The two ranges must line up exactly, and whole-column references are safer than hand-typed bounds.
Assuming it works everywhere.XLOOKUP requires Microsoft 365 or Google Sheets. A workbook shared with anyone on Excel 2019 or earlier shows #NAME? throughout, which looks like a corrupted file.
Questions
Why is this better than VLOOKUP?
Exact match by default, a return column that can sit anywhere, a built-in not-found value, and no column number to go stale when someone inserts a column. Each removes a specific way VLOOKUP fails quietly.
What should the fallback be?
An empty string when absence is expected, or a short message such as not found when the reader needs to know. Avoid zero, which asserts a value rather than reporting its absence.
Can it search from the bottom up?
Yes — the sixth argument sets the search mode, and -1 searches last to first. Useful for finding the most recent entry in a log-style sheet without sorting it.
What if my colleagues do not have it?
Use INDEX/MATCH, which works in every version and does the same job in two functions. Mixing both in one workbook is the worst option, since half the formulas break for half the people.