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

  • Thread starter Thread starter Mike C
  • Start date Start date
M

Mike C

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.
 
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.
Click to expand...

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.
 
Assuming you're entering th formula on Sheet1:

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