MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Comparing 2 columns


Posted by Sebastiao on November 14, 2001 12:02 PM

I have two columns.
The first one with 7000 rows, and the second one with 500 rows.

I need to check if the value in columm two exists in columm one.

If it exists, i'd like to show the number of the row where is is, in a third columm.

If it doesn't exists, i'd like to show NONE, in the third columm.

Thanks in advance


Posted by Aladin Akyurek on November 14, 2001 12:24 PM

Lets say that the first column is column A and the second column is column B. And you have values in column A from A1 on and in column B from B1 on.

In C1 enter: =IF(ISNUMBER(MATCH(B1,$A$1:$A$7000,0)),MATCH(B1,$A$1:$A$7000,0),"NONE")

Copy down this till row 500.

Aladin

========

Posted by Juan Pablo on November 14, 2001 12:40 PM

Aladin....

... i know you don't like doing a double calculation, so why don't use the COUNTIF() as the boolean ?

=IF(COUNTIF($A$1:$A$7000,B1),MATCH(B1,$A$1:$A$7000,0),"NONE")

Just suggesting... the other works just fine.

Juan Pablo

Posted by Aladin Akyurek on November 14, 2001 12:55 PM

Re: Aladin....

=IF(COUNTIF($A$1:$A$7000,B1),MATCH(B1,$A$1:$A$7000,0),"NONE")

Simply slept thru I guess. :)

Sebastio -- I strongly suggesting using this one (which I should have suggested in the first place as Juan kindly reminds me.)

Aladin