Function Problems

Use Excel Lookup and Conditional Formatting to Color Total Hours

Olivia MillerOlivia Miller Nov 18, 2026 869 views

Question details

Look up the total hours associated with a dropdown selection and color the result green if it equals 30 or red if it does not.

Look Up Total Hours and Apply Green or Red Formatting in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user selects a value from a dropdown and wants to compare its associated total hours with 30.
Observed behavior
The desired result is a displayed total with green formatting for 30 and red formatting for other numeric totals.
Before you start

Identify the dropdown cell, the lookup values, and the total-hours column. In the example below, A2 contains the dropdown, D2:D10 contains lookup values, E2:E10 contains numeric hours, and B2 displays the result.

Solution 1Recommended

Look Up Hours and Create Conditional Formatting Rules

Use VLOOKUP to return the hours for the selected value, then apply separate rules for totals equal to and different from 30.

1
Enter the lookup formula

Select B2 and enter =IFERROR(VLOOKUP(A2,$D$2:$E$10,2,FALSE),"Not found"). Replace the cell references with those in your workbook. VLOOKUP searches the first column of the range and returns the value from the second.

2
Create the green rule

Select B2, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(ISNUMBER($B$2),$B$2=30), select Format, choose a green fill, and confirm.

3
Create the red rule

With B2 selected, create another formula-based rule through Home > Conditional Formatting > New Rule. Enter =AND(ISNUMBER($B$2),$B$2<>30), choose a red fill under Format, and confirm.

4
Test the dropdown

Choose different values in the A2 dropdown. Check that B2 turns green for a numeric result of 30 and red for other numeric results.

Look Up Hours and Create Conditional Formatting Rules
Unmatched values: The ISNUMBER checks leave the text “Not found” uncolored. IFERROR also displays that message for other lookup errors, so inspect the formula and source range if a known value is not found.
WPS Spreadsheets

Look Up and Color Total Hours in WPS Spreadsheets

Use VLOOKUP and formula-based conditional formatting in WPS Spreadsheets to display the selected total in green or red.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to load your workbook. Identify the dropdown cell and the columns containing lookup values and hours.
  2. 2. Add the lookup: For a dropdown in A2 and a lookup table in D2:E10, select B2 and enter =IFERROR(VLOOKUP(A2,$D$2:$E$10,2,FALSE),"Not found"). Adjust the references to match your sheet.
  3. 3. Add the green rule: Select B2 and open Home > Conditional Formatting > New Rule. Choose a formula-based rule, enter =AND(ISNUMBER($B$2),$B$2=30), and set a green fill.
  4. 4. Add the red rule: Create another formula-based conditional formatting rule for B2 using =AND(ISNUMBER($B$2),$B$2<>30), and set a red fill.
  5. 5. Check the result: Select values from the A2 dropdown and confirm that B2 changes color according to the returned numeric total.
Compare dropdown selections with total hours using VLOOKUP.Apply green and red fills with conditional formatting rules.Open and edit Microsoft Excel XLSX and XLS files.Work in a familiar spreadsheet interface.
microsoft office alternative - wps office

Frequently Asked Questions

Can I use XLOOKUP instead of VLOOKUP?

Yes. In a version of Excel that supports XLOOKUP, enter =XLOOKUP(A2,D2:D10,E2:E10,"Not found") in B2, then apply the same conditional formatting rules to B2.

Why is a result of 30 not turning green?

Check that B2 contains a numeric value rather than text and that the green rule under Home > Conditional Formatting > Manage Rules uses the correct cell reference and applies to B2.

Why does the lookup show “Not found”?

Check the A2 dropdown value against the entries in D2:D10, including extra spaces and text-versus-number differences. Also verify that the VLOOKUP range starts with the lookup column.

What happens if the lookup value appears more than once?

VLOOKUP returns the result for the first matching value in the lookup range. Check D2:D10 for duplicates if a different total is expected.