How to Convert Critical, High, Moderate, Low Ratings to Numbers in Excel
Question details
The user needs to assign numeric values to priority text labels (Critical = 4, High = 3, Moderate = 2, Low = 1) using a formula.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Scoring or ranking tasks, tickets, or risks based on text-based priority labels.
- Observed behavior
- Needs an automated way to calculate a numeric score based on text inputs like Critical, High, Moderate, and Low.
Identify the cell containing your priority labels and ensure the text is spelled consistently, as formulas depend on exact or patterned matches.
Use the SWITCH Function for Exact Matches
The SWITCH function provides a clean and easily readable way to map exact text values to corresponding numbers without complex nesting.
If the cell contains only the priority keyword and nothing else, the SWITCH formula is the most efficient method to assign a score.
Click on the empty cell where you want the numeric score to appear.
Type the following formula: =SWITCH(LOWER(V35), "critical", 4, "high", 3, "moderate", 2, "low", 1, 0). Replace 'V35' with the cell reference containing your priority label.
Press Enter to get the result, then drag the fill handle down to apply it to other rows.
Use XLOOKUP with a Separate Reference Table
Best for situations where priority values or score weightings might change in the future, allowing you to update scores without editing the formula.
Use IFS and SEARCH for Labels Inside Longer Text
Useful if the priority label is buried within a longer string or description in the cell.
Quickly Manage Formulas and Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like SWITCH, XLOOKUP, and IFS. Easily process text labels and calculate numeric scores in a highly compatible environment.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the priority data.
- 2. Insert your formula: Use the formula bar to type your SWITCH or XLOOKUP function exactly as you would in Excel.
- 3. Calculate and drag: Press Enter to evaluate, then double-click the fill handle to populate the entire column instantly.
Frequently Asked Questions
Can I use a standard IF function instead of SWITCH or IFS?
Yes. You can use a nested IF formula like =IF(V35="Critical", 4, IF(V35="High", 3, IF(V35="Moderate", 2, IF(V35="Low", 1, 0)))). However, SWITCH or IFS are generally easier to read, write, and maintain.
Why is my formula returning an #N/A or error for matching text?
This usually happens due to trailing spaces in your cells. Wrap your cell reference in the TRIM function, such as =SWITCH(LOWER(TRIM(V35)), ...) to remove hidden spaces before matching.
Is XLOOKUP available in all versions of spreadsheet software?
XLOOKUP is a newer function available in recent versions of Excel and WPS Office. If you are using an older version, you can achieve the exact same result using the VLOOKUP function combined with your reference table.
