Lookup & Reference Excel 2021 ◉ 3 views

=XLOOKUP()

The desired value was not found — Fix: Clean the data, correct the data

Searches a range and returns the corresponding value in the result range (row-wise or column-wise)

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

Hover argument names in the table for details.

Output typeMixed

Popularity100 / 100

VolatileNo

Dynamic ArrayNo

📥 Download sample .xlsx — Coming soon ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas

⬇ Download Sample Workbook

A dedicated .xlsx for =XLOOKUP() with Persian sample data & live formulas — open in Excel and practice.

📥 Download XLOOKUP.xlsx — Coming soon ~ 8–12 KB · No macros

%d questions — syntax, example & errors — test yourself!

Start quiz →

Argument	Type	Required	Description
`lookup_value`	Any type	Required	Lookup value
`lookup_array`	Range	Required	Lookup range
`return_array`	Range	Required	Result range
`if_not_found`	Any type	Optional	What if error? — default: empty
`match_mode`	Number	Optional	Match type — default: 0

Example 1 =XLOOKUP(E2, A2:A100, C2:C100)

Example 2 =XLOOKUP(E2, A2:A100, C2:C100, "Not found")

The lookup range and the result range must be the same size.
If the result range has multiple columns, all those columns are returned with one formula.
If the value appears multiple times, the first one is returned; for the last, set the sixth argument to -1.
Not case-sensitive.