How to Fix VLOOKUP #N/A Error When Looking Left in Excel
Question details
The user needs to retrieve a value located to the left of the search column but encounters an #N/A error because VLOOKUP only searches from left to right.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Extracting or matching data from a table where the desired return value is positioned in a column to the left of the lookup value column.
- Observed behavior
- The VLOOKUP formula returns an #N/A error instead of the expected matched value, as it strictly searches the leftmost column and only returns values to its right.
Before applying a fix, verify your spreadsheet software version to see if newer functions like XLOOKUP are supported, and ensure the lookup value actually exists in your target array.
Use the INDEX and MATCH Functions
The most versatile and universally compatible method to look left in any spreadsheet version is combining the INDEX and MATCH functions.
INDEX and MATCH work together seamlessly to find a value anywhere in your table, entirely bypassing VLOOKUP's strict left-to-right limitation.
Click on the cell where you want the extracted data to appear.
Type MATCH(lookup_value, lookup_array, 0) to find the exact row number of your target value. For example: MATCH(C6, $H$2:$H$83, 0).
Complete the formula by wrapping the MATCH result inside the INDEX function. The syntax is =INDEX(return_array, MATCH_formula). Example: =INDEX($F$2:$F$83, MATCH(C6, $H$2:$H$83, 0)).
Press Enter. The correct value from the left column will now be displayed without triggering an #N/A error.
Use the Modern XLOOKUP Function
If you are using a newer version of Spreadsheet software, XLOOKUP is the easiest and most direct way to perform a leftward search.
Rearrange the Table Columns
If you strongly prefer to stick with VLOOKUP, physically moving the columns so the lookup column is on the far left will eliminate the issue.
Easily Manage Data and Formulas with WPS Spreadsheet
WPS Office provides a highly compatible Spreadsheet application that fully supports advanced functions like XLOOKUP, INDEX, and MATCH, making complex data analysis simple and error-free.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data table.
- 2. Select the cell for your formula: Click on the specific cell where you want to output the search result.
- 3. Enter the XLOOKUP formula: Type =XLOOKUP() and simply select your lookup value, the search array, and the return array to its left.
- 4. Get instant results: Press Enter to instantly fetch your data, bypassing traditional VLOOKUP #N/A limitations.
Frequently Asked Questions
Why does VLOOKUP return an #N/A error when looking left?
By design, VLOOKUP (Vertical Lookup) strictly searches for a match in the leftmost column of a specified table array and only returns values from columns to the right. It simply does not have the built-in capability to search backward or look left, causing an #N/A error when attempted.
Can I force VLOOKUP to look left without rearranging columns?
While you cannot natively force VLOOKUP to look left, you can use a complex workaround by nesting the CHOOSE function inside VLOOKUP to virtually rearrange the columns in the formula. However, using INDEX/MATCH or XLOOKUP is highly recommended as a much simpler and cleaner alternative.
Is XLOOKUP better than using INDEX and MATCH?
XLOOKUP is generally considered more straightforward and robust than INDEX and MATCH because it requires writing only a single function, defaults to exact matches, and can easily search in any direction. However, INDEX and MATCH remain valuable for compatibility with older spreadsheet versions.
What should I do if the XLOOKUP formula returns a #NAME? error?
A #NAME? error indicates that your current spreadsheet software version does not support the XLOOKUP function. If you encounter this, switch to the universally supported INDEX and MATCH combination or upgrade your software.
