Fix Excel VLOOKUP #N/A Errors from Wrong Ranges and Data Types
Question details
The user needs to fix a VLOOKUP formula that returns #N/A because the selected table range starts in the wrong column or the lookup values use inconsistent data types.
- Product
- Excel spreadsheet or WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Looking up a tradedCode or SKU from one worksheet in a table on another worksheet.
- Observed behavior
- VLOOKUP returns #N/A even though the expected lookup value appears to exist in the source table.
Identify the exact column that contains the lookup key on the source sheet and verify which column contains the result you want to return. Check a few matching values to see whether they are stored as text or as numbers.
Set the VLOOKUP range to begin with the lookup column
Use this solution when the lookup key is not in the first column of the VLOOKUP table array.
VLOOKUP searches only the first column of its selected range. Start the range at the tradedCode or SKU column, then adjust the column index so it counts from that first selected column.
In the formula bar, edit the VLOOKUP table_array so the lookup column is first. For example, if the key is in column B on Sheet2, use Sheet2!$B$2:$O$25 rather than a range that begins before column B.
Count the result column from column B within the selected range. For example, the ninth column in Sheet2!$B$2:$O$25 is returned with column index 9.
Enter or update the formula as =VLOOKUP(B2,Sheet2!$B$2:$O$25,9,FALSE). The FALSE argument requires an exact match, and the $ symbols keep the range fixed when you fill the formula down.
Convert lookup values to the same text or number data type
Use this solution when matching values look identical but VLOOKUP still returns #N/A.
Fix VLOOKUP Range and Data-Type Errors in WPS Spreadsheets
WPS Spreadsheets lets you edit VLOOKUP formulas, lock table ranges with absolute references, convert text numerals, and use XLOOKUP for more flexible lookups without a fixed column index.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the Excel workbook containing the lookup formula.
- 2. Edit the VLOOKUP table array: Select the formula cell, press F2, and change the table array so it begins with the lookup-key column, such as Sheet2!$B$2:$O$25.
- 3. Match the lookup data types: Select the lookup values and use Home > Number Format, or add VALUE(cell) or cell*1 to convert text-formatted numerals.
- 4. Use XLOOKUP when available: Replace the formula with an XLOOKUP pattern such as =XLOOKUP(B2*1,Sheet2!$B$2:$B$25,Sheet2!$J$2:$J$25) to specify separate lookup and return ranges.
Frequently Asked Questions
Why does VLOOKUP return #N/A when the value is visible in the table?
The value may be text in one location and a number in the other, or the VLOOKUP table array may not start with the lookup column. Check both the data type and the first column of the selected range.
How do I calculate the VLOOKUP column index correctly?
Count from the first column in the table_array, not from column A of the worksheet. In Sheet2!$B$2:$O$25, column B is 1, column C is 2, and column J is 9.
Should I use FALSE in a VLOOKUP formula for codes and SKUs?
Yes. Use FALSE for an exact match when looking up identifiers such as tradedCode, SKU, product IDs, or invoice numbers. For example: =VLOOKUP(B2,Sheet2!$B$2:$O$25,9,FALSE).
Can XLOOKUP avoid VLOOKUP range problems?
Yes. XLOOKUP uses separate lookup and return ranges, so the return column does not need to be to the right of the lookup column and no column index is required.
