Complex Conditional Formating

[Ben A]

I am trying to create condtional formating to turn a cell red with a complex
IF/ LOOKUP Function for the rule. The Function works outside of conditional
formating but when used with in condtional formating I get no result. What
do I need to do indorder for this work?

The IF/LOOKUP Formula is:
=IF(B5<"M",IF(B8>LOOKUP(B7,Data!F3:F38,Data!E3:E38)" "
The quatation marks have nothing in them because I would like no result just
the number already entered in cell "B8".

 

[Glenn]

I am trying to create condtional formating to turn a cell red with a complex
IF/ LOOKUP Function for the rule. The Function works outside of conditional
formating but when used with in condtional formating I get no result. What
do I need to do indorder for this work?

The IF/LOOKUP Formula is:
=IF(B5<"M",IF(B8>LOOKUP(B7,Data!F3:F38,Data!E3:E38)" "
The quatation marks have nothing in them because I would like no result just
the number already entered in cell "B8".

The formula above would not work outside of conditional formatting. Copy it
directly from a working cell to your post and try again.

 

[Ben A]

=IF(B5="M",IF(B8>LOOKUP(B7,Data!F3:F38,Data!E3:E38),"Fail"
If I enter the above formula into a cell it works great and will display the
word fail within the cell. I don't want to do that though I just want the
"B8" cell to change color if the statement is true. How do I do this?

 

[Sean Timmons]

In Conditional formatting, change to formula is from the drop-down

=IF(AND(B5="M",B8>LOOKUP(B7,Data!F3:F38,Data!E3:E38)))

Then format as needed.

 

[Ben A]

Thank you for your help I tried your suggestion and recieved an error telling
me the formula I entered contians an error. I can't find any mistakes in my
entry. Do you have any Ideas

 

[~L]

I think you need something like
=IF(AND(B5="M",B8>LOOKUP(B7,Data!F3:F38,Data!E3:E38)),TRUE(),FALSE())

 

[Ben A]

Thank you for every ones help but I figured it out. The solution was
=AND(IF(B5="F",B8>LOOKUP(B7,Y3:Y38,X3:X38))) The reference table for the
LOOKUP function had to be moved to the current worksheet and the AND needed
to be moved to the begining.

Again thanks for everyones help with this I don't think I could have figured
it out with a little help from all of you.