How to ask whether a value is in a range of values, and produce a YES/NO answer

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike C - 18 Mar 2008 03:47 GMT

I know there is a way to do this, but I can't quite come up with it.

I have a table in which I am trying to match one of the fields to
anothter table (i.e., to check to see if it exists in that table). I
need a yes or no result.

To illustrate, I am in Sheet1 E2, and need to know if Sheet1 B2
existsin in the range Sheet 5 B9:B100.

Thanks for any suggestions.

Reply to this Message

pub - 18 Mar 2008 04:31 GMT

Mike C <js2k111@yahoo.com> wrote in news:40dd464e-6b0f-47f1-af5f-
2df85f0bb964@q78g2000hsh.googlegroups.com:

> I know there is a way to do this, but I can't quite come up with it.
>
[quoted text clipped - 6 lines]
>
> Thanks for any suggestions.

you just want to know if just 1 cell (b2) is in the range?
use this formula in e2

=COUNTIF(Sheet5!$B$9:$B$100,B2)

that will count the number of time b2 is in the range. any count 1 or
more means that its in the range that many times. if you get 0 then b2
not in the range.

if you have a long column you want to check, then use this in an if()
statement and copy down.

Reply to this Message

T. Valko - 18 Mar 2008 04:38 GMT

Assuming you're entering th formula on Sheet1:

=IF(COUNTIF(Sheet5!B9:B100,B2),"Yes","No")

Signature

Biff
Microsoft Excel MVP

>I know there is a way to do this, but I can't quite come up with it.
>
[quoted text clipped - 6 lines]
>
> Thanks for any suggestions.

Reply to this Message

Rate this thread: