Vlookup

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 
Joined
Sep 4, 2003
Messages
4,916
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
 

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.
 
Joined
Dec 9, 2003
Messages
563
you need the absolute references as in Rollin's advise - use the $ sign in your formulas,

lol

Hew
 

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
 
Joined
Sep 4, 2003
Messages
4,916
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
 
Joined
Aug 10, 2004
Messages
6,091
cprao said:
Hi HEWANM, :)

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

Thanks
So is it working yet or not?

and BTW very NICE coding there Rollin! (y)

J
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top