checking if a certain value is in an array: excel

Hi,

I am using excel 07 and want to write an IF statement to check if there is a certain value in a column of data.
My original formula was =if(A6:A15="FAUTY",FAIL,PASS) which i was hoping would say FAIL if there was a FAULTY in my column and PASS if there wasnt. Instead I only get a #VALUE! error??

Can you please help?
WTC_ServicesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
nutschConnect With a Mentor Commented:
yes, because you're using an array in a formula that is not expecting one, in addition to using strings without double quoting them.

Try
=if(isnumber(match("FAULTY",A6:a15,0)),"FAIL","PASS")

Thomas
0
 
nutschCommented:
or
=if(countif(a6:A15,"FAULTY")>0,"FAIL","PASS")

T
0
 
WTC_ServicesAuthor Commented:
Beautiful,

Thanks very much Thomas, I eventually used a combination of the two with:
=IF(COUNTIF(P13:P57,"FAULTY"),"FOR REVIEW","PASS")
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.