• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

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?
0
WTC_Services
Asked:
WTC_Services
  • 2
1 Solution
 
nutschCommented:
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now