Conditional formatting with multiple IFs

[mucusboy]

I am trying to do some conditional formatting which I feel should be simple but I am getting nowhere


[TABLE="width: 380"]
<TBODY>[TR]
[TD]Product type</SPAN>
[/TD]
[TD]Code</SPAN>
[/TD]
[TD]Area</SPAN>
[/TD]
[/TR]
[TR]
[TD]X</SPAN>
[/TD]
[TD]1-A-3-008</SPAN>
[/TD]
[TD]W-1 Back26</SPAN>
[/TD]
[/TR]
[TR]
[TD]Y</SPAN>
[/TD]
[TD]3-B-1-023</SPAN>
[/TD]
[TD]W-1 Back23</SPAN>
[/TD]
[/TR]
[TR]
[TD]X</SPAN>
[/TD]
[TD]9-N-4-369</SPAN>
[/TD]
[TD]W-2 Front2</SPAN>
[/TD]
[/TR]
[TR]
[TD]N</SPAN>
[/TD]
[TD]7-Y-8-028</SPAN>
[/TD]
[TD]W-2 Back69</SPAN>
[/TD]
[/TR]
[TR]
[TD]Y</SPAN>
[/TD]
[TD]6-V-7-058</SPAN>
[/TD]
[TD]W-3 Left 78</SPAN>
[/TD]
[/TR]
[TR]
[TD]Y</SPAN>
[/TD]
[TD]2-S-5-014</SPAN>
[/TD]
[TD]W-1 Front7</SPAN>
[/TD]
[/TR]
[TR]
[TD]X</SPAN>
[/TD]
[TD]4-F-8-963</SPAN>
[/TD]
[TD]W-2 Right 8</SPAN>
[/TD]
[/TR]
[TR]
[TD]N</SPAN>
[/TD]
[TD]8-G-7-214</SPAN>
[/TD]
[TD]W-2 Front2</SPAN>
[/TD]
[/TR]
[TR]
[TD]B</SPAN>
[/TD]
[TD]3-B-1-089</SPAN>
[/TD]
[TD]W-2 Back69</SPAN>
[/TD]
[/TR]
[TR]
[TD]Y</SPAN>
[/TD]
[TD]9-N-4-769</SPAN>
[/TD]
[TD]W-3 Left 78</SPAN>
[/TD]
[/TR]
[TR]
[TD]H</SPAN>
[/TD]
[TD]7-Y-8-022</SPAN>
[/TD]
[TD]W-1 Front7</SPAN>
[/TD]
[/TR]
[TR]
[TD]V</SPAN>
[/TD]
[TD]6-Q-7-058</SPAN>
[/TD]
[TD]W-2 Right 8</SPAN>
[/TD]
[/TR]
[TR]
[TD]N</SPAN>
[/TD]
[TD]5-C-6-589</SPAN>
[/TD]
[TD]W-2 Front2</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


This is the data I am working with.

I am trying to get the row to highlight using conditional formatting IF column B contains "F" AND column C starts with "W-2". so in the case above the row I have made bold should get highlighted. I have tried he following to no avail:

=AND($b2="*-F*",$C2="W-2*")

=IF($B2="*-F*", IF($C2="W-2",TRUE,FALSE),FALSE)

I am not sure if it will take the * wildcard in conditional formatting or if I have used too many. Just lost the plot now.

I have tried various other conbinations along the way to no avail and searched the forum. Can anyone help???

 

[mucusboy]

Thanks tygrrboi! That works fantastic and I have been able to manipulate it for my other scenarios.

I have almost completed what I am trying to do but I now need to conditional format a row if row F contains an “S” and row G does not contain “LEFT”. I’ve got this working by using an ISERROR in the formula you gave so it now reads:

=AND(ISNUMBER(SEARCH("F", $C4)), ISERROR(SEARCH("LEFT",$G4)))

[TABLE="width: 380"]
<tbody>[TR]
[TD]Product type
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD]X
[/TD]
[TD]1-A-3-008
[/TD]
[TD]W-1 Back26
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]3-B-1-023
[/TD]
[TD]W-1 Back23
[/TD]
[/TR]
[TR]
[TD]X
[/TD]
[TD]9-S-4-369
[/TD]
[TD]W-2 Front2
[/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]7-Y-8-028
[/TD]
[TD]W-2 Back69
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]6-S-7-058
[/TD]
[TD]W-3 Left 78
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]2-S-5-014
[/TD]
[TD]W-1 Front7
[/TD]
[/TR]
[TR]
[TD]X
[/TD]
[TD]4-F-8-963
[/TD]
[TD]W-2 Right 8
[/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]8-S-7-214
[/TD]
[TD]W-2 Front2
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]3-B-1-089
[/TD]
[TD]W-2 Back69
[/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]7-Y-8-022
[/TD]
[TD]W-2 Left 78
[/TD]
[/TR]
[TR]
[TD]H
[/TD]
[TD]7-S-8-022
[/TD]
[TD]W-1 Back7
[/TD]
[/TR]
[TR]
[TD]V
[/TD]
[TD]6-Q-7-058
[/TD]
[TD]W-2 Right 8
[/TD]
[/TR]
[TR]
[TD]N
[/TD]
[TD]5-C-6-589
[/TD]
[TD]W-2 Front2
[/TD]
[/TR]
</tbody>[/TABLE]


However I am stuck again as I also want to exclude entries with “Back” in column G. I have tried =AND(ISNUMBER(SEARCH("F", $C4)), ISERROR(SEARCH("LEFT",$G4)), ISERROR(SEARCH("BACK",$G4))) but this doesn’t seem to work.

What am I missing or doing wrong?

 

[Joe4]

Your data sample seems to indicate column F, but your formula is referencing column C.
Which is correct?

Also, based on this latest requirement you have, which rows in your example should be highlighted?

 

[mucusboy]

Your data sample seems to indicate column F, but your formula is referencing column C.
Which is correct?

Also, based on this latest requirement you have, which rows in your example should be highlighted?

Sorry I am being so confusing... on my proper sheet they are columns F and G but in the examples in this thread I have just put an extract of that using columns C and D.

For my formula I will want it to be F and G.

Based on the data above rows 3, 6, 8 should be highlighted.

 

[Joe4]

Based on the data above rows 3, 6, 8 should be highlighted.

But none of those seem to have a "F" in the first column?
Your first condition is searching for the letter "F".

 

[tygrrboi]

Have you tried NOT(ISNUMBER(SEARCH
instead of (ISERROR(SEARCH for the conditions when you want all rows that don't have a certain element?