Office 365 Excel, resolve #N/A error on an XLOOKUP formula

Question

Office 365 Excel, resolve #N/A error on an XLOOKUP formula

Tom Hasler 20

The zip code is [Moderator note: Personal information removed] on the sales order form. I'm using the following formula to shorten the zip code to 5 digits and lookup the corresponding zipcode for the sales tax rate:

=XLOOKUP(LEFT(Q19,5),TAXRATES_ZIP5_KY202605!B:D,3,FALSE). Q19 is formatted as Text

The sales tax table is as follows:

State ZipCode TaxRegionName EstimatedCombinedRate

KY [Moderator note: Personal information removed] DAVIESS 0.06

The format of the zipcode is Text. I continue to receive a #N/A error. There are no special hidden characters or spaces. I suspect the issue is with using the LEFT(Q19,5) formula since there is a perfect match between the five digit number on the sales order and the five digit number on the sales tax table.

Tom Hasler 20 Reputation points

2026-05-22T13:36:41.5966667+00:00

Your AI's only suggestion was to add a "Not Found" argument to the XLOOKUP. There is a definite match and the "Not Found" answer is incorrect.
Barry Schwarz 6,106 Reputation points

2026-05-22T18:32:28.2+00:00

Since a moderator erroneously removed public data published by the state, we cannot really tell what is happening. After you correct the XLOOKUP formula per Tamara-Hu's suggestion, if you still have a problem please post screenshots showing the actual formula and the relevant referenced cells.
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-05-22T22:57:51.3+00:00

Hi,

Share data in a format that can be pasted in an MS Excel file.

Answer accepted by question author

0 additional answers

Your answer

Tom Hasler 20 Reputation points

2026-05-22T13:36:41.5966667+00:00

Your AI's only suggestion was to add a "Not Found" argument to the XLOOKUP. There is a definite match and the "Not Found" answer is incorrect.
Barry Schwarz 6,106 Reputation points

2026-05-22T18:32:28.2+00:00

Since a moderator erroneously removed public data published by the state, we cannot really tell what is happening. After you correct the XLOOKUP formula per Tamara-Hu's suggestion, if you still have a problem please post screenshots showing the actual formula and the relevant referenced cells.
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-05-22T22:57:51.3+00:00

Hi,

Share data in a format that can be pasted in an MS Excel file.

Answer 1

Hello @Tom Hasler,

Thanks for sharing the details, this is a common scenario with ZIP code lookups, and you’re very close. The #N/A you’re seeing is most likely caused by how the XLOOKUP formula is structured and how Excel is interpreting the data types.

Here’s how to resolve it step by step:

Step 1: Correct the XLOOKUP structure

Your current formula is:

=XLOOKUP(LEFT(Q19,5),TAXRATES_ZIP5_KY202605!B:D,3,FALSE)

This syntax is valid for VLOOKUP, but not for XLOOKUP. XLOOKUP requires:

One column for the lookup array
One column for the return array (not a column index)

Update the formula to:

=XLOOKUP(LEFT(Q19,5), TAXRATES_ZIP5_KY202605!B:B, TAXRATES_ZIP5_KY202605!D:D)

This tells Excel to:

Look for the first 5 digits of the ZIP code in column B
Return the matching tax rate from column D

Step 2: Ensure both sides use the same data type

Even if the ZIP codes look identical, Excel may treat one as text and the other as a number or other format. In that case, XLOOKUP will not match them and will return #N/A.

To avoid this, you can force both values to the same type. If your ZIP codes are stored as text (which is common), update the formula to:

=XLOOKUP(TEXT(LEFT(Q19,5),"00000"), TEXT(TAXRATES_ZIP5_KY202605!B:B,"00000"),TAXRATES_ZIP5_KY202605!D:D)

This ensures both the lookup value and the lookup column are treated consistently as 5-digit text. Once both are corrected, the formula should return the expected result.

If you’re still seeing the issue after these steps, feel free to share a sample row from your sheet and I can help pinpoint it further.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.