conditional formatting - like reverse lookup

[lezawang]

Hi
I want to highlight "pass" in result column to different color but not only pass but student name that correspond to that pass

For example, cell F2 = pass should be highlighted to different color and also Mary1 as well (like reverse lookup)

I can use conditional formatting to highlight pass to different color but was not able to combine the name as well. Is that possible? Thank you very much.


[TABLE="class: grid, width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]name[/TD]
[TD="width: 64"]test1[/TD]
[TD="width: 64"]test2[/TD]
[TD="width: 64"]test3[/TD]
[TD="width: 64"]average[/TD]
[TD="width: 64"]result[/TD]
[/TR]
[TR]
[TD]mary1[/TD]
[TD]77[/TD]
[TD]99[/TD]
[TD]65[/TD]
[TD]80[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD]adam1[/TD]
[TD]80[/TD]
[TD]46[/TD]
[TD]68[/TD]
[TD]65[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD]li1[/TD]
[TD]98[/TD]
[TD]34[/TD]
[TD]24[/TD]
[TD]52[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD]sue1[/TD]
[TD]55[/TD]
[TD]35[/TD]
[TD]37[/TD]
[TD]42[/TD]
[TD]fail[/TD]
[/TR]
[TR]
[TD]jack1[/TD]
[TD]100[/TD]
[TD]86[/TD]
[TD]52[/TD]
[TD]79[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD]linda1[/TD]
[TD]80[/TD]
[TD]58[/TD]
[TD]48[/TD]
[TD]62[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD]mary2[/TD]
[TD]67[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]29[/TD]
[TD]fail[/TD]
[/TR]
[TR]
[TD]adam2[/TD]
[TD]50[/TD]
[TD]66[/TD]
[TD]60[/TD]
[TD]59[/TD]
[TD]pass[/TD]
[/TR]
[TR]
[TD]li2[/TD]
[TD]42[/TD]
[TD]92[/TD]
[TD]2[/TD]
[TD]45[/TD]
[TD]fail[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Simply select A2:A whatever & F2:F whatever & use
=$F2="pass"

 

[lezawang]

That is really amazing. Thanks a lot for the help. I appreciate it.

 

[Fluff]

You're welcome & thanks for the feedback