# Vlookup

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

Not open for further replies.

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

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

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

Joined:
Dec 9, 2003
Messages:
563

lol

Hew

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

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

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

and BTW very NICE coding there Rollin!

J

As Seen On

### Welcome to Tech Support Guy!

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

over 733,556 other people just like you!