Formula Errors

Fix Excel MATCH and VLOOKUP Returning #N/A Error for One Table

John Wilson Oct 9, 2026 868 views

Question details

User's VLOOKUP and INDEX/MATCH formulas return an #N/A error for values in a specific table, despite working properly with manually entered data.

How to Fix Excel MATCH and VLOOKUP Returning #N/A Error

Product: Excel
Device & OS: not provided
Scenario: Looking up and matching data across multiple tables using Excel's VLOOKUP or INDEX/MATCH functions.
Observed behavior: Formulas unexpectedly return an #N/A error for one table because the underlying data formatting, such as hidden spaces or text-versus-number formatting, does not strictly match the lookup values.

Before you start

Verify that the lookup values in both tables visually appear the same and make sure your formula references the exact and correct cell ranges.

Solution 1Recommended

Clean and Standardize Table Data Formatting

Resolve #N/A errors by ensuring lookup values and table arrays have identical underlying data types, removing hidden spaces and formatting issues.

Even if data looks identical on the screen, Excel may treat it differently due to hidden trailing spaces or differing cell formats (such as numbers stored as text). Matching functions require exact equivalence to succeed.

Remove hidden spaces

Insert a new column next to your lookup values. Type the formula =TRIM(A2) (replacing A2 with your target cell) and drag it down to remove leading and trailing spaces.

Convert text to numbers

Select the column containing your data, click the 'Data' tab in the top ribbon, and choose 'Text to Columns'. Click 'Finish' immediately in the dialog box to convert any text-formatted numbers into actual numbers.

Update your formulas

Adjust your VLOOKUP, MATCH, or INDEX/MATCH formulas to reference the newly cleaned and standardized data ranges to successfully pull your results.

Clean and Standardize Table Data Formatting

Data Format Check: Values imported from other software systems often contain hidden apostrophes that force numbers to be stored as text. The Text to Columns trick is the fastest way to clear these anomalies.

Efficient Data Processing

Easily Manage Complex Formulas and Lookups with WPS Spreadsheet

WPS Office offers a fully compatible spreadsheet application that seamlessly handles VLOOKUP, MATCH, and complex data cleaning tasks with ease.

1. Open your spreadsheet: Launch WPS Office and open your workbook containing the data tables.
2. Standardize formatting: Select your lookup column, navigate to the Data tab, and use the 'Text to Columns' feature to clear format mismatches.
3. Apply your formulas: Type your VLOOKUP or MATCH formula exactly as you would in Microsoft Excel to seamlessly retrieve your data.

Fully compatible with Microsoft Excel formulas including VLOOKUP, INDEX, and MATCH.Built-in data cleaning tools like Text to Columns and TRIM to prevent #N/A errors.Lightweight software that processes large data tables quickly and smoothly.Free to use with a familiar interface, requiring no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VLOOKUP return #N/A even when there is an exact match visually?

This usually happens because the underlying data types differ. For example, the lookup value might be formatted as a true number, while the equivalent value in the table array is stored as text, or there are hidden leading and trailing spaces.

How can I easily spot leading or trailing spaces in Excel cells?

Click on the cell and press F2, or click inside the formula bar. Look closely at the cursor position. If it blinks after a blank space at the end of your text instead of right next to the last character, you have trailing spaces that need to be removed.

What should I do if VLOOKUP still returns #N/A after fixing the data formats?

Ensure your formula is set to find an exact match by setting the [range_lookup] argument to FALSE or 0 (e.g., =VLOOKUP(A2, B:D, 2, FALSE)). Also, verify that the lookup value actually exists in the very first column of your specified table array.

Does INDEX and MATCH fix formatting mismatch errors automatically?

No, INDEX and MATCH are subject to the same strict data type and formatting rules as VLOOKUP. You must still standardize the text or number formats in both ranges for the MATCH function to correctly identify the row.

Excel & Data Problems