How to Use Two Criteria to Return a Survey Score in WPS Office Excel
Question details
The user needs an Excel formula that returns a sports survey score only when both the player ID and survey number match the lookup row.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is reviewing sports survey data and wants to pull a selected skill or score from a table based on a player ID and a survey number.
- Observed behavior
- A valid formula should match both values before returning the score. If either criterion does not match, the formula should return a no-match result or an error only if not handled.
Confirm that your player ID range, survey number range, and score range cover the same number of rows and that lookup values are entered exactly as they appear in the data. In WPS Office Spreadsheets, use Excel-compatible ranges without entire-column references if your workbook contains other data below the table.
Use XLOOKUP with Two Conditions in WPS Office Excel
Use this method when your WPS Office or Excel version supports XLOOKUP. It is the simplest way to match both player ID and survey number and return the survey score.
XLOOKUP accepts multiple criteria by checking where both comparisons return TRUE. Multiplying the two TRUE/FALSE arrays produces 1 only on rows where the player ID and survey number both match, so the lookup value is 1.
Select the empty cell where you want the survey score or skill to appear.
Type =XLOOKUP(1,(IDRange=PlayerID)*(SurveyRange=SurveyNumber),ScoreRange,"No match") into the formula bar, then replace IDRange, PlayerID, SurveyRange, SurveyNumber, and ScoreRange with your actual sheet references.
Press Enter. WPS Office Spreadsheets returns the score from the row where both criteria match, or "No match" when no row qualifies.
Use INDEX and MATCH with Two Conditions for Broad Compatibility
Use this method if you are sharing the workbook with older Excel versions or if XLOOKUP is not available.
Return a Survey Score in WPS Office Spreadsheets
Open your Excel workbook in WPS Office Spreadsheets and use either XLOOKUP with combined boolean conditions or INDEX/MATCH to match both the player ID and the survey number and return the selected skill or score.
- 1. Open your survey workbook: Install WPS Office Free, then open the sports survey .xlsx file in WPS Office Spreadsheets.
- 2. Click the output cell: Select the cell where you want the score for the chosen player ID and survey number to appear.
- 3. Type the two-criteria formula: Enter =XLOOKUP(1,(IDRange=PlayerID)*(SurveyRange=SurveyNumber),ScoreRange,"No match") or =INDEX(ScoreRange,MATCH(1,(IDRange=PlayerID)*(SurveyRange=SurveyNumber),0)), replacing the ranges with your real columns.
- 4. Review and save: Press Enter, confirm that the correct survey score is returned, and save the workbook in WPS Office Spreadsheets.
Frequently Asked Questions
Can XLOOKUP return a text value such as a skill name instead of a numeric score?
Yes. Point the ScoreRange argument to the text column that contains the skill or score you want to return. The formula returns the matching value from that column regardless of whether it is text or numeric.
Why is the formula not returning anything even though both criteria seem to match?
Check for extra spaces, text-formatted numbers, or inconsistent values in the player ID and survey number cells. Also ensure the criteria ranges and the return range have the same number of rows and do not include header cells.
Can I use this two-criteria lookup in older versions of Microsoft Excel?
XLOOKUP requires Excel 365 or Excel 2021, or a WPS Office version that supports XLOOKUP. INDEX and MATCH works in older versions, but you may need to enter it with Ctrl+Shift+Enter as an array formula in those versions.
How do I show a custom message when no player ID and survey number match?
In XLOOKUP, put your message as the fourth argument, such as "No match". In INDEX and MATCH, wrap the formula with IFERROR or IFNA and specify the message you want to display.
Can I replace the named ranges with table column references?
Yes. If your data is in an Excel table, you can use structured references such as Table1[Player ID], Table1[Survey Number], and Table1[Score] in the formula, and they will adjust automatically as the table grows.
