Contains a criteria (2k)

Topic

New Reply

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.

Reply | Quote

Replies

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

=A1=”Criteria”

Andrew C

Reply | Quote

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 A1:D1 & if the range has “Overdue” in it at all, return a value of “True” or “False” if there isn’t.

Reply | Quote

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

Try

=A1:D1=”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 : {=A1:D1=”Overdue”})

Andrew C

Reply | Quote

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

Reply | Quote

Sorry again,

Try =IF(ISNA(MATCH(“Overdue”,A1:D1,0)),FALSE,TRUE)

Andrew C

Reply | Quote

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

Reply | Quote

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

Andrew

Reply | Quote

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.

Reply | Quote

I prefer the MATCH formula myself.

However, the COUNTIF formula should work too:

=COUNTIF(A1:D1,”Overdue”)>0

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

Regards,

Aladin

Reply | Quote

GUILTY, as charged 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).

Reply | Quote

=ISNUMBER(MATCH(“Overdue”,A1:D1,0))

or

=COUNTIF(A1:D1,”Overdue”)>0

Aladin

Reply | Quote

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

=ISNA(HLOOKUP(“overdue”,A1:D1,1,FALSE))=FALSE

Reply | Quote