+ Reply to Thread
Results 1 to 5 of 5

How to compare a cell to a range.

  1. #1
    Registered User
    Join Date
    02-17-2008
    Posts
    3

    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. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Hi mandy,

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

  3. #3
    Registered User
    Join Date
    02-17-2008
    Posts
    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.
    Last edited by mandy02; 02-17-2008 at 06:06 PM.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Quote 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. #5
    Registered User
    Join Date
    02-17-2008
    Posts
    3
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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