Function Problems

How to Use Excel Conditional Formatting with Named Value Mappings

Elise Williams Oct 10, 2026 870 views

Question details

The user needs to apply conditional formatting based on text values that represent numeric scores by mapping the text to numbers before comparing them.

How to Use Excel Conditional Formatting with Named Value Mappings

Product: Excel
Device & OS: not provided
Scenario: Comparing text values that have underlying numeric meanings (like letter grades or ratings) to apply dynamic color rules in a spreadsheet.
Observed behavior: By default, Excel compares text alphabetically, which yields incorrect conditional formatting results when specific letters represent non-alphabetical numeric scores (e.g., X=4, O=3, A=2, RS=1).

Before you start

Ensure you have a clear list of all your text categories and their corresponding numeric scores before setting up the conditional formatting rules.

Solution 1Recommended

Use VLOOKUP and a Mapping Table in Conditional Formatting

Create a named mapping table to convert text to numbers within your conditional formatting rule using the VLOOKUP function.

Because Excel sorts and compares text alphabetically, comparing letter grades or codes directly in conditional formatting often yields incorrect results. By creating a background lookup table, you can force Excel to compare the numerical value assigned to each letter.

Create the mapping table

In a blank area of your sheet, type your letters (e.g., X, O, A, RS) in one column and their corresponding numbers (e.g., 4, 3, 2, 1) in the adjacent column.

Name the range

Select the two-column table you just created, click into the Name Box (next to the formula bar), type 'List', and press Enter.

Select the target cells

Highlight the range of cells where you want to apply the conditional formatting (for example, O6:O20).

Create the first rule (Greater than)

Go to Home > Conditional Formatting > New Rule. Select 'Use a formula to determine which cells to format'. Enter the formula =VLOOKUP(O6,List,2,FALSE)>VLOOKUP(F6,List,2,FALSE).

Set the format

Click 'Format', choose a green fill color, and click OK to apply the rule.

Create the second rule (Less than)

Repeat the process to add another new rule with the formula =VLOOKUP(O6,List,2,FALSE)<VLOOKUP(F6,List,2,FALSE) and choose a red fill color.

Use VLOOKUP and a Mapping Table in Conditional Formatting

Relative Cell References: Make sure the cell references in your VLOOKUP formula match the top-left cell of your selected target range (e.g., O6) without dollar signs so the rule applies correctly across all rows.

Free Microsoft Office alternative

Use Conditional Formatting with Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced conditional formatting rules, including VLOOKUP formulas. You can easily map text values to numbers and highlight your data dynamically, just like in Microsoft Excel.

1. Set up your lookup table: Open your spreadsheet in WPS Office and set up your lookup table in two columns, then name the range 'List'.
2. Open Conditional Formatting: Select the cells you want to format, navigate to the Home tab, and click 'Conditional Formatting' > 'New Rule'.
3. Enter the VLOOKUP formula: Choose 'Use a formula to determine which cells to format' and input your VLOOKUP comparison formula.
4. Apply the colors: Click 'Format' to set your desired background color (e.g., Green or Red) and apply the rule.

100% compatibility with Microsoft Excel (.xlsx) file formatsFull support for VLOOKUP and named ranges in conditional formattingLightweight, fast, and completely free to useIntuitive interface with familiar formula inputs

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting formula return an error?

Errors usually occur if the named range ('List') is misspelled or if the text in your target cells doesn't exactly match the text in your lookup table. Ensure there are no trailing spaces in your cells.

Can I use this method for more than two colors?

Yes. You can add as many conditional formatting rules as you need. For example, you can add an 'equal to' rule (=VLOOKUP(O6,List,2,FALSE)=VLOOKUP(F6,List,2,FALSE)) and set it to a yellow fill color.

Do I have to use a named range for the lookup table?

While not strictly required, using a named range like 'List' makes your formula cleaner and prevents reference shifting. If you don't use a named range, you must use absolute references (e.g., $Z$1:$AA$4) for the lookup table array.

Does this conditional formatting rule update automatically if I change a letter?

Yes, Excel and WPS Spreadsheet will instantly recalculate the VLOOKUP formula and update the cell colors whenever you change the text values in either the target cells or the comparison cells.

Excel & Data Problems