Excel XLOOKUP function — the modern VLOOKUP

XLOOKUP finds a value in one column and returns the result from another. Unlike VLOOKUP it can look to the left, matches exactly by default and shows your own message when nothing is found.

In other languages: ÇAPRAZARA (Türkçe), ПРОСМОТРX (Русский)Only in Excel 2021 / Microsoft 365

Syntax

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Arguments

lookup_value: The value to find.
lookup_array: The column to search.
return_array: The column to return from (same size).
if_not_found: Optional: text to show when nothing is found.

Example

Find the price for the code in E2 (P-102), or write "Not found".

ABC

1CodeProductPrice

2P-101Laptop1200

3P-102Monitor350

4P-103Printer280

Type the formula in F2

The formula in your Excel

English Excel=XLOOKUP(E2,A2:A4,C2:C4,"Not found")
Türkçe Excel=ÇAPRAZARA(E2;A2:A4;C2:C4;"Not found")
Русский Excel=ПРОСМОТРX(E2;A2:A4;C2:C4;"Not found")

350 for P-102; "Not found" for a code that is not in the list.

✨ Have the AI explain this formula

Useful tips

The return column can be to the left of the search column.
Older Excel (2019 and earlier) has no XLOOKUP — use INDEX + MATCH instead.

Common errors

#NAME? — your Excel version has no XLOOKUP.
#VALUE! — the search and return columns are not the same size.