Xlookup formula returns #N/A

First verify that the lookup values are truly identical. Hidden spaces, nonprinting characters, different data types (text versus numbers), or slight formatting differences can prevent a match. Use functions such as TRIM(), CLEAN(), VALUE(), or TEXT() to normalize the data if necessary.

Confirm that the lookup array and return array are the correct ranges and have the same dimensions. Also check that the lookup value is not an error or an empty string, and that the source workbook or worksheet is available if the data is external.

If you are using the default exact-match behavior, ensure the value exists exactly as entered. If you need wildcard matching, set the match_mode argument to 2 and use * or ? in the lookup value. If the formula uses an approximate match (match_mode 1 or -1), make sure the lookup array is sorted.

To troubleshoot, test the lookup value with =EXACT(), =ISTEXT(), =ISNUMBER(), or =LEN() to identify hidden differences. You can also use =MATCH() or Evaluate Formula (Formulas > Evaluate Formula) to determine why the lookup is failing.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin