How to Use INDEX MATCH to Return Values from a Lookup Table in WPS Office Excel
Question details
The user wants to replace VLOOKUP with INDEX and MATCH to retrieve corresponding values from a lookup table in WPS Office Excel.
- Product
- WPS Office Excel / WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a lookup table and needs to return corresponding values from a column based on a lookup value.
- Observed behavior
- VLOOKUP is not flexible enough for the user's lookup, so the goal is to use INDEX and MATCH for exact column and row matching.
Open your WPS Office spreadsheet and identify the lookup column and the column from which you want to return values. In the example formula, the lookup value is in column B, the lookup column is column H, and the return column is column I.
Return values with INDEX MATCH and fill the formula across rows and columns
Use this method when you need a flexible lookup that returns values from any column and copies across a range without rewriting the formula.
The formula combines INDEX to select the return column and MATCH to find the row position of the lookup value. Using 0 in MATCH forces an exact match, and locking the lookup column with $ lets you fill the formula right without losing the correct lookup column.
Click cell C3 in your WPS Office Excel worksheet, where the first returned value should appear.
Type =INDEX(I:I,MATCH($B3,$H:$H,0)) into cell C3 and press Enter.
Use the fill handle to drag cell C3 to the right to column D so the return column moves from I to J.
Select C3:D3 and drag the fill handle down through the remaining rows to return each matching value.
Build INDEX MATCH with the Function Arguments dialog
Use this method if you prefer a guided way to create the formula without typing it manually.
Use INDEX MATCH Lookups in WPS Office Spreadsheets
WPS Office Spreadsheets supports INDEX and MATCH, so you can replace VLOOKUP with a more flexible lookup formula. Open your workbook, enter the formula once, and fill it across your data to return exact values without switching tools.
- 1. Open your workbook: Launch WPS Office and open the Excel-compatible workbook that contains your lookup table.
- 2. Select the first result cell: Click the cell where you want the first returned value, such as C3.
- 3. Enter the lookup formula: Type =INDEX(I:I,MATCH($B3,$H:$H,0)) and press Enter to return the matching value.
- 4. Apply the formula to the rest of the data: Drag the fill handle right to column D, then select the filled cells and drag down through the remaining rows.
Frequently Asked Questions
Can INDEX MATCH replace VLOOKUP in WPS Office Excel?
Yes. Use =INDEX(return_column,MATCH(lookup_value,lookup_column,0)). It returns the same value as VLOOKUP but keeps the formula stable if columns are inserted or rearranged.
Can INDEX MATCH return values from a column to the left of the lookup key?
Yes. Unlike VLOOKUP, INDEX and MATCH can look left because INDEX and MATCH are independent. Place the return column in INDEX and the lookup column in MATCH.
Why is the third argument of MATCH set to 0?
0 tells MATCH to find an exact match. Use it when your lookup value must match the lookup column exactly; use 1 or -1 only for sorted approximate matches.
How do I copy the formula to other cells without getting wrong results?
Use $ to lock the lookup column and the return column where needed. For example, $H:$H keeps column H fixed, while $B3 keeps column B fixed but lets the row number update.
What should I check if the formula returns #N/A?
Check that the lookup value exists in the lookup column, then look for extra spaces, different data types, or hidden characters in the lookup value or lookup column.
