Using VLOOKUP only if two conditions are met

Hi,

I'm trying to use the formula

Formula:

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

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!

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

Thanks kind people! So

Formula:

C2<>""

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?