Ravindra BagaleCourses & study guides Track your progress

Excel XLOOKUP Function

By · Last updated:

Chala mitrano — today we learn XLOOKUP. It is the modern replacement for VLOOKUP: look left or right, and give a clear message when the ID is missing.

Fictional sample data for learning. Kirana + online grocery orders (Pune / Birmingham). Mixed ₹ and £. Names are fictional.

Purpose

Find a value in one column (or row) and return a matching value from another column (or row). Works left-to-right and right-to-left, and can return a friendly message when nothing matches.

Return value

The matching value from the return array, or the if_not_found value you set. Can also return multiple columns when combined with other functions.

Syntax

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

Arguments

ArgumentRequired?Description
lookup_valueRequiredThe value you want to find (cell, number, or text).
lookup_arrayRequiredThe one-column (or one-row) range to search in.
return_arrayRequiredThe one-column (or one-row) range to return from. Same height/width as lookup_array.
if_not_foundOptionalWhat to show when nothing matches (text or value). If omitted, Excel shows #N/A.
match_modeOptional0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
search_modeOptional1 first-to-last (default), -1 last-to-first, 2 binary asc, -2 binary desc.

Version

Microsoft 365, Excel 2021. Not in Excel 2019 or older. LibreOffice Calc: limited / may be missing — use INDEX+MATCH as a fallback there.

How to use — Why → What → How

Why: VLOOKUP can only look to the right and breaks when you insert a column. Interviews and real MIS sheets need a lookup that stays stable and can search either side.

What: XLOOKUP searches lookup_array for lookup_value and returns the matching cell from return_array. Optional arguments control “not found”, wildcards and search direction.

How — numbered steps:

  1. Click the cell where the answer should appear.
  2. Type =XLOOKUP( then click the lookup value (example: order ID).
  3. Select the lookup column (IDs), comma, then the return column (City or Amount).
  4. Add a fourth argument like "Not found" so missing IDs stay readable.
  5. Press Enter. Copy the formula down if you need many rows.

Ravindra Bagale's Tip

Friends, always put if_not_found for IDs and phone numbers. A clean “Not found” looks better in a report than a red #N/A.

Ravindra Bagale's Tip

Keep lookup_array and return_array the same height. If one is longer, XLOOKUP can spill wrong or throw #VALUE!.

Download practice file: practice-xlookup.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) Exact match — city for an order ID

Small kirana sheet. Find the city for order ORD-104.

OrderCityAmount (₹)
ORD-101Pune1,250
ORD-104Nashik890
ORD-107Pune2,100
Formula: =XLOOKUP("ORD-104", A2:A4, B2:B4)

Result: Nashik

What it means: Excel finds ORD-104 in the Order column and returns the City from the same row.

2) Friendly message when ID is missing

Manager typed ORD-999 by mistake.

OrderCity
ORD-101Pune
ORD-104Nashik
Formula: =XLOOKUP("ORD-999", A2:A3, B2:B3, "Not found")

Result: Not found

What it means: No match, so the fourth argument appears instead of #N/A.

3) Look to the left (VLOOKUP cannot do this)

City is in column A; Order ID is in column B. Return the order for Nashik.

CityOrder
PuneORD-101
NashikORD-104
BirminghamORD-110
Formula: =XLOOKUP("Nashik", A2:A4, B2:B4)

Result: ORD-104

What it means: Lookup array is City; return array is Order — left-side return works naturally.

4) Return amount in £ for a UK order

Same list has a Birmingham order in pounds.

OrderCityAmount
ORD-101Pune₹1,250
ORD-110Birmingham£48.50
ORD-104Nashik₹890
Formula: =XLOOKUP("ORD-110", A2:A4, C2:C4, "Missing")

Result: £48.50

What it means: Exact match on ORD-110 returns the Amount cell as stored.

5) Last match with search_mode -1

Customer Priya placed two orders; you want the latest row.

CustomerOrder
Priya SharmaORD-101
James ColeORD-102
Priya SharmaORD-118
Formula: =XLOOKUP("Priya Sharma", A2:A4, B2:B4, "None", 0, -1)

Result: ORD-118

What it means: search_mode -1 starts from the bottom, so the last Priya row wins.

Common errors and fixes

#N/A — No match and you omitted if_not_found. Fix: Add a fourth argument, or wrap with IFNA.
#VALUE! — lookup_array and return_array are different sizes. Fix: Select equal-height columns (or equal-width rows).
#SPILL! — Return would overwrite filled cells when spilling. Fix: Clear cells below/right, or return a single column only.
Wrong city / old amount — Duplicate keys; default returns the first match. Fix: Use search_mode -1 for last match, or clean duplicates.

Notes and tips

  • Default match is exact (match_mode 0) — safer than VLOOKUP’s approximate habit.
  • Wildcards (* and ?) need match_mode 2.
  • For many columns back, people often nest XLOOKUP or use FILTER; keep examples simple first.
  • On Excel 2019 and older, use INDEX + MATCH instead.

VLOOKUP, INDEX, MATCH, FILTER, IFNA

Practice tasks (10)

  1. Task 1. On sheet Orders, return City for order ORD-104 in cell G2.
    Hint: lookup the Order column; return the City column.
  2. Task 2. In G3, look up ORD-999 and show the text Missing ID when not found.
    Hint: use the fourth argument.
  3. Task 3. In G4, return Amount for Birmingham’s order (look up by City).
    Hint: City can be the lookup_array.
  4. Task 4. Find the last order ID for customer Priya Sharma.
    Hint: search_mode is the sixth argument.
  5. Task 5. Return the Amount for ORD-110 (UK row).
    Hint: exact match on Order; return Amount.
  6. Task 6. Write an XLOOKUP that returns Not in list for a blank lookup cell.
    Hint: still set if_not_found; test with an empty cell.
  7. Task 7. Change your City lookup so a wrong spelling shows Check spelling.
    Hint: fourth argument is just text.
  8. Task 8. Using the practice file, return City for the order in cell F2 (cell reference, not typed text).
    Hint: lookup_value should be F2, not a quoted ID.
  9. Task 9. Explain in one line why XLOOKUP is safer than VLOOKUP when someone inserts a column.
    Hint: think about column index numbers.
  10. Task 10. Bonus: return Amount for ORD-101 and format the result cell as ₹.
    Hint: formula first; number format is separate.

Quick quiz

Q1. XLOOKUP can return a value from a column to the left of the lookup column.
Q2. Which argument stops a red #N/A when nothing matches?
Q3. search_mode -1 means:
Q4. XLOOKUP is available in Excel 2016 by default.

Answers: Q1=True, Q2=if_not_found, Q3=Last match, Q4=False (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: XLOOKUP
XLOOKUP revision card