How to Fix Excel VLOOKUP Returning #N/A When Value Exists
Question details
The user needs to resolve an issue where VLOOKUP formulas return an #N/A error despite the lookup value visibly existing in the source table.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Copying or dragging VLOOKUP formulas down a column or referencing data across multiple worksheets.
- Observed behavior
- The formula initially displays the correct result but turns into an #N/A error when copied to other cells, because the relative lookup range shifts out of bounds.
Ensure your workbook's calculation mode is set to Automatic in the Formulas tab. Also, briefly check your source data for any hidden trailing spaces or mismatched text-to-number formatting that could prevent an exact match.
Lock the Lookup Range with Absolute References
Use absolute references to prevent your lookup table array from shifting downwards when you copy or drag the formula to other cells.
By default, Excel uses relative cell references. When you drag a formula down a column, the range it searches also shifts down by the same number of rows. This causes the function to miss the data located at the top of your original array.
Click on the cell containing the VLOOKUP formula that is returning the #N/A error.
Click into the Formula Bar at the top and highlight the second argument in your VLOOKUP formula (the table_array, e.g., '360 item catalog'!A5:AM146).
Press the F4 key on your keyboard. This will add dollar signs to the column letters and row numbers (e.g., '360 item catalog'!$A$5:$AM$146), locking the range in place.
Press Enter to save the formula. Click the fill handle in the bottom-right corner of the cell and drag it down to apply the fixed formula to the rest of your column.
Ensure Exact Match is Specified
Verify that your VLOOKUP formula is set to find an exact match rather than an approximate match, which can cause false #N/A errors if the data is not sorted alphabetically.
Clean Data to Remove Extra Spaces or Type Mismatches
Format mismatches (like numbers stored as text) or invisible trailing spaces will cause an exact match lookup to fail.
Effortlessly Manage Data and Functions with WPS Spreadsheet
WPS Office Spreadsheet provides full native support for VLOOKUP, XLOOKUP, and other advanced formulas. Its intuitive formula builder and automatic error-checking make troubleshooting referencing issues incredibly straightforward.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your dataset containing the lookup tables.
- 2. Insert the VLOOKUP function: Select the target cell, click the 'Formulas' tab, choose 'Insert Function', and search for VLOOKUP.
- 3. Select and lock the range: Input your lookup value, select your table array, and press F4 to automatically apply absolute references ($) to lock the range.
- 4. Apply the exact match parameter: Enter the target column index number, type FALSE in the Range_lookup field, and click OK to get accurate results.
Frequently Asked Questions
Why does VLOOKUP work on the first row but return #N/A for the rest?
This happens when the table array is entered as a relative reference. When you copy the formula down, the search range shifts down correspondingly, skipping the top rows of your data. To fix this, use the F4 key to apply absolute references (e.g., $A$1:$D$100) before dragging the formula.
Can VLOOKUP pull data from a different worksheet?
Yes. You can reference another sheet by clicking on it while building your formula. The syntax will include the sheet name followed by an exclamation mark, such as 'Sheet2'!$A$1:$D$100. Ensure you still lock the range with dollar signs.
What if the lookup value is a number but stored as text in the source table?
VLOOKUP treats text and numbers differently. If the lookup value is a number but the source data stores it as text, the function will return #N/A. You can fix this by converting the text to numbers using the 'Convert to Number' error-checking option or multiplying the column by 1.
How do I hide the #N/A error if the lookup value doesn't exist yet?
You can wrap your VLOOKUP formula in the IFERROR function to display a blank cell or custom message instead of the error. For example: =IFERROR(VLOOKUP(A2, $B$2:$C$10, 2, FALSE), "").
