How to Look Up Matching Values in WPS Spreadsheets When the Lookup List Is in Random Order
Question details
The user needs to return corresponding values from a reference table when the lookup keys appear in random order or repeat multiple times in the result table.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building a result table where lookup keys are not sorted or repeat, and needs to pull the correct corresponding values from a reference table.
- Observed behavior
- A standard lookup formula returns only the first match rather than the matching occurrence for each repeated key.
Open your workbook in WPS Spreadsheets and identify the lookup key column, the value column to return, and the first result cell. Confirm whether your version supports dynamic array functions such as FILTER so you can choose the simpler formula.
Use INDEX, FILTER and COUNTIF to Return Matching Values in WPS Spreadsheets
Use this formula when your version of WPS Spreadsheets supports dynamic array functions and you need to return each repeated lookup key's matching occurrence.
FILTER returns all matching values for the current key, and COUNTIF selects which occurrence to use for the current row. This is the recommended approach when your WPS Spreadsheets version supports dynamic array functions.
Click the cell where the first matching value should appear, such as F3, in your WPS Spreadsheets worksheet.
Type =INDEX(FILTER(Table1[Number],Table1[Letter]=[@Letter]),COUNTIF(E$3:E3,E3)) and press Enter.
If your workbook doesn’t use Table1, replace Table1[Number] with the value column, Table1[Letter] with the key column, and [@Letter] with the current row lookup key cell.
Drag the fill handle from the first result cell down through all result rows to return the correct occurrence for each repeated lookup key.
Use an INDEX, SMALL, IF and COUNTIF Array Formula for Repeated Lookup Keys
Use this formula when FILTER is not available or you need compatibility with older spreadsheet engines.
Complete Random-Order Lookups in WPS Spreadsheets
Use Excel-compatible INDEX, FILTER, SMALL and COUNTIF formulas directly in WPS Spreadsheets to return the correct matching values when lookup keys are in random order or repeat.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open your .xlsx file, and click the worksheet that contains the lookup table.
- 2. Enter the lookup formula: In the first result cell, enter the INDEX/FILTER/COUNTIF formula or the INDEX/SMALL/IF/COUNTIF array formula for your version.
- 3. Apply the formula to all result rows: Use the fill handle to copy the formula down through every lookup key row.
- 4. Save your workbook: Save the file as .xlsx to keep compatibility with Microsoft Excel.
Frequently Asked Questions
Why does VLOOKUP return the wrong value when lookup keys repeat?
VLOOKUP always stops at the first match it finds. To return the second, third, or later occurrence, use an INDEX formula with FILTER or with SMALL, IF, and COUNTIF.
Do I need to sort the lookup list before using these formulas?
No. The INDEX and COUNTIF approach works even when the lookup list is in random order; sorting is not required.
How do I change Table1[Letter] if my workbook uses normal ranges?
Replace Table1[Letter] with the lookup key range, such as A2:A6, and replace Table1[Number] with the value range, such as B2:B6. For structured references, keep [@Letter] as the current row key.
Why do I need Ctrl+Shift+Enter for the INDEX, SMALL formula?
In spreadsheet versions without dynamic array support, Ctrl+Shift+Enter tells the application to evaluate the formula as an array formula so IF and SMALL can process multiple cells correctly.
Can WPS Spreadsheets open and save this workbook in Excel format?
Yes. WPS Spreadsheets supports .xlsx files, so you can open, edit, and save the workbook without converting it to another format.
