How to Fix VLOOKUP Cannot Look Left Using XLOOKUP or INDEX MATCH
Question details
The user needs to retrieve a value from a column located to the left of the lookup column, which VLOOKUP does not support by default.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Attempting to look up an ID or value that sits in a column to the left of the search criteria column in a data table.
- Observed behavior
- VLOOKUP returns an error or fails because it is restricted to searching the first column of the range and only returning values to the right.
Check which version of your spreadsheet software you are using, as the XLOOKUP function is only available in Office 365 and newer software versions. Ensure you know the exact column letters for your return values and lookup criteria before writing the formula.
Use the XLOOKUP Function (Modern Approach)
The easiest way to look left in modern spreadsheet software is by using the XLOOKUP function, which has no directional restrictions.
XLOOKUP replaces both VLOOKUP and HLOOKUP. It allows you to specify a lookup array and a return array separately, meaning the return array can be positioned anywhere, including to the left of the lookup array.
Click on the cell where you want the lookup result to appear.
Type `=XLOOKUP(` to begin the function.
Specify your lookup value, lookup array (the column to search), and return array (the column to the left containing the desired result). For example, type `=XLOOKUP(C2, $B$2:$B$7, $A$2:$A$7)`.
Press Enter to execute the function and retrieve the value from the left column.
Combine INDEX and MATCH Functions (Legacy Support)
If your software version does not support XLOOKUP, use the classic INDEX and MATCH combination to look up values in any direction.
Rearrange the Data Columns for Standard VLOOKUP
If you must use VLOOKUP and are allowed to alter the dataset, simply move the lookup column to the leftmost position.
Perform Advanced Lookups Easily in WPS Office
WPS Spreadsheet fully supports advanced functions like XLOOKUP, INDEX, and MATCH. It is highly compatible with Microsoft Excel formulas, enabling you to process data and look up values in any direction effortlessly.
- 1. Open your file in WPS: Launch WPS Office and open your Excel workbook (.xlsx).
- 2. Select the target cell: Click on an empty cell where you need the lookup result to be displayed.
- 3. Enter the formula: Type `=XLOOKUP(` or `=INDEX(` and follow the built-in formula syntax prompts to select your ranges.
- 4. Get the result: Press Enter to instantly get your exact match from any column direction.
Frequently Asked Questions
Why can't VLOOKUP look to the left?
VLOOKUP (Vertical Lookup) is inherently designed to search only the first (leftmost) column of the specified table array and count column index numbers to the right. It does not support negative column index numbers to look left.
Is XLOOKUP available in older versions of Excel?
No, XLOOKUP was introduced in Excel 365 and Excel 2021. If you are using Excel 2019 or older, you must use the INDEX and MATCH combination or switch to a modern alternative like WPS Office that supports newer functions.
What does the '0' mean in the MATCH function?
The '0' at the end of the MATCH function specifies the match type as an 'Exact Match'. This ensures the formula only returns a result if it finds the exact lookup value in your search column, avoiding incorrect approximate matches.
Does INDEX MATCH slow down large spreadsheets?
INDEX MATCH is actually very efficient and often performs faster than VLOOKUP on large datasets because it only evaluates the specific columns referenced, rather than an entire table array.
