How to Use XLOOKUP in Excel (Syntax, Examples & Fixes)

/ How To Fix, Office & Email / By

XLOOKUP is a modern lookup function that can search one range and return a value from another. Unlike VLOOKUP, it can look left, does not rely on a column index number, and defaults to exact matching.

Does this affect you?

Use this for Excel for Microsoft 365, Excel 2021, and Excel for the web. XLOOKUP is not available in Excel 2019, Excel 2016, or older versions.

Write a basic XLOOKUP

The everyday version uses three main arguments.

  1. Click the result cell.
  2. Type =XLOOKUP(.
  3. Add the lookup value, such as A2.
  4. Add the lookup array, such as D2:D500.
  5. Add the return array, such as F2:F500.
  6. Close the formula and press Enter.
  7. Example: =XLOOKUP(A2,D2:D500,F2:F500).

The lookup and return arrays do not need to be adjacent. The return range can be to the left or right of the lookup range.

Show a custom not-found result

Use the fourth argument to replace #N/A with a useful message.

  1. Start with the working XLOOKUP.
  2. Add a fourth argument such as “Not found”.
  3. Example: =XLOOKUP(A2,D2:D500,F2:F500,”Not found”).
  4. Press Enter.

Look up with two criteria

Combine two fields into one lookup key.

  1. Join the lookup values with &, such as A2&B2.
  2. Join the two lookup arrays the same way, such as D2:D500&E2:E500.
  3. Use the return array normally.
  4. Example: =XLOOKUP(A2&B2,D2:D500&E2:E500,F2:F500).

More control

Use approximate match modes

XLOOKUP exact-matches by default. For bracket or tier lookups, use match mode -1 for exact or next smaller, or 1 for exact or next larger.

Fix #NAME?

#NAME? usually means the Excel version does not support XLOOKUP. Use INDEX and MATCH instead on older Excel versions.

Fix missing matches

Extra spaces, numbers stored as text, or hidden characters can cause #N/A. Try TRIM around text values and confirm the lookup and return ranges are the same length.

Inserted columns do not break it

XLOOKUP references the return range directly, so adding a column between lookup and return ranges does not change which column is returned.

Sources

  • Microsoft Support – XLOOKUP function (2025)
  • Microsoft Support – VLOOKUP, HLOOKUP, LOOKUP, INDEX, MATCH function comparison (2025)
  • Microsoft Support – IFNA function (2025)
Disclosure: This post may contain affiliate links which means I may receive a commission for purchases made through links. I will only recommend products that I have personally used! Learn more on my Private Policy page.
A thoughtful woman reads a newspaper while enjoying coffee at an indoor workspace.

DEALWEEK

SUBSCRIBE AND GET 20% OFF YOUR NEXT ORDER! OFFER ENDS SOON - DON’T MISS OUT!

We don’t spam! Read our privacy policy for more info.

Shopping Cart