Excel troubleshooting

Why Is XLOOKUP Not Finding a Value?

Troubleshoot XLOOKUP not finding values in Excel by checking match mode, data types, hidden spaces, and lookup array alignment.

Problem

  • XLOOKUP returns no match when the lookup value and lookup array values are not truly the same.

Quick fix

  • Check for extra spaces, mismatched text/number types, wrong lookup_array, and match_mode settings before adding if_not_found text.

What to check first

  • Confirm the lookup_array is the column or row that contains the value you are searching for.
  • Compare data types with ISNUMBER or ISTEXT if the values look identical but do not match.
  • Use TRIM for extra spaces and CLEAN for non-printing characters.

Formula fixes

  • Use the if_not_found argument to show a helpful message after confirming the lookup is correct.
  • Use match_mode 0 for exact match, 2 for wildcard match, or -1/1 for approximate matching.
  • Make sure lookup_array and return_array have compatible sizes.

Overview

  • When XLOOKUP fails, check match_mode, array alignment, and data types before changing the return column. if_not_found is for display — not a substitute for a valid match.

Diagnostic checklist

  • match_mode 0 for exact IDs?
  • lookup_array and return_array same row count?
  • Binary search modes only on sorted columns?
  • Wildcards only with match_mode 2?

Wrong value vs #N/A

  • #N/A — no match found with current mode.
  • Wrong value — approximate match or unsorted binary search.
  • Test with =XMATCH(E2,A:A,0) or =MATCH for position.

Resources

  • [XLOOKUP function](/functions/xlookup/)
  • [XLOOKUP tutorial](/blog/xlookup-excel-tutorial/)
  • [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/)

Quick answer: xlookup not working

  • When XLOOKUP does not find a value, verify match_mode (0 for exact), that lookup_array and return_array align row-for-row, and that binary search modes are only used on sorted data.
  • Use if_not_found for display after logic is verified.
  • Partial text may need match_mode 2 (wildcards).
  • Tutorial: [XLOOKUP guide](/blog/xlookup-excel-tutorial/).

Binary search on sorted data

  • match_mode -1 and 1 require the lookup array to be sorted in the corresponding order.
  • If the column is unsorted, use exact match (0) instead of binary search modes.

XLOOKUP vs VLOOKUP failure modes

  • Both fail when the underlying value is truly missing — if_not_found only changes the message.
  • XLOOKUP adds failure modes when lookup_array and return_array lengths differ.
  • Compare fixes on [XLOOKUP](/functions/xlookup/) and [VLOOKUP #N/A](/problems/vlookup-returning-na/).

People also ask

  • XLOOKUP #N/A meaning? — No match in lookup_array with current match_mode.
  • XLOOKUP returns wrong column? — return_array may not align with lookup_array rows.

Frequently asked questions

  • XLOOKUP #N/A but VLOOKUP worked? Often match_mode, different lookup column, or if_not_found masking an old IFERROR pattern.
  • XLOOKUP returns #SPILL? Different issue — destination blocked. FILTER and XLOOKUP can both spill; clear the range.
  • Horizontal XLOOKUP not working? Pass a row as lookup_array; return_array must align column-for-column.
  • Should I use if_not_found before fixing the match? Use if_not_found for display only after you confirm the value exists in the lookup column with TRIM and type checks.
  • Does match_mode affect XLOOKUP results? Yes — 0 is exact match for IDs. Wildcard mode (2) is for partial text; wrong mode causes misses or wrong rows.
  • Can XLOOKUP search a row instead of a column? Yes — horizontal lookup_array works the same way. Confirm return_array aligns row-for-row.

Extended diagnostic workflow

  • Problem: XLOOKUP returns no match when the lookup value and lookup array values are not truly the same.
  • Quick fix: Check for extra spaces, mismatched text/number types, wrong lookup_array, and match_mode settings before adding if_not_found text.
  • Reproduce on three sample rows before changing production formulas shared with the whole team.
  • Save version history or a copy before bulk find-replace across thousands of cells.
  • If the fix works on a sample but fails in production, compare text vs number storage on key columns.

Related functions to verify

  • XLOOKUP (/functions/xlookup/): =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) — Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • TRIM (/functions/trim/): =TRIM(text) — Removes extra spaces from text, leaving only single spaces between words.
  • CLEAN (/functions/clean/): =CLEAN(text) — Removes all non-printable characters from text.
  • VALUE (/functions/value/): =VALUE(text) — Converts a text string that represents a number to a number.
  • TEXT (/functions/text-function/): =TEXT(value, format_text) — Converts a value to text in a specified number format.

Escalation and documentation

  • Rebuild the formula on a blank sheet with minimal ranges to isolate the failing argument.
  • Document the root cause in a shared runbook so the next teammate does not repeat the same fix.
  • Link to fix-excel-formula-errors (/guides/fix-excel-formula-errors/) when multiple error types appear.
  • Escalate to Power Query or IT when the source system export format changed without notice.