How to Fix VLOOKUP #N/A Error When Value is in Another Column
Question details
The user encounters an #N/A error when using VLOOKUP to search for a value that exists in a column other than the leftmost column of the table array.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Attempting to retrieve data from a column to the left of the lookup column (e.g., searching in column B to return a corresponding value from column A).
- Observed behavior
- The VLOOKUP formula returns an #N/A error because it strictly searches the leftmost column of the specified range and cannot look backward to the left.
Verify your data ranges to confirm exactly which column contains your lookup value and which column contains the result you wish to retrieve.
Use INDEX and MATCH for Leftward Lookups
Since VLOOKUP cannot look to its left, combining the INDEX and MATCH functions is the most reliable way to retrieve values regardless of column order.
VLOOKUP is designed to search only the first (leftmost) column of a selected table array. If your search criteria is in Column B and your desired result is in Column A, a standard VLOOKUP will fail and return an #N/A error.
The INDEX and MATCH combination overcomes this limitation by separating the return array from the lookup array, giving you the flexibility to look up values in any direction.
Click on the cell where you want the retrieved value to appear.
Begin by typing `=INDEX(A:A,` where 'A:A' represents the column containing the result you want to return.
Next, type `MATCH(G1,B:B,0))` where 'G1' is your specific lookup value, 'B:B' is the column containing the lookup value, and '0' specifies an exact match.
Press Enter to execute the formula. The final formula should look like `=INDEX(A:A,MATCH(G1,B:B,0))`. The correct value will now display instead of the #N/A error.
Master Formulas and Functions in WPS Spreadsheet
WPS Spreadsheet provides robust support for all standard formulas including VLOOKUP, INDEX, MATCH, and XLOOKUP. Enjoy seamless data processing and intelligent formula suggestions that help prevent #N/A errors.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the lookup tables.
- 2. Access the Function Wizard: Click the 'fx' button next to the formula bar to open the Insert Function dialog.
- 3. Find INDEX and MATCH: Search for INDEX or MATCH, and use the interactive prompt to select your lookup and return arrays without typing them manually.
Frequently Asked Questions
Why does VLOOKUP always return #N/A even when the lookup value visibly exists?
This usually happens for three main reasons: the lookup value is not in the very first column of your table array, there are hidden spaces in the text, or there is a data type mismatch (e.g., numbers stored as text in one column but as numbers in the other).
Can I make VLOOKUP look to the left without using INDEX and MATCH?
Standard VLOOKUP cannot look to the left naturally. However, you can use the CHOOSE function nested inside VLOOKUP to virtually rearrange the columns in memory, though INDEX and MATCH or XLOOKUP are much simpler approaches.
Is XLOOKUP better than the INDEX and MATCH combination?
XLOOKUP is generally easier to write and read than INDEX and MATCH because it requires only one function to handle lookups in any direction. However, INDEX and MATCH remain widely used for backward compatibility with older spreadsheet software versions.
