1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Vlookup

Discussion in 'Business Applications' started by cprao, Sep 1, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. cprao

    cprao Thread Starter

    Joined:
    Sep 1, 2004
    Messages:
    18
    Hi,
    I have a requirement in which I need to compare two columns of an Spread sheet and see whether there is a match or not.

    In detail, I have two columns A and B.
    I need to take the A1 and need to check with B(1:240), if there is a match, I need to print 'YES" on Column C of the same row
    This way I need to do a search for all the rows of the Column A.

    Then I will do the same for the second column (B) and compare with all the rows of the First column(A) and print the same in Col D.

    I am using the following formula for the first part of this which is not working.
    =VLOOKUP($A$2,$B$2:$B$130,"YES",FALSE)

    Can some one help me in this one ?

    I just got this formula from one of my friend. But it is not working.

    Thanks
    Cprao.
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Try the following formula in cell C1. Then copy formula down for all cells in column C.

    =IF(COUNTIF(B$1:B$240,A1)>0,"YES","NO")

    In cell D1 place the following formula and copy formula down for all cells in column D.

    =IF(COUNTIF(A$1:A$240,B1)>0,"YES","NO")


    You can also compare two lists using conditional formatting. Here is an example of how.

    http://www.j-walk.com/ss/excel/usertips/tip073.htm


    Rollin
     
  3. cprao

    cprao Thread Starter

    Joined:
    Sep 1, 2004
    Messages:
    18
    :) Hi Rollin_Again,

    Thanks for your reply. This is working but I have some problem with this. When a start passing the Formula for other cells, the range is also getting incremented. I mean for the First cell c, the formula will be
    =IF(COUNTIF(B1:B11,C2) > 0, "YES","NO")
    when I copy these to the second cell of C2, The formula becomes
    =IF(COUNTIF(B2:B12,C3) > 0, "YES","NO").

    I think this should be OK as long as both the columns are sorted on the same order separately.

    I am also looking at the other option of Conditional formatting. Will let you know when I am done with that, Because I think that seems to be better option than this.

    Thanks a lot for your help on this one.
     
  4. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    you need the absolute references as in Rollin's advise - use the $ sign in your formulas,

    lol

    Hew
     
  5. cprao

    cprao Thread Starter

    Joined:
    Sep 1, 2004
    Messages:
    18
    Hi HEWANM, :)

    Could you be more specific ? I just had a test run with the Rollin's advise and it is working.

    Thanks
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    With dollar signs $ placed before the row numbers like in my example above, the formula maintains its reference to the original row numbers. Therefore, by including the dollar signs $, the rows numbers will not be incremented when you copy the formula down to the other cells in the column.


    Rollin
     
  7. jmosmith

    jmosmith

    Joined:
    Aug 10, 2004
    Messages:
    6,091
    So is it working yet or not?

    and BTW very NICE coding there Rollin! (y)

    J
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/269140

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice