# Thread: Contains a criteria (2k)

1. ## Contains a criteria (2k)

Now I know this is probably a basic question, but I just can't remember how to get a True/False result when a range contains/does not contain a specified criteria.

2. ## Re: Contains a criteria (2k)

The following returns TRUE if A1 contains the value "Criteria", FALSE if it does not :

=A1="Criteria"

Andrew C

3. ## Re: Contains a criteria (2k)

Nope, that just brings the #VALUE error. I'll give an example;
A1 has "Overdue"
B1 has "Overdue"
C1 has "Due"
D1 has "OK"

In E1 I'd like it to look in A11 & if the range has "Overdue" in it at all, return a value of "True" or "False" if there isn't.

4. ## Re: Contains a criteria (2k)

Sorry, I did not pick up on the Range aspect.

Try

=A11="Overdue", but when you enter it press Ctrl-Shift-Enter instead of Enter, as it is an array formula. It should be automatically enclosed in brace brackets ( like : {=A11="Overdue"})

Andrew C

5. ## Re: Contains a criteria (2k)

=ISNUMBER(MATCH("Overdue",A11,0))

or

=COUNTIF(A11,"Overdue")>0

Aladin

6. ## Re: Contains a criteria (2k)

This looks really awful but it works, avoids all the #VALUE and #NA stuff:

=ISNA(HLOOKUP("overdue",A11,1,FALSE))=FALSE

7. ## Re: Contains a criteria (2k)

Ah, getting somewhere, but I'm getting spurious results, it seems it's only checking the first column. I've attached a sample.

8. ## Re: Contains a criteria (2k)

Sorry again,

Try <big>=IF(ISNA(MATCH("Overdue",A11,0)),FALSE,TRUE)</big>

Andrew C

9. ## Re: Contains a criteria (2k)

Yep that's done the trick. Thank you kindly for your prompt help, you've pulled me out of a jam, Andrew.
Paul

10. ## Re: Contains a criteria (2k)

Actually Aladin's first offering looks best to me, and DoryO's solutions should also suffice.

Andrew

11. ## Re: Contains a criteria (2k)

Oops, sorry peeps, I was trying things out when you posted, & then just looked at the last post after.

BTW Aladin, I had tried the COUNTIF, but I got a count of "Overdue"s in the range.

Either way, thanks one & all.

12. ## Re: Contains a criteria (2k)

I prefer the MATCH formula myself.

However, the COUNTIF formula should work too:

=COUNTIF(A11,"Overdue")>0

I believe you omitted the >0 bit, that's why you get a count instead of a logical value.

Regards,

Aladin

13. ## Re: Contains a criteria (2k)

GUILTY, as charged [img]/forums/images/smilies/smile.gif[/img] That's exactly what I'd done. That's one of the caveats of formulea & code; you've GOT to be precise (I just wish I was more so).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•