Use Excel Lookup and Conditional Formatting to Color Total Hours
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.

- 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.
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.
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.
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.
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.
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.
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 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. 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. 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. 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. 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. Check the result: Select values from the A2 dropdown and confirm that B2 changes color according to the returned numeric total.

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.




