XLOOKUP looks up a value and returns a matching result from another column, and it can search in any direction, which VLOOKUP cannot. The formula is =XLOOKUP(lookup_value, lookup_array, return_array, "if not found"). It gives an exact match by default and lets you set your own "not found" message. XLOOKUP is available in Excel 2021 and Microsoft 365, and once you learn it, you'll rarely go back to VLOOKUP.
Why XLOOKUP is better than VLOOKUP
VLOOKUP can only search the leftmost column and needs you to count column numbers. XLOOKUP fixes both problems: it can look left or right, you pick the return column directly, and it matches exactly by default. Fewer errors, less counting.
The XLOOKUP formula
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
- lookup_value — what you're searching for.
- lookup_array — the column to search in.
- return_array — the column to return the answer from.
- if_not_found — (optional) text to show when there's no match.
Step by step
Employee IDs in column A, names in B, salaries in C. To find a salary by ID typed in E2:
- Click the answer cell.
- Type
=XLOOKUP(E2,A2:A100,C2:C100,"Not found") - Press Enter.
XLOOKUP finds the ID in column A and returns the salary from column C. If the ID doesn't exist, it shows "Not found" instead of an error.
Looking to the left (VLOOKUP can't)
Suppose IDs are in column C and names in column A. VLOOKUP can't return a value to the left of the lookup column, but XLOOKUP can:
=XLOOKUP(E2, C2:C100, A2:A100)
It searches column C and returns from column A, no problem.
XLOOKUP vs VLOOKUP
| Point | VLOOKUP | XLOOKUP |
|---|---|---|
| Search direction | Left column only | Any direction |
| Column number | Count manually | Pick the array directly |
| Default match | Must type FALSE | Exact by default |
| If not found | #N/A | Custom message built in |
| Version | All versions | Excel 2021 / 365 |
Worked example
Product prices: codes in A, prices in B. A code is typed in D2.
=XLOOKUP(D2, A2:A50, B2:B50, "No such code")
If D2 matches a code, you get the price. If not, you get "No such code" instead of a confusing error.
Pro tips
- Use XLOOKUP whenever you're on Excel 2021 or Microsoft 365; it's simpler and safer than VLOOKUP.
- Always add the fourth argument (
"Not found") so blanks and typos don't show#N/A. - XLOOKUP can also return a whole row/column, useful for pulling several fields at once.
Common mistakes
- Mismatched array sizes. The lookup_array and return_array must cover the same rows.
- Expecting it in old Excel. XLOOKUP doesn't exist before Excel 2021. Use VLOOKUP or INDEX-MATCH there.
- Skipping the not-found text. Without it, missing values show
#N/Aagain.
Key takeaways
- XLOOKUP looks up a value and returns a match from any direction.
- Syntax: lookup_value, lookup_array, return_array, "if not found".
- It's exact-match by default and handles "not found" cleanly.
- Available in Excel 2021 and Microsoft 365.
Practice task
Make a table of product codes and prices with the code column on the right and the price on the left. Use XLOOKUP to return a price by code (looking left), and add a "Not found" message for missing codes.
Learn modern Excel, including XLOOKUP and dashboards, in the ADCA program at HCI.
Frequently Asked Questions
What is XLOOKUP in Excel?
XLOOKUP looks up a value and returns a matching result from another column. It can search in any direction and shows a custom message when there's no match.
What is the difference between XLOOKUP and VLOOKUP?
VLOOKUP only searches the leftmost column and needs a column number. XLOOKUP can look in any direction, returns an exact match by default, and handles "not found" text.
What is the XLOOKUP formula?
`=XLOOKUP(lookup_value, lookup_array, return_array, "if not found")`. The last argument sets what to show when nothing matches.
Which Excel versions have XLOOKUP?
XLOOKUP is available in Excel 2021 and Microsoft 365. Older versions don't have it, so use VLOOKUP or INDEX-MATCH there.
Can XLOOKUP look to the left?
Yes. Unlike VLOOKUP, XLOOKUP can search one column and return a value from a column to its left.