Using VLOOKUP only if two conditions are met

[splosh313]

Hi,

I'm trying to use the formula

Please Login or Register  to view this content.

to check first whether C4's value is greater or equal to 0.5, then to check whether I4's text is 'P'. If these are both true, I want the VLOOKUP function to activate; if either one of the values is false, I want 'Fail' to be displayed.

It's only half-working at this point - if someones receives a 'P' in column I, but doesn't have any score in column C, the VLOOKUP function still activates. Excel seems to be assuming a blank cell in column C is greater than or equal to 0.5. The cells in Column C contains a formulae, which I wonder might be the cause.

Could anyone help? I've attached the spreadsheet, and the columns I'm referring to are in sheet 'Merge'.

Thanks!

[Pete_UK]

You could change your formula to this in J2:

=IF(AND(C2<>"",C2>=0.5,I2="P"), VLOOKUP(Assessments!C$2,Criteria!B$18:E$42,2,FALSE), "Fail")

and similar for the other formulae, then copy down.

Hope this helps.

Pete

[Special-K]

Excel seems to be saying

"">0.5

which is weird
Thats your problem
Maybe change C2>0.5 to
AND(C2<>"",C2>0.5)

[splosh313]

Thanks kind people! So

Please Login or Register  to view this content.

means 'C2 isn't blank'? Is that right? Also, why do you think Excel was treating a blank cell as being greater or equal to 0.5?

[Special-K]

Yep that's right.

In a new blank sheet in any cell enter
"">0
and it says TRUE ??