Formula Errors

How to Fix Excel VLOOKUP #N/A or #VALUE! Errors Caused by Mismatched IDs

Phi Hung Vo Oct 8, 2026 869 views

Question details

The user is experiencing #N/A or #VALUE! errors when using VLOOKUP because the lookup values contain prefixes that are missing in the lookup table.

Fix Excel VLOOKUP #N/A or #VALUE! Errors Caused by Mismatched IDs

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to retrieve data from a table where the lookup ID format differs slightly from the source ID (e.g., source has 'Ab-F01' while the table only has 'F01').
Observed behavior: VLOOKUP returns an #N/A or #VALUE! error because it requires an exact match, and basic cleaning functions like TRIM only remove spaces, not differing text prefixes.

Before you start

Verify that your version of Excel supports the TEXTAFTER function (available in Microsoft 365 and Excel 2021 or later). If using an older version, you will need to use a combination of RIGHT and FIND functions instead.

Solution 1Recommended

Extract the Matching ID Using TEXTAFTER

Use the TEXTAFTER function to remove the prefix before the hyphen, ensuring an exact match with the lookup table.

To make VLOOKUP work, the lookup value must exactly match the value in your table array. By nesting TEXTAFTER inside your VLOOKUP formula, you can strip away the prefix on the fly without altering your original dataset.

Identify the target cells

Locate the cell containing the prefixed ID, such as N2 containing 'Ab-F01'.

Enter the nested VLOOKUP formula

In your result cell, type the formula =VLOOKUP(TRIM(TEXTAFTER(N2,"-")),Cereals!$A$2:$B$4,2,FALSE). This extracts the text after the hyphen and trims any hidden spaces.

Lock the table array

Ensure you are using absolute references (like $A$2:$B$4) for your lookup range so the target area does not shift when you copy the formula down.

Apply to remaining cells

Press Enter to calculate the result, then click and drag the fill handle at the bottom right of the cell to copy the formula to the rest of your column.

Pro Tip: Wrapping TEXTAFTER inside the TRIM function is highly recommended as it acts as a fail-safe against invisible trailing or leading spaces that frequently cause #N/A errors.

Solution 2

Use RIGHT and FIND for Older Excel Versions

If you are using a version of Excel that lacks the TEXTAFTER function, you can achieve the same result by combining RIGHT, LEN, and FIND.

Advanced Formula Support

Easily Handle Complex Lookup Formulas with WPS Spreadsheets

WPS Spreadsheets provides robust support for advanced lookup functions and text extraction formulas, ensuring exact data matching and seamless calculation for large datasets.

1. Open your workbook: Launch WPS Spreadsheets and open the file containing your mismatched lookup data.
2. Enter your formula: Select the target cell and type your nested lookup formula, such as =VLOOKUP(TRIM(TEXTAFTER(N2,"-")),$A$2:$B$4,2,0).
3. Calculate and drag: Press Enter to return the exact match, then drag the fill handle down to apply the formula across your dataset.

Fully compatible with Microsoft Excel formulas including VLOOKUP, XLOOKUP, and text manipulation functions.Built-in advanced data cleaning tools to effortlessly format and standardize mismatched IDs.Lightweight, fast performance ensuring smooth calculation even with complex nested formulas.

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP return #N/A even when I can visually see the matching value?

VLOOKUP requires a mathematically exact match. Invisible characters such as trailing spaces, different data formatting (text formatted as a number), or hidden prefixes will prevent Excel from recognizing the match, resulting in an #N/A error.

What does the FALSE argument do in a VLOOKUP formula?

The FALSE (or 0) argument at the end of a VLOOKUP formula forces the function to look for an exact match. If you omit this argument or use TRUE, Excel defaults to an approximate match, which can return incorrect data if your lookup table is not sorted alphabetically.

How do I lock my lookup table range to prevent #VALUE! errors?

Highlight the cell range in your formula (e.g., A2:B4) and press the F4 key. This adds dollar signs to your reference (e.g., $A$2:$B$4), turning it into an absolute reference. This ensures the lookup area stays exactly the same when you drag the formula to other rows.

Excel & Data Problems