SOLVED
Home

Conditional Formatting with formulas and VLOOKUP

%3CLINGO-SUB%20id%3D%22lingo-sub-320181%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20with%20formulas%20and%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320181%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20scheduling%20spreadsheet%20and%20I%20have%20Column%20D%20%26amp%3B%20Column%26nbsp%3BE%20with%20drop%20down%20list%20data%20for%20a%20range%20of%20time%20starting%20with%20%22MINUTES%22%20to%20%22%26gt%3B%203%20Months.%22%20These%20are%20assigned%20values%200-30%20with%20a%20VLOOKUP%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(D6%2CLISTS!%24A%242%3A%24B%2419%2C2%2C0)%3C%2FP%3E%3CP%3E%3DVLOOKUP(E6%2CLISTS!%24A%242%3A%24B%2419%2C2%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20use%20conditional%20formatting%20to%20make%20the%20contents%20in%20column%20E%20turn%20RED%20if%20the%20value%20is%20greater%20than%20the%20value%20in%20D.%20What%20is%20the%20formula%20if%20I%20have%20these%20hidden%20%22helper%20columns%22%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EALSO%2C%20is%20there%20any%20better%20way%20to%20do%20do%20this%2C%20maybe%20without%20the%20helper%20columns%3F%20Can%20I%20inject%20the%20VLOOKUP%20into%20the%20%26gt%3B%20formula%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20sample%20data%20example%20attached.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-320181%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321486%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20foumulas%20and%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321486%22%20slang%3D%22en-US%22%3EHi%20Jamie%2C%20I%20see%20no%20problem%20using%20helper%20columns%2C%20if%20you%20need%20to%2C%20as%20long%20as%20it%20achieves%20what%20you%20want%20that's%20the%20most%20important%20point.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320974%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20foumulas%20and%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320974%22%20slang%3D%22en-US%22%3EOK%20thanks!%20I%20did%20end%20up%20using%20something%20similar%20but%20I%20may%20have%20over%20complicated%20it...%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(B6%26gt%3B%3DC6%2C%22OK%22%2C%22PROBLEM%22)%20-%20Helper%20Column%3CBR%20%2F%3E%3D%24D6%3D%22Problem%22%20-%20Conditional%20Formatting%20Formula%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-320723%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20with%20foumulas%20and%20VLOOKUP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-320723%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20the%20attached.%26nbsp%3B%20Hopefully%20it%20will%20give%20what%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Jamie Ormand
New Contributor

I am working on a scheduling spreadsheet and I have Column D & Column E with drop down list data for a range of time starting with "MINUTES" to "> 3 Months." These are assigned values 0-30 with a VLOOKUP function. 

 

=VLOOKUP(D6,LISTS!$A$2:$B$19,2,0)

=VLOOKUP(E6,LISTS!$A$2:$B$19,2,0)

 

I want to use conditional formatting to make the contents in column E turn RED if the value is greater than the value in D. What is the formula if I have these hidden "helper columns" ?

 

ALSO, is there any better way to do do this, maybe without the helper columns? Can I inject the VLOOKUP into the > formula?

 

Some sample data example attached. 

3 Replies
Highlighted
Solution

Hi,

 

Try the attached.  Hopefully it will give what you want.

 

Kind regards

Highlighted
OK thanks! I did end up using something similar but I may have over complicated it...

=IF(B6>=C6,"OK","PROBLEM") - Helper Column
=$D6="Problem" - Conditional Formatting Formula
Highlighted
Hi Jamie, I see no problem using helper columns, if you need to, as long as it achieves what you want that's the most important point.
Related Conversations
Cell Validation - Drop Down Lists and Text Formatting
JenSmith in Excel on
1 Replies
I need the Formual...
alanaj in Excel on
1 Replies
pull data based on dropdown list
bbombb in Excel on
1 Replies
CHALLENGE
marcialfigueroa in Excel on
0 Replies