How to compare a cell to a range.

1. How to compare a cell to a range.

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.

2. Hi mandy,

Try this formula:
``Please Login or Register  to view this content.``

3. 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.

4. Originally Posted by 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.
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

5. 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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1