Excel Guides

Why Does XLOOKUP Return #N/A When the ID Exists?

You export an invoice list, run XLOOKUP against your vendor master, and get #N/A for vendor 4812. You switch tabs. There it is, in row 2. Excel can show two IDs the same way while storing one as a number and the other as text. A space you can’t see can break the match too.

Start with one failed lookup and the row you expected it to find. It’s usually faster than rewriting the formula for the whole sheet.

1. Check the lookup range and the two cells

Suppose your invoice export is on Invoices and your vendor master is on Vendors. This formula searches the vendor ID column and returns the name from the same row.

=XLOOKUP(A2,Vendors!$A$2:$A$4,Vendors!$B$2:$B$4)

Make sure the expected ID is inside the lookup range. If the master grew beyond row 4, extend both ranges or use an Excel Table so new rows are included. XLOOKUP uses exact matching by default. Leave that setting in place for IDs.

If the row is in range, compare the actual values. In this example, ISNUMBER(A2) returns TRUE on Invoices and ISTEXT(Vendors!A2) returns TRUE. Both cells show 4812, but the stored types differ. Changing the display format alone won’t convert the existing values.

2. Check for a hidden character

On the next invoice, V-204 also returns #N/A. Compare LEN(A3) with LEN(Vendors!A3). If the invoice returns 6 and the master returns 5, inspect the last character.

=UNICODE(RIGHT(A3,1))

A result of 32 is an ordinary space. A result of 160 is a nonbreaking space, which can come from copying a report out of a web page. TRIM removes ordinary leading and trailing spaces, but it needs help with character 160.

3. Clean both columns and keep the originals

Add a helper column on each sheet. Put this formula in Invoices!B2 and Vendors!C2, then fill down. It treats IDs as text, turns nonbreaking spaces into ordinary spaces, and trims them. Use this rule only if extra spaces have no meaning in your IDs. Excel’s TRIM also reduces repeated spaces inside a value.

=TRIM(SUBSTITUTE(A2&"",UNICHAR(160)," "))

Point XLOOKUP at the two helper columns. A blank invoice ID should stay blank, and a missing vendor should be easy to spot.

=IF(B2="","",XLOOKUP(B2,Vendors!$C$2:$C$4,Vendors!$B$2:$B$4,"No match"))

Check that each cleaned vendor ID identifies one row before you rely on the results. If two records turn into the same ID after cleaning, review them first. By default, XLOOKUP returns the first match it finds.

Try it on three rows

On Vendors, enter these ID and name pairs in columns A and B, starting in row 2. The leading apostrophe tells Excel to store the value as text and stays out of the stored ID.

A2: '4812     B2: Northline Electrical
A3: V-204     B3: Cedar Office Supply
A4: '00731    B4: Bayview Tools

On Invoices, enter the following in column A. Copy the original lookup down all three rows. Then add the helper columns and try the revised lookup.

A2: 4812
A3: ="V-204"&UNICHAR(160)
A4: '00731

The original lookup misses the first two invoices and finds Bayview Tools. The revised lookup finds all three. If an export already changed 00731 to 731, the helper formula will keep 731. Reimport the original ID column as text or restore the zeros using a verified fixed-width rule.

Check a whole export with Jolene.pro

If dozens of invoices show #N/A, compare the invoice export and vendor master together. Keep the original ID columns so you can review the differences before changing any records.

Which vendor IDs fail to match? Show the source cells and separate type mismatches, hidden spaces, and IDs missing from the vendor master.

Try the check at Jolene.pro and follow Jolene.pro on LinkedIn for more spreadsheet guides.

Microsoft references for XLOOKUP and TRIM.

The vendor records are synthetic. Confirm your source system’s ID rules before normalizing production data.

Leave a comment