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
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
Solution

Hi,

 

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

 

Kind regards

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
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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies