XLOOKUP not working: how to fix #N/A and wrong results

1. The ID is a number on one side and text on the other

The staff directory has employee IDs in column A. You type 10433 in H1 and =XLOOKUP(H1,A2:A9,B2:B9) returns #N/A, even though 10433 is clearly in A4. The reason: A4 holds the text "10433", not the number. That happens when IDs are imported from a CSV or a system export, or typed into a cell formatted as Text. XLOOKUP never treats the number 10433 and the text "10433" as equal.

Check it with =ISTEXT(A4), which returns TRUE, or look for the small green triangle in the cell's corner and left-aligned numbers. The lasting fix is to convert the column: select it, click the warning icon and choose Convert to Number, or use Data > Text to Columns > Finish. When you cannot change the source, convert inside the formula. The double minus turns every text number in the lookup range into a real number.

2. A hidden space in the lookup column

H2 says Priya Shah and so does B5, but XLOOKUP returns #N/A. B5 is "Priya Shah " with a trailing space, so the two texts are not equal. =LEN(B5) gives 11 for a 10-character name, which gives the problem away. XLOOKUP ignores upper and lower case, but not spaces.

Wrap the lookup range in TRIM so every name is compared without leading or trailing spaces. If the data came from a web page, it may contain non-breaking spaces that TRIM leaves alone; use TRIM(SUBSTITUTE(B2:B9,CHAR(160)," ")) for those. Cleaning the column once with a TRIM helper column and Paste Values is better than patching every formula.

3. Wildcards only work with match_mode 2

With VLOOKUP and MATCH, an exact-match search treats * and ? as wildcards automatically. XLOOKUP does not. =XLOOKUP("Lak*",B2:B9,D2:D9) searches for the literal text Lak*, finds nothing and returns #N/A.

Set the fifth argument, match_mode, to 2 to switch wildcards on. Leave the fourth argument (if_not_found) empty with two commas, or put your own message there. In this mode, to search for a real asterisk or question mark, put a tilde in front of it: ~* or ~?.

4. Tier tables: -1 belongs in the fifth argument, not the fourth

J1:K5 is a bonus table: from 0 sales the rate is 0, from 5000 it is 1%, from 10000 it is 1.5% and from 20000 it is 2%. Fill in your own thresholds and rates. You want the rate for 12500 in H3. VLOOKUP with TRUE would find the 10000 row, but XLOOKUP defaults to an exact match, so =XLOOKUP(H3,J2:J5,K2:K5) returns #N/A.

The fix is match_mode -1, meaning "exact match, or else the next smaller value". The common slip is typing =XLOOKUP(H3,J2:J5,K2:K5,-1). That puts -1 in the fourth argument, if_not_found, so the formula returns -1 as its answer, which looks like a strange rate rather than an error. Add the extra comma. Unlike VLOOKUP with TRUE, match_mode -1 and 1 do not need the table sorted.

5. A wrong answer with no error: misaligned ranges

XLOOKUP finds the position of the match in the lookup range and returns the item at the same position in the return range. If the two ranges start on different rows, it returns the wrong person and gives no warning. Here 10425 is the second ID in A2:A9, so XLOOKUP returns the second cell of B1:B8, which is Maria Lopez in B2, not Sam Patel in B3.

If the ranges have different numbers of rows, for example A2:A9 and B2:B10, you get #VALUE! instead. Both mistakes come from editing one range and not the other. Press F2 on the formula and check that the coloured boxes start and end on the same rows. Whole columns (A:A and B:B) or an Excel table keep them aligned. When you fill the formula down, lock the ranges with $ (A$2:A$9), or they slide down a row at a time and lower rows start missing matches.

6. #NAME? in Excel 2019 and older

XLOOKUP exists in Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Excel 2019, 2016 and older do not have it. A file built in 365 still opens there, but the formula shows with _xlfn. in front of XLOOKUP and returns #NAME? once it recalculates. A typo in the function name gives #NAME? in every version.

If the file must work for people on older versions, use INDEX and MATCH. MATCH with 0 as its last argument is an exact match, and INDEX returns the item at that position, so it has the same rules about text, spaces and aligned ranges as XLOOKUP.

7. It found the first match, not the one you wanted

XLOOKUP stops at the first row that matches. Look up the department Sales and it returns Jo Kim, the first Sales row, even if you meant the most recent entry. The sixth argument, search_mode, sets the direction: -1 searches from the bottom up and returns the last match. For every match, use FILTER instead.

Leave search_mode at 1 or -1 unless you have a reason. The values 2 and -2 run a binary search, which is only correct when the lookup column is sorted; on unsorted data it can return a wrong row without any error.

One more surprise: if the matching row's return cell is empty, XLOOKUP shows 0, not a blank. Ana Costa has no extension, so =XLOOKUP(10489,A2:A9,D2:D9) returns 0. Add &"" to the end of the formula to show a blank instead, which turns the result into text.

A 30-second checklist

Work through these in order. Most broken lookups are fixed by the first two.

1. Check both sides with =ISTEXT(): if one is TRUE and the other FALSE, you have a text-number mismatch.
2. Compare =LEN() of the lookup value and the cell you expect it to match. A different length means hidden spaces.
3. Using * or ? or looking up a tier? Set match_mode, the fifth argument, to 2 or -1.
4. Press F2 and confirm the lookup and return ranges start and end on the same rows.
5. #NAME? means the version has no XLOOKUP or the name is misspelled.
6. Only after the lookup works, add a message in if_not_found for values that are genuinely missing.