How to Use MATCH or XLOOKUP with Two Criteria in a WPS Office Excel Roster
Question details
The user needs a reliable formula to look up a person's security clearance by name or SSN together with a company or UIC code in a WPS Office Spreadsheets/Excel roster, even after the roster is resorted.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A personnel roster contains multiple people who may share a name, so a lookup must match both the person identifier and the company/UIC code before returning the correct security clearance.
- Observed behavior
- A single-criterion lookup may return the wrong clearance when duplicate names exist across companies. The desired state is a two-criteria formula that keeps the correct result after sorting or changing row order.
Open the workbook in WPS Office Spreadsheets and confirm the roster is formatted as a table or named range with the Name, UIC, and Security Clearance columns clearly labeled. Place the person and UIC lookup values in separate cells, such as A2 and B2, so the formula can reference them.
Use INDEX and MATCH for a two-criteria lookup in any WPS Spreadsheets version
This is the most compatible method for WPS Office Spreadsheets and older Excel versions, and it remains accurate when the roster is resorted.
The formula multiplies two logical tests to create an array of 1s and 0s: one where the Name matches A2 and one where the UIC matches B2. MATCH finds the row where both conditions are true, and INDEX returns the Security Clearance from that row.
In legacy versions that do not support dynamic arrays, press Ctrl+Shift+Enter to enter the formula as an array formula. In current WPS Office versions, a normal Enter is usually sufficient.
Click the cell where you want the Security Clearance result to appear.
Type =INDEX(Roster[Security Clearance],MATCH(1,(Roster[Name]=A2)*(Roster[UIC]=B2),0)) and press Enter. If WPS prompts for an array formula, press Ctrl+Shift+Enter instead.
Change the values in A2 and B2 to another name and UIC combination to confirm the formula returns the correct clearance from the matched row.
Use XLOOKUP with two criteria in the latest WPS Office and Excel 365
Use this shorter formula if your WPS Office version supports XLOOKUP. It performs the same two-condition match without requiring a separate MATCH function.
Create a Two-Criteria Lookup in WPS Office Spreadsheets
Use WPS Office Spreadsheets to open your Excel roster and apply either INDEX/MATCH or XLOOKUP with two criteria. WPS Office gives you the formula features you need to return accurate personnel clearances without changing your existing workbook.
- 1. Open your Excel roster: Launch WPS Office Spreadsheets and open the workbook that contains the personnel roster.
- 2. Add lookup input cells: Enter the person's name or SSN in one cell and the company/UIC code in another cell, such as A2 and B2.
- 3. Enter the two-criteria formula: In the result cell, type =INDEX(Roster[Security Clearance],MATCH(1,(Roster[Name]=A2)*(Roster[UIC]=B2),0)) or the equivalent XLOOKUP formula.
- 4. Confirm the result: Press Enter and test the formula with several name/UIC combinations to verify the correct clearance is returned.
Frequently Asked Questions
Does WPS Office support XLOOKUP with multiple criteria?
Recent WPS Office versions support XLOOKUP. If your version does not, use the INDEX and MATCH formula instead, which works in all WPS Spreadsheets versions and older Excel versions.
Why multiply the two criteria in the MATCH formula?
Multiplying (Roster[Name]=A2)*(Roster[UIC]=B2) converts TRUE/FALSE results to 1/0. Only the row where both conditions are TRUE produces 1, so MATCH(1,...) finds the correct personnel row.
Do I need to press Ctrl+Shift+Enter for the INDEX/MATCH formula in WPS Office?
In legacy WPS Spreadsheets versions, yes, press Ctrl+Shift+Enter to enter it as an array formula. Current dynamic-array versions usually accept a normal Enter.
Will the lookup still work if the roster is sorted differently?
Yes. Because the formula matches all rows by the two criteria rather than by a fixed position, it continues to return the correct clearance after sorting, filtering, or moving rows.
