compare two columns and display a third
 

here's the challenge:
-I need to compare 2 columns: A1:A10 and B1:B20 (different range)
-For each common value I would like to display the information contained in
a third column C1:C20

I did some research on this forum and http://www.cpearson.com/ but so far no
good.
any help will be greatly appreciate to avoid doing it manually 2000 times.

chris90

=COUNTIF(A1:A10,B1) in C1 gives 1 if B1.value occurs in A1:A10, 0 if not.
Fill down to C10!

or =COUNTIF(A1:A10,B1) 0 gives TRUE or FALSE respectively.

Regards,
Stefi

"ch90" wrote:

here's the challenge:
-I need to compare 2 columns: A1:A10 and B1:B20 (different range)
-For each common value I would like to display the information contained in
a third column C1:C20

I did some research on this forum and http://www.cpearson.com/ but so far no
good.
any help will be greatly appreciate to avoid doing it manually 2000 times.

chris90

thank you for the quick reply. the formula works great for another problem I
had.

nevertheless I did not express correctly my challenge.
-I've 3 columns of data (A1:A10), (B1:B20), (C1:C20)
-For example, if B1 is in (A1:A10) I want to display C1 in D1
There are in different worksheets but it should not be a problem I guess.

Hope it is clearer.
Chris90

"Stefi" wrote:

=COUNTIF(A1:A10,B1) in C1 gives 1 if B1.value occurs in A1:A10, 0 if not.
Fill down to C10!

or =COUNTIF(A1:A10,B1) 0 gives TRUE or FALSE respectively.

Regards,
Stefi

"ch90" wrote:

here's the challenge:
-I need to compare 2 columns: A1:A10 and B1:B20 (different range)
-For each common value I would like to display the information contained in
a third column C1:C20

I did some research on this forum and http://www.cpearson.com/ but so far no
good.
any help will be greatly appreciate to avoid doing it manually 2000 times.

chris90

Formula for D1:
=IF(NOT(ISNA(VLOOKUP(B1, 'Sheet Name'!$A$1:$A$10,1,0))), C1, "")

HTH
Kostis Vezerides

thank you for this great formula and the quickness of your reply:
what about this:
-3 columns of data (worksheet1!A1:A10), (worksheet1!B1:B10),
(whorksheet2!C1:C20)
-For example, if C1 is in (A1:A10) I want to display B1 in worksheet2!D1

tricky or not tricky?
chris90

"vezerid" wrote:

Formula for D1:
=IF(NOT(ISNA(VLOOKUP(B1, 'Sheet Name'!$A$1:$A$10,1,0))), C1, "")

HTH
Kostis Vezerides