compare two columns and display a third

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Back
Top