ExcelFlux

XLOOKUP function

XLOOKUP searches one range and returns the value at the same position in another. The default is an exact match.

Lookup and reference

Where can you use it?

Reporting and operations teams. Use functions in this group to retrieve a field from an item, employee or order list, select the right record, or move values into another report.

Scenario in the workbook

Look up a region by employee ID. Supply a message for missing codes.

A worked example from the workbook

RowABC
6ValueComparisonRegion
7E01E01North
8E02E02East
9E99E03West
Record 1
=XLOOKUP(A7,$B$7:$B$9,$C$7:$C$9,"Missing")

Expected resultNorth

See the inputs and instructions for all 3 examples

Copyable formulas

English Excel · en-US
=XLOOKUP(E2,B2:B4,A2:A4,"Not found")
Turkish Excel · tr-TR
=ÇAPRAZARA(E2;B2:B4;A2:A4;"Bulunamadı")

Sample data

Enter the headers and sample records in A1:B4.

Price Product ID
125 K-10
150 K-20
95 K-30
  • E2 = K-20

Enter the formula in E3.

To try the Turkish formula, use the sample data on the Turkish page.

Expected result

150

How it works

The return range can be to the left of the lookup range. K-99 uses the fourth argument. With duplicate IDs, this formula returns the first match from the top.

Syntax

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

Compatibility

Excel for Microsoft 365, Excel 2021, and Excel 2024.

Worked examples

Look up a region by employee ID. Supply a message for missing codes.

Open the English sheet. Inputs begin at A6. F7:F9 show the live results and G7:G9 show the original results for comparison.

Row A B C
6 Value Comparison Region
7 E01 E01 North
8 E02 E02 East
9 E99 E03 West
Case Formula Expected result
Record 1 =XLOOKUP(A7,$B$7:$B$9,$C$7:$C$9,"Missing") North
Record 2 =XLOOKUP(A8,$B$7:$B$9,$C$7:$C$9,"Missing") East
Record 3 =XLOOKUP(A9,$B$7:$B$9,$C$7:$C$9,"Missing") Missing
Microsoft Excel reference