help in countif formula.

[Guest]

i have this table, wherein i wanted to have output in column3 names of those
who appears in column1 and column2

i.e.

name1 name2
jasper aileen
rd jasper
jp norman
aimee jojo
cris aimee

but as i use this formula - =if(countif(a:a,"=" & b1)>0,B1,""), this is what
happens,

name1 name2 name3
jasper aileen
rd jasper jasper
jp norman
aimee jojo
cris aimee aimee

is it possible for me to be able to line them properly at the top?

 

[Bob Phillips]

Put name3 in C1

Then select C2:C10, and in the formula bar enter

=IF(ISERROR(SMALL(IF(COUNTIF($A$1:$A$20,$B$1:$B$20)>0,ROW($A1:$A20),""),ROW(
$A1:$A20))),"",
INDEX($B$1:$B$20,SMALL(IF(COUNTIF($A$1:$A$20,$B$1:$B$20)>0,ROW($A1:$A20),"")
,ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

thanks!!!!

im a happy person!!!

 

[Bob Phillips]

That's our goal <G>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)