How to Convert Text Grades to Numeric Scores with XLOOKUP
Question details
The user needs to assign numeric payment values to text-based employee grades across a weekly worksheet.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating employee payroll or performance by looking up a text grade and mapping it to a numeric score.
- Observed behavior
- A formula is required to automatically fetch the matching numeric score from a lookup table and return zero if the text grade is not found.
Before applying the formula, ensure you have set up a complete reference table containing all possible text grades and their corresponding numeric scores.
Use XLOOKUP to Map Text Grades to Numeric Scores
Create a dedicated grade-to-score reference table and utilize the XLOOKUP function to automatically fetch and apply the matching numeric values.
The XLOOKUP function provides a modern, flexible way to search for a value in an array and return a corresponding value from another array. By defining a default 'not found' value, it gracefully handles missing data without the need for additional error-handling formulas.
Set up a lookup table in your worksheet named 'Scores'. Ensure it contains two columns: one named 'Grade' for your text-based grades, and one named 'Score' for the corresponding numeric payment values.
Select the cell where you want the numeric score to appear for your first employee. Type the formula: =XLOOKUP(B2, Scores[Grade], Scores[Score], 0) (assuming the employee's text grade is in cell B2).
Press Enter to execute the formula, then drag the fill handle down to apply it to the rest of the column. You can now easily sum or multiply these returned numeric scores to calculate the total amount payable for the week.
Use WPS Spreadsheet for Advanced Formula Calculations
WPS Spreadsheet fully supports advanced array functions like XLOOKUP, making it incredibly easy to manage payroll, evaluate performance, and convert text to numbers effortlessly.
- 1. Launch WPS Spreadsheet: Open WPS Office and create a new blank workbook or open your existing payroll worksheet.
- 2. Build your dataset: Enter your weekly results and set up a separate area for your 'Scores' reference table.
- 3. Apply the XLOOKUP function: Type =XLOOKUP into the designated cell to instantly map your text grades to exact numeric values.
Frequently Asked Questions
Why is my XLOOKUP formula returning a #NAME? error?
This error typically occurs if the table names or column names referenced in the formula (like Scores[Grade]) do not exist or are misspelled. Verify that you have properly formatted your reference data as a Table and named it exactly as referenced in your formula.
Can I use VLOOKUP instead of XLOOKUP for converting grades?
Yes, VLOOKUP can accomplish this task, but it requires the search column to be the first column in your reference table. Furthermore, to avoid errors for unfound text, you would need to wrap your VLOOKUP formula inside an IFERROR or IFNA function. XLOOKUP simplifies this by having a built-in 'if_not_found' parameter.
How do I format my reference data as a named table?
Select the range of cells containing your grades and scores. Go to the 'Insert' tab and click 'Table', or press Ctrl+T. Once the table is created, go to the 'Table Design' tab and type 'Scores' into the Table Name box on the far left.
