Excel practice › Excel functions › XLOOKUP

XLOOKUP in Excel: how it works, with examples you can edit

XLOOKUP looks for a value in one column (or row) and returns the value at the same position in another. To get the name of vendor V-331 when the IDs are in C2:C7 and the names in A2:A7: =XLOOKUP("V-331", C2:C7, A2:A7).

You point at two ranges, where to look and what to return, so the result can sit to the left or to the right of the column you search. Exact match is the default. XLOOKUP exists in Excel 2021, Excel 2024 and Microsoft 365. Excel 2016 and 2019 don't have it.

Skip to the 3 exercises ↓

Syntax

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

lookup_value
What to look for: a value or, more often, the cell that holds it.
lookup_array
The one column (or row) to search.
return_array
The column (or row) to return from. XLOOKUP returns the item at the same position as the match, so this range has to cover the same rows as lookup_array.
if_not_found
Optional. What to return when there is no match. Without it, a missing value gives #N/A.
match_mode
Optional. 0 = exact match, the default. -1 = exact match, or the next smaller item. 1 = exact match, or the next larger item. 2 = wildcard match with *, ? and ~.
search_mode
Optional. 1 = search from the first item, the default. -1 = search from the last item. 2 and -2 = binary search on data sorted ascending or descending.
Live exampleVendor name for the ID in F2
G2fx
ABCDEFG
1VendorCategoryVendor IDNet daysFind IDVendor
2Apex PaperSuppliesV-20430V-331Coastal Freight
3Blue Ridge ITServicesV-11745
4Coastal FreightShippingV-33115
5Delgado ElectricRepairsV-14220
6Evergreen CleanServicesV-25660
7Fulton PrintSuppliesV-30910

How Excel reads it

  1. It takes the value in F2 (V-331) and goes down C2:C7 until it finds it: the third cell.
  2. It returns the third cell of A2:A7: Coastal Freight.

If the ID appears twice, XLOOKUP returns the first one it meets. The lookup column and the return column are two separate ranges, so their order on the sheet doesn't matter. Here the name is left of the ID, which VLOOKUP can't handle.

What changes compared with VLOOKUP

The two ways it goes wrong

Both are live below. Fix the formula in the box and watch the cell.

1. The value isn't in the lookup range. There is no vendor V-400, so the lookup ends in #N/A. That is the honest answer, but it spreads to every formula that uses this cell. To replace it, add a fourth argument: =XLOOKUP(F2,C2:C7,A2:A7,"No such vendor").

Live example#N/A: an ID that isn't in the list
G2fx
ABCDEFG
1VendorCategoryVendor IDNet daysFind IDVendor
2Apex PaperSuppliesV-20430V-400#N/A
3Blue Ridge ITServicesV-11745
4Coastal FreightShippingV-33115
5Delgado ElectricRepairsV-14220
6Evergreen CleanServicesV-25660
7Fulton PrintSuppliesV-30910

2. The two ranges don't line up. XLOOKUP returns the item at the same position as the match. If the return range starts one row lower than the lookup range, every answer comes from one row too low, and there is no error to warn you. Blue Ridge IT (V-117) is paid in 45 days. This formula says otherwise:

Live exampleWrong row: ranges that start on different rows
G2fx
ABCDEFG
1VendorCategoryVendor IDNet daysFind IDNet days
2Apex PaperSuppliesV-20430V-11715
3Blue Ridge ITServicesV-11745
4Coastal FreightShippingV-33115
5Delgado ElectricRepairsV-14220
6Evergreen CleanServicesV-25660
7Fulton PrintSuppliesV-30910

What XLOOKUP also does in Excel

The sheets on this page run exact matches that return one value. In Excel, XLOOKUP has more to offer:

Practice: 3 exercises

Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.

Exercise 1Category of one vendor

Someone types a vendor ID in F2. In G2, return that vendor's category.

G2fx
ABCDEFG
1VendorCategoryVendor IDNet daysFind IDCategory
2Apex PaperSuppliesV-20430V-142
3Blue Ridge ITServicesV-11745
4Coastal FreightShippingV-33115
5Delgado ElectricRepairsV-14220
6Evergreen CleanServicesV-25660
7Fulton PrintSuppliesV-30910
Show the answer

=XLOOKUP(F2,C2:C7,B2:B7) gives Repairs.

Look in the ID column (C2:C7), return from the category column (B2:B7). The category is left of the ID, and that makes no difference to XLOOKUP.

Exercise 2A missing ID

V-500 isn't in the list, so a plain XLOOKUP shows #N/A. In G2, return the vendor name for the ID in F2, or the text Not found when the ID doesn't exist.

G2fx
ABCDEFG
1VendorCategoryVendor IDNet daysFind IDVendor
2Apex PaperSuppliesV-20430V-500
3Blue Ridge ITServicesV-11745
4Coastal FreightShippingV-33115
5Delgado ElectricRepairsV-14220
6Evergreen CleanServicesV-25660
7Fulton PrintSuppliesV-30910
Show the answer

=XLOOKUP(F2,C2:C7,A2:A7,"Not found") gives Not found.

The fourth argument, if_not_found, is returned only when the lookup finds nothing. Wrapping the lookup in IFNA gives the same result.

Exercise 3Look up a value you calculate first

Which vendor has to be paid soonest? In G2, return the name of the vendor with the smallest number in the Net days column.

G2fx
ABCDEFG
1VendorCategoryVendor IDNet daysPay first
2Apex PaperSuppliesV-20430
3Blue Ridge ITServicesV-11745
4Coastal FreightShippingV-33115
5Delgado ElectricRepairsV-14220
6Evergreen CleanServicesV-25660
7Fulton PrintSuppliesV-30910
Show the answer

=XLOOKUP(MIN(D2:D7),D2:D7,A2:A7) gives Fulton Print.

MIN(D2:D7) gives 10. XLOOKUP then finds 10 in the Net days column and returns the name from the same row. The lookup value can be any formula, not only a cell.

Questions people ask

What is an XLOOKUP in Excel?

A lookup function: it searches one column or row for a value and returns the item at the same position in another column or row. It does the job of VLOOKUP and HLOOKUP, with exact match as the default and an optional value to show when nothing is found.

XLOOKUP vs VLOOKUP: which one should I use?

If your Excel has it, XLOOKUP leaves less room for mistakes: no column number, no FALSE to forget, and it can return a column to the left. VLOOKUP also works in Excel 2016 and 2019, where XLOOKUP doesn't exist, so it is still what you'll find in older workbooks, and it's worth being able to read both.

Why does my XLOOKUP return #N/A?

Because the lookup value isn't in lookup_array exactly as written. A different spelling or a space at the end of one of the two values is enough. Check too that lookup_array is the column that holds those values. More causes, each with a formula to fix, are on the #N/A error page.

Does XLOOKUP work in Excel 2016 or 2019?

No. Microsoft's page says XLOOKUP is not available in Excel 2016 and Excel 2019. In those versions, INDEX with MATCH does the same lookups, including the ones to the left.

Keep going

Lesson 4 is six XLOOKUP drills on an online store's product list: lookups in both directions, the not-found argument, XLOOKUP inside IF and inside a calculation. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

VLOOKUP function, with live examplesINDEX MATCH, with live examplesIFERROR and IFNA, with live examples#N/A error in Excel: meaning and fixesFILTER function, with live examplesXLOOKUP practice exercises (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: XLOOKUP function. Last checked November 2026.