Lookup and Conditional formatting

**Dannypak** · 09-13-2010, 10:29 AM

Hi,
I'm hoping to get some help with a lookup formula.
I have in Sheet1, contract numbers in column A that I need t lookup in Sheet2, for each Colum B to E in sheet1, if the corresponding Column in Sheet2 is conditionally formatted Green, then "Yes" on Sheet1 needs to be written and if Sheet2 is formatted RED, then "NO" needs to be written in Sheet1.

I have attached a sample worksheet for reference.

Looking forward to your assistance and thank you in advance!

**teylyn** · 09-13-2010, 10:40 PM

You can't test the color of a conditional format with a formula. You can, though, apply the same test as the CF that leads to the color.

Since you're obviously using Excel 2007, try this in B3, copy across and down

=IFERROR(IF(SEARCH("opt-in",INDEX('Sheet 2'!$K$1:$N$1000,MATCH($A3,'Sheet 2'!$A:$A,0),MATCH(G$2,'Sheet 2'!$K$2:$N$2,0))),"Yes"),"")&IFERROR(IF(SEARCH("opt-out",INDEX('Sheet 2'!$K$1:$N$1000,MATCH($A3,'Sheet 2'!$A:$A,0),MATCH(G$2,'Sheet 2'!$K$2:$N$2,0))),"No"),"")

see attached

**Dannypak** · 09-15-2010, 11:49 AM

Originally Posted by teylyn

You can't test the color of a conditional format with a formula. You can, though, apply the same test as the CF that leads to the color.

Since you're obviously using Excel 2007, try this in B3, copy across and down

=IFERROR(IF(SEARCH("opt-in",INDEX('Sheet 2'!$K$1:$N$1000,MATCH($A3,'Sheet 2'!$A:$A,0),MATCH(G$2,'Sheet 2'!$K$2:$N$2,0))),"Yes"),"")&IFERROR(IF(SEARCH("opt-out",INDEX('Sheet 2'!$K$1:$N$1000,MATCH($A3,'Sheet 2'!$A:$A,0),MATCH(G$2,'Sheet 2'!$K$2:$N$2,0))),"No"),"")

see attached

Hi,
Thank you very much for you advice, searching for "Opt-in" & "Opt-out" is a great solution.
I am having some trouble with the formula though...it's doesn't seem to be able to correctly search/index/match. I'm getting blanks all the way...

I attached a new sample workbook for you to take a look if you can...

Thanks again so much for your help!

dan

**teylyn** · 09-15-2010, 05:16 PM

Start in F3 with

=IFERROR(IF(SEARCH("opt-in",INDEX('Alliance Tracking Master'!$K$1:$N$1000,MATCH($A3,'Alliance Tracking Master'!$A$1:$A$1000,0),MATCH(F$2,'Alliance Tracking Master'!$K$2:$N$2,0))),"Yes"),"")&IFERROR(IF(SEARCH("opt-out",INDEX('Alliance Tracking Master'!$K$1:$N$1000,MATCH($A3,'Alliance Tracking Master'!$A$1:$A$1000,0),MATCH(F$2,'Alliance Tracking Master'!$K$2:$N$2,0))),"No"),"")

copy across.