How to Fix VLOOKUP #N/A Errors When Lookup Column Is Not First
Question details
The user needs to retrieve data from a table where the matching criteria column is not the first column, which causes an #N/A error when using VLOOKUP.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Retrieving specific attributes from a dataset where the search criteria column is located to the right of the return value column.
- Observed behavior
- Using the VLOOKUP function results in an #N/A error because it is structurally limited to searching only the leftmost column of a specified range.
Identify the exact column containing your search criteria and the column containing the data you want to retrieve. Ensure your data range does not contain hidden columns that might shift your expected column references.
Use INDEX and MATCH Functions to Look Left
Since VLOOKUP cannot look to the left, combining INDEX and MATCH is the most reliable way to retrieve data regardless of column position.
The VLOOKUP function is strictly designed to search the first column of a given range. When your lookup value is in the middle or end of the table, VLOOKUP will fail and return an #N/A error.
By nesting the MATCH function inside the INDEX function, you can perform a two-way lookup or a 'left lookup' without restructuring your dataset.
Click on the cell where you want the retrieved attribute to appear.
Type =INDEX(return_range, ...) where 'return_range' is the column containing the data you want to extract.
Inside the INDEX function, add MATCH(lookup_value, lookup_range, 0). The 'lookup_value' is what you are searching for, and 'lookup_range' is the column containing the search terms.
Wrap the entire formula in =IFNA(your_formula, "") to return a blank cell instead of an #N/A error if the value is not found. Example: =IFNA(INDEX(ReturnCol, MATCH(SearchValue, SearchCol, 0)), "").
Use XLOOKUP for a Simpler Formula
Modern spreadsheet software includes XLOOKUP, a powerful function that natively supports left lookups and replaces both VLOOKUP and INDEX/MATCH.
Fix Complex Formula Errors Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced lookup functions like INDEX, MATCH, and the modern XLOOKUP. Handle large datasets without worrying about VLOOKUP's left-lookup limitations.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the lookup tables.
- 2. Navigate to the Formulas tab: Click on 'Formulas' in the top ribbon to access the function library.
- 3. Insert your lookup formula: Select 'Lookup & Reference' and choose INDEX, MATCH, or XLOOKUP based on your preference.
- 4. Evaluate your formula: Use the 'Evaluate Formula' feature under the Formulas tab to step through your calculation and ensure errors like #N/A are resolved.
Frequently Asked Questions
Why does VLOOKUP always return #N/A when the lookup value exists?
VLOOKUP searches only the first (leftmost) column of the specified table_array. If your lookup value is actually located in the second or third column, VLOOKUP will not find it in the first column and will return an #N/A error.
Can I force VLOOKUP to look left without changing my data?
Yes, you can force VLOOKUP to look left by using the CHOOSE function to virtually rearrange the columns in memory (e.g., =VLOOKUP(Value, CHOOSE({1,2}, LookupCol, ReturnCol), 2, FALSE)). However, using INDEX/MATCH or XLOOKUP is generally easier and more efficient.
What is the difference between INDEX/MATCH and XLOOKUP?
XLOOKUP is a newer, simpler function that requires only one formula to look in any direction and includes built-in error handling. INDEX and MATCH is an older, widely supported combination of two functions that achieves the same result but is slightly more complex to write.
How do I hide the #N/A error if the data is genuinely missing?
Wrap your formula in the IFERROR or IFNA function. For example, =IFNA(formula, "Not Found") will display "Not Found" or a blank space instead of the #N/A error code, keeping your spreadsheet clean.
