How to compare a cell to a range.

[mandy02]

I need to know how to compare a single cell to a range of cells. Here's what I want to do.

I have values in cells A1-A169. I want to compare the value in cell B1 to those values in A1-A169. If the value in cell B1 is equal to any value in A1-A156, then return "ok", otherwise "not okay."

I am doing this for B1, B2, B3, etc...

Here's what I did:

=IF(B1=$A$1:$A$169,"Ok","Not OK")

This isn't working though. It doesn't appear to be comparing the range. It only returns "Ok" if the value for say B1 is also in A1, even though I want it to compare the range. I'm betting it's painfully obvious, but what do I need to change?

Thanks in advance.

[Paul]

Hi mandy,

Try this formula:

Please Login or Register  to view this content.

[mandy02]

Sorry that didn't work. I realize though that saying it is looking for a value is not exactly right. Here is what it is comparing:

192.168.16.1.........................192.168.16.1
192.168.16.2.........................192.168.16.2
192.168.16.3.........................192.168.16.3
192.168.16.4.........................192.168.16.4
192.168.16.7.........................192.168.16.5
192.168.16.8.........................192.168.16.6
192.168.16.10........................192.168.16.7

So, for example Column 1 is A1-A7. Column 2 is B1-B7. I want to know if B1 is equal any IP in cells A1-A7. If so then return OK. Right now I'm only getting OK for cells B1-B4 because those values are in the same column. I keep getting Not Oka for cells B5-B7 even though the values do exist in A1-A7.

Thanks again.

[Richard Buttrey]

Sorry that didn't work. I realize though that saying it is looking for a value is not exactly right. Here is what it is comparing:

192.168.16.1.........................192.168.16.1
192.168.16.2.........................192.168.16.2
192.168.16.3.........................192.168.16.3
192.168.16.4.........................192.168.16.4
192.168.16.7.........................192.168.16.5
192.168.16.8.........................192.168.16.6
192.168.16.10........................192.168.16.7

So, for example Column 1 is A1-A7. Column 2 is B1-B7. I want to know if B1 is equal any IP in cells A1-A7. If so then return OK. Right now I'm only getting OK for cells B1-B4 because those values are in the same column. I keep getting Not Oka for cells B5-B7 even though the values do exist in A1-A7.

Thanks again.

By my reckoning it's only B5 & B6 that don't exist in A:A, B7 occurs in A5.

Anyway here's the formula

Please Login or Register  to view this content.

HTH

[mandy02]

That did the trick thanks! And you were right about the cells, I wasn't thinking when I typed that example. Thanks for understanding my gibberish.