Function Problems

How to Lookup Values to the Left using INDEX MATCH or XLOOKUP

Amos Gikunda Oct 7, 2026 869 views

Question details

The user needs to retrieve data from columns located to the left of a lookup column, which is a known limitation of the standard VLOOKUP function.

How to Lookup Values to the Left using INDEX MATCH or XLOOKUP

Product: Spreadsheets
Device & OS: not provided
Scenario: Retrieving data from columns C and D when the search criteria or lookup value is located to their right in column E.
Observed behavior: VLOOKUP fails because it is designed to only search for values in the first column of the given range and return values to the right.

Before you start

Check your spreadsheet software version. If you are using an older version that does not support XLOOKUP, the INDEX and MATCH combination is your most reliable alternative.

Solution 1Recommended

Use the INDEX and MATCH Combination

This is the most universally compatible method to perform a left-lookup in any spreadsheet software.

By combining the INDEX and MATCH functions, you decouple the lookup array from the return array. This means your return column can be positioned anywhere in relation to your lookup column.

Select the destination cell

Click on the cell where you want the retrieved value to appear.

Enter the formula

Type the formula: =IFERROR(INDEX(Sheet1!C:C, MATCH(G2, Sheet1!E:E, FALSE)), ""). In this formula, Sheet1!C:C represents the column with your desired results, G2 is the value you are searching for, and Sheet1!E:E is the column containing the lookup values.

Apply the formula

Press Enter to execute the formula. You can drag the fill handle down to apply this formula to other rows. The IFERROR wrapper ensures that if no match is found, a blank cell is displayed instead of a #N/A error.

Understanding the formula: The MATCH function finds the relative row number of your lookup value in Column E. The INDEX function then uses that row number to fetch the corresponding value from Column C.

Solution 2

Use the XLOOKUP Function

A modern, straightforward alternative that natively supports looking up values in any direction without combining multiple functions.

Master Spreadsheets with WPS Office

Easily Handle Complex Lookups in WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions like INDEX, MATCH, and XLOOKUP, allowing you to manipulate and analyze your data seamlessly without compatibility issues.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your lookup tables.
2. Access the function library: Navigate to the 'Formulas' tab on the top ribbon and click on 'Insert Function'.
3. Insert the lookup formula: Select the 'Lookup & Reference' category to easily find and insert INDEX, MATCH, or XLOOKUP via a user-friendly dialog box that guides you through each argument.

Fully compatible with Microsoft Excel formulas and functionsNatively supports XLOOKUP, INDEX, MATCH, and VLOOKUPBuilt-in formula suggestions and intuitive syntax highlightingFree and lightweight alternative for robust data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does VLOOKUP only work from left to right?

By design, VLOOKUP requires the lookup value to be located in the first (leftmost) column of the selected table array. It then counts columns to the right to find the return value and does not support negative column index numbers.

Is XLOOKUP available in all versions of spreadsheet software?

No, XLOOKUP is a modern function introduced in newer spreadsheet versions. If you are using an older version or sharing your file with someone who does, you should use the INDEX and MATCH combination to ensure full compatibility.

How can I look up multiple criteria at once?

You can perform multi-criteria lookups using INDEX/MATCH or XLOOKUP by concatenating the lookup values and lookup arrays with the ampersand (&) symbol. For example: =INDEX(C:C, MATCH(val1&val2, A:A&B:B, 0)). Note that older spreadsheet software might require you to enter this as an array formula using Ctrl+Shift+Enter.

Excel & Data Problems