How to use XLOOKUP or INDEX MATCH for a left-side lookup in WPS Office Spreadsheets
Question details
The user needs a formula that looks up a value in column G and returns the corresponding result from column F, which is to the left of the lookup column.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Performing a left-side lookup in a spreadsheet when VLOOKUP is currently using the lookup column as the first column of the range.
- Observed behavior
- VLOOKUP cannot normally return a value to the left of its lookup column, so it fails or returns incorrect results when the lookup value is in column G and the required result is in column F.
Open your workbook and confirm that the lookup value is in column G and the value to return is in column F. Check whether your WPS Office version supports XLOOKUP, because INDEX and MATCH work in all current WPS Spreadsheets versions.
Use INDEX and MATCH to return a value from the left of the lookup column
This works in every WPS Office Spreadsheets version and is the safest fix when VLOOKUP is looking in the wrong column.
INDEX and MATCH separate the lookup column and the return column, so you can search column G and return the corresponding value from column F without reordering the sheet.
Click the cell where you want the left-side lookup result to appear, usually in the same row as the lookup value in column G.
Type =IFERROR(INDEX(Sheet2!F:F,MATCH(B2,Sheet2!G:G,0)),"not found") in the formula bar and adjust Sheet2, F:F, G:G and B2 to your actual sheet and cell references.
Press Enter to confirm, then drag the fill handle down or double-click it to apply the formula to the remaining rows.
Use XLOOKUP for a shorter left-side lookup formula
Use XLOOKUP if your WPS Office version supports it and you want a simpler formula without separating lookup and return ranges.
Fix left-side Excel lookups in WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel-compatible lookup functions, so you can search column G and return values from column F with INDEX MATCH or XLOOKUP without rearranging your workbook.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open the Excel-compatible workbook, and go to the sheet containing the lookup data.
- 2. Enter the INDEX MATCH formula: Click the result cell and type =IFERROR(INDEX(Sheet2!F:F,MATCH(B2,Sheet2!G:G,0)),"not found").
- 3. Press Enter and fill down: Press Enter to calculate the result, then drag the fill handle down to apply the formula to other rows.
- 4. Use XLOOKUP if supported: If your version supports XLOOKUP, enter =XLOOKUP(B2,Sheet2!G:G,Sheet2!F:F,"not found") in the result cell instead.
- 5. Save in Excel-compatible format: Save the workbook as .xlsx or .xls so it remains fully compatible with Microsoft Excel users.
Frequently Asked Questions
Why does VLOOKUP fail when my lookup column is to the right of the result column?
VLOOKUP searches the first column of the selected range and can only return values to the right. When the lookup value is in column G and the result is in column F, use INDEX MATCH or XLOOKUP instead.
Can I use XLOOKUP in WPS Office Spreadsheets?
XLOOKUP is available in newer WPS Office versions that support Microsoft 365-compatible functions. If the function does not appear when you type =XLOOKUP, use INDEX and MATCH because it works in all current WPS Spreadsheets versions.
How do I return a value from the left of the lookup column using INDEX and MATCH?
In the result cell enter =IFERROR(INDEX(Sheet2!F:F,MATCH(B2,Sheet2!G:G,0)),"not found"). MATCH finds the row in column G, and INDEX returns the corresponding value from column F.
Can I fix the issue by swapping columns F and G instead of changing the formula?
Yes, if you control the layout, you can move the result column to the right of the lookup column so VLOOKUP works. However, using INDEX MATCH or XLOOKUP is usually safer and avoids changing your data structure.
Does INDEX MATCH work with large datasets?
Yes, INDEX MATCH is efficient for exact-match lookups on large tables. For better performance, use specific ranges such as F2:F1000 and G2:G1000 instead of entire columns.
