Share via

Office 365 Excel, attempting to remove the +4 digits from a zip code and perform a vlookup to a sales tax table for the tax rate

Tom Hasler 20 Reputation points
2026-05-22T13:15:57.6766667+00:00

The zip code is 42355-9610 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:

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

The sales tax table is as follows:

State ZipCode TaxRegionName EstimatedCombinedRate

KY 42355 DAVIESS 0.06

The format of the zipcode is General. 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.

Microsoft 365 and Office | Excel | For business | Other
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Marcin Policht 90,805 Reputation points MVP Volunteer Moderator
    2026-05-22T13:30:44.41+00:00

    This might be a data type mismatch. LEFT(Q19,5) returns text. If the ZipCode column in TAXRATES_ZIP5_KY202605!B:B contains numeric values (42355 stored as a number), then Excel will not find a match between text "42355" and number 42355 when using an exact-match VLOOKUP.

    Test this by checking:

    =ISNUMBER(LEFT(Q19,5))

    This should return FALSE because LEFT returns text. If the tax table ZIP codes are numeric, convert the LEFT result to a number:

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

    or

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

    If the tax table ZIP codes are stored as text, keep the LEFT result as text and verify the lookup column is also text:

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

    You can determine the data type in the tax table with:

    =ISNUMBER(B2)

    where B2 contains 42355.

    Another possibility is that the ZIP+4 value in Q19 is actually stored as a number. If Q19 contains the numeric value 423559610, then:

    =LEFT(Q19,5)

    returns "42355", but you may be better off extracting the ZIP numerically

    =VLOOKUP(INT(Q19/10000),TAXRATES_ZIP5_KY202605!B:D,3,FALSE)

    Also verify that the ZIP code is truly in column B of the lookup range. In your description the table columns appear to be:

    A: State
B: ZipCode
C: TaxRegionName
D: EstimatedCombinedRate

    If so, your formula should return column 3 of the range B:D, which is the tax rate in column D. That part is correct. A quick diagnostic is

    =MATCH(VALUE(LEFT(Q19,5)),TAXRATES_ZIP5_KY202605!B:B,0)

    If this returns a row number, the ZIP exists and the issue is elsewhere. If it returns #N/A, the values are not identical from Excel's perspective, usually due to text-vs-number storage.

    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

    Was this answer helpful?

    0 comments

  2. AI answer

    2026-05-22T13:16:12.0866667+00:00

    The #N/A error indicates that VLOOKUP is not finding an exact match for the lookup value in the first column of the lookup range.

    In this case, the most likely causes are:

    1. Data type mismatch between the 5‑digit ZIP from LEFT(Q19,5) and the ZIP codes in TAXRATES_ZIP5_KY202605!B:B.
      • LEFT(Q19,5) always returns text.
      • If the ZIP codes in column B are stored as numbers, VLOOKUP with FALSE will not match them.
    2. The lookup column is not the first column of the table array.
      • VLOOKUP requires the lookup value to be in the first column of the table_array.
      • In TAXRATES_ZIP5_KY202605!B:D, column B must contain the ZIP codes being matched.

    To fix the #N/A:

    1. Ensure matching data types
      • Either convert the ZIP codes in the tax table to text, or convert the lookup value to a number.
      • Option A (make both text):
        • Format the ZIP column in the tax table as Text.
        • Re‑enter the values or use a helper conversion (e.g., =TEXT(B2,"00000") then paste values back).
      • Option B (make both numbers):
        • Use VALUE(LEFT(Q19,5)) in the VLOOKUP so the lookup value is numeric: 
                 =VLOOKUP(VALUE(LEFT(Q19,5)),TAXRATES_ZIP5_KY202605!B:D,3,FALSE)
    2. Confirm the lookup column and range
      • Verify that TAXRATES_ZIP5_KY202605!B:B contains the 5‑digit ZIP codes (e.g., 42355) with no extra characters.
      • Confirm that the tax rate is indeed in the third column of B:D (i.e., column D of that sheet).

    If the data types match and the ZIP is in the first column of the table array, VLOOKUP with FALSE will return the tax rate instead of #N/A.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.