How to Fix Excel XLOOKUP Not Finding a Match

In this tutorial, you'll learn about How to Fix Excel XLOOKUP Not Finding a Match. We cover key concepts, practical examples, and best practices.

XLOOKUP searches a range for a value and returns a corresponding result. When it returns #N/A even though the value appears to exist, the data types do not match, there are hidden spaces, or the match mode is wrong.

The Problem

=XLOOKUP(D2, A:A, B:B)

Returns #N/A even though the value in D2 exists in column A.

Wrong approach — re-typing the lookup value manually instead of diagnosing the mismatch.

The Fix

Check for leading/trailing spaces with TRIM:

= XLOOKUP(TRIM(D2), TRIM(A:A), B:B)

Or create a helper column to clean data:

A2: =TRIM(A2) → drag down

Check for data type mismatch (text vs number):

= XLOOKUP(D2&"", A:A&"", B:B)    ← convert all to text
= XLOOKUP(VALUE(D2), A:A, B:B)    ← convert lookup value to number

Use the if_not_found parameter to return a custom message instead of #N/A:

= XLOOKUP(D2, A:A, B:B, "Value not found in lookup range")

Set the match mode for approximate matching:

= XLOOKUP(D2, A:A, B:B, , -1)    ← exact match or next smaller
= XLOOKUP(D2, A:A, B:B, , 1)     ← exact match or next larger
= XLOOKUP(D2, A:A, B:B, , 2)     ← wildcard match (*, ?)

Expected output:

XLOOKUP returns the correct matching value
Custom "not found" message when value truly does not exist
Wildcard match finds partial matches

Prevention Tips

• Clean source data with TRIM() and CLEAN() before performing lookups
• Ensure data types match: both lookup value and lookup array should be the same type
• Use the if_not_found parameter to handle missing values gracefully
• Use wildcard match mode (2) for partial text matching
• Wrap XLOOKUP in IFERROR only when you expect occasional failures

Common Mistakes with xlookup not found

1. Forgetting deriving (Show, Eq) on custom data types needed for debugging
2. Placing the wildcard pattern first in case expressions, making all subsequent patterns unreachable
3. Using head and tail instead of pattern matching, causing runtime errors on empty lists

These mistakes appear frequently in real-world EXCEL code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ

Related: DodaTech's Lookup Performance Analyzer tests XLOOKUP, VLOOKUP, and INDEX-MATCH speed across large datasets and recommends the fastest approach. Pair with DodaZIP for backup before data restructuring.