Excel Lookup Formula to Return a Score for Each Selection in WPS Office
Question details
The user needs an Excel lookup formula in WPS Office to map a selection value from 1 to 5 to a corresponding score and display the result automatically in another cell.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is building a scoring or evaluation sheet and wants a chosen numeric value to retrieve a predefined score.
- Observed behavior
- The score cell should automatically display the corresponding score after the 1–5 selection is made or changed.
Open your WPS Office spreadsheet and decide which cell will hold the choice, for example B3, and which cell will show the score, for example B4. Create or locate a two-column lookup range where column 1 contains choices 1–5 and column 2 contains the corresponding scores.
Use XLOOKUP to return a matching score automatically
Best for WPS Office and newer Excel-compatible versions; it directly returns the score for the selected choice without needing the score column to be to the right.
XLOOKUP searches the choice column and returns the value from the same row in the score column. The IFERROR wrapper shows a blank cell instead of an error when no valid selection exists.
Click the cell where the score should appear, such as B4.
Type =IFERROR(XLOOKUP(B3,$E$2:$E$6,$F$2:$F$6,""),"") and press Enter.
Enter a value from 1 to 5 in B3 and confirm that B4 updates to the corresponding score.
Use VLOOKUP for older Excel-compatible versions
Use this if your WPS Office or shared workbook environment does not support XLOOKUP and you want a classic Excel-compatible formula.
Add a data validation dropdown for the selection
Prevents invalid entries and lets users choose 1–5 from a dropdown instead of typing the value manually.
Return a Score Automatically with WPS Office Spreadsheets
WPS Office Spreadsheets supports XLOOKUP and VLOOKUP, so you can create a scoring sheet that updates automatically and remains compatible with Excel files.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and create or open a workbook in Spreadsheets.
- 2. Create the lookup table: In E2:E6 enter 1, 2, 3, 4, 5 and in F2:F6 enter the matching scores.
- 3. Enter the lookup formula: Click the score cell and enter =IFERROR(XLOOKUP(B3,$E$2:$E$6,$F$2:$F$6,""),"") or the VLOOKUP version.
- 4. Press Enter and test: Select a value in B3 to confirm the score cell updates automatically.
Frequently Asked Questions
Why does XLOOKUP return #NAME? in WPS Office?
This usually means the XLOOKUP function is not available in your WPS Office version. Update WPS Office to the latest version or use the VLOOKUP formula: =IFERROR(VLOOKUP(B3,$E$2:$F$6,2,FALSE),"").
Can I put the lookup table on another sheet?
Yes. Reference the sheet name in the formula, for example =IFERROR(XLOOKUP(B3,Sheet2!$E$2:$E$6,Sheet2!$F$2:$F$6,""),"").
How do I show a blank instead of #N/A when the selection is empty?
Wrap the lookup in IFERROR and set the empty string as the value, for example =IFERROR(XLOOKUP(B3,$E$2:$E$6,$F$2:$F$6,""),"").
How do I add a dropdown list for the 1–5 selection?
Select the choice cell, go to Data > Data Validation, choose List, set Source to =$E$2:$E$6, and click OK.
Does VLOOKUP work the same in WPS Office as Excel?
Yes, WPS Office Spreadsheets supports VLOOKUP with the same arguments: lookup value, table range, column index, and FALSE for exact match.
