Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup Problems (2k)

    I'm trying to do a comparison of 2 columns of data. I figured the best way to do this was to list the 2 columns that have data: 1 of the columns has many repeating values, the other does not, so the columns are not of equal length. So for each of the 2 columns, I have another column next to it to that checks to see if the values exist in the other columns. I tried using the "Lookup" function, with this as my code for each column:

    =IF(ISNA(LOOKUP(A2,$C$2:$C$370))=TRUE, "Not Found", "Found")

    =IF(ISNA(LOOKUP(C2,$A$2:$A$1562))=TRUE, "Not Found", "Found")

    I only got 2 values that weren't found of the entire bunch, so I knew something was up. The lookup function obviously doesn't do what I'd like it to do. I did a check and infact found items not existing in both columns. I also tried a simple 10 row example, and even that didn't work right using 1-9 and 4-12 as my data values. Since this method doesn't work, what would?

    My data is set up in this fashion:

    Data Source1 | Matching? | Data Source2 | Matching?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Problems (2k)

    You need to use the VLOOKUP function like this:

    <pre>=IF(ISNA(VLOOKUP(A2,$C$2:$C$370,1,FALSE)), "Not Found", "Found")

    =IF(ISNA(VLOOKUP(C2,$A$2:$A$1562,1,FALSE)), "Not Found", "Found")
    </pre>

    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Lookup Problems (2k)

    Without seeing the data, it's hard to say. Does this work better?

    =IF(COUNTIF($C$2:$C$370,A2),"Found","Not Found")

    =IF(COUNTIF($A$2:$A$1562,C2),"Found","Not Found")

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup Problems (2k)

    yeah i tried vlookup, but didn't know what to put at the "false" part of it and i used true by mistake, i figured it was something simple. Thanks Legare!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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