Match Two Columns in WPS Spreadsheets and Return a Value from Another Sheet
Question details
The user needs a formula that matches both Location and ID across two columns on another sheet and returns the statement type from a third column, because the same ID can appear in multiple locations.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Performing a two-criteria lookup from a source sheet into a reporting sheet.
- Observed behavior
- The user needs the returned value to correspond to the row where both Location and ID match, not to the first occurrence of the ID alone.
Open the workbook in WPS Spreadsheets and confirm the sheet names and ranges for the two lookup columns and the return column. If either lookup column contains blank cells or inconsistent spacing, the combined key may not match.
Use INDEX and MATCH with a combined Location and ID key
Best for keeping the source sheet unchanged; it matches both criteria in a single formula.
This formula creates two temporary arrays by joining the Location and ID columns with an ampersand, then MATCH finds the exact row where the joined value equals A2&B2.
In WPS Spreadsheets, open the workbook and click Sheet1 cell D2, where the returned statement type should appear.
Type =INDEX(Sheet2!$C$2:$C$100,MATCH(A2&B2,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,0)).
Press Enter in current WPS versions. If the formula returns #VALUE! or only evaluates the first combined key, press Ctrl+Shift+Enter to confirm it as an array formula.
Replace $2:$100 with the actual data range, then use the fill handle in cell D2 to copy the formula down the column.
Use a helper column to create a single lookup key
Use this when the source sheet can be modified and you prefer a simpler VLOOKUP formula.
Perform a two-column lookup and return values with WPS Spreadsheets
WPS Spreadsheets supports INDEX, MATCH, and VLOOKUP formulas, so you can match Location and ID together and return the corresponding statement type from another sheet without switching to Microsoft Excel.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office and open the workbook that contains both Sheet1 and Sheet2.
- 2. Select the result cell and enter the lookup formula: Click Sheet1 cell D2 and enter =INDEX(Sheet2!$C$2:$C$100,MATCH(A2&B2,Sheet2!$A$2:$A$100&Sheet2!$B$2:$B$100,0)).
- 3. Confirm the formula: Press Enter or, if needed in an older version, press Ctrl+Shift+Enter to confirm the array formula.
- 4. Copy the formula to the remaining rows: Drag the fill handle from D2 down through all rows where the Location and ID values need to be matched.
Frequently Asked Questions
Can I use VLOOKUP instead of INDEX and MATCH for two-column matching?
A standard VLOOKUP only checks one value. You can combine Location and ID into a helper column and then use VLOOKUP, or use INDEX and MATCH with concatenated lookup arrays for a no-helper solution.
Why does the formula return #N/A even when the combination looks correct?
Check for leading or trailing spaces, different text case, numbers stored as text, or extra characters in either key column. Use TRIM or ensure both parts are the same data type.
Do I always need to press Ctrl+Shift+Enter for the combined INDEX MATCH formula?
In current WPS Spreadsheets versions the formula can often be entered normally, but if it does not return the expected row, confirm it with Ctrl+Shift+Enter or use the formula as an array formula.
Can I match more than two columns with this approach?
Yes. Add more arrays with ampersands, for example A2&B2&C2 matched against three source columns with =INDEX(return_range,MATCH(A2&B2&C2,key1&key2&key3,0)).
