"Max" <demechanik@yahoo.com> wrote in message
news:%23VHsyXOsFHA.3504@TK2MSFTNGP10.phx.gbl...
> One way ..
>
> Assume the 2 lists are in cols A and B, in row1 down with
> col A housing the 4500 names, col B containing the 400 names
>
> Put in C1:
> = IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),ROW(),""))
>
> Put in D1:
> =IF(ISERROR(SMALL(C:C,ROWS($A$1:A1))),"",INDEX(B:B,MATCH(SMALL(C:C,ROWS($A$1
> :A1)),C:C,0)))
>
> Select C1:D1, copy down to D400
>
> Col D will return all the names in the 400 list in col B which is found
> within the 4500 list in col A, neatly bunched at the top, with blank rows
> below

Or, with one single formula,
having 4500 names in Ra1, 400 names in Ra2:

{=IF(ISERROR(INDEX(Ra1,SMALL(IF(ISNA(MATCH(Ra2,Ra1,0)),
"",MATCH(Ra2,Ra1,0)),ROW(A1)))),"",INDEX(Ra1,
SMALL(IF(ISNA(MATCH(Ra2,Ra1,0)),
"",MATCH(Ra2,Ra1,0)),ROW(A1))))}
FormulaArray aside the first row of Ra2, then to be copied
alongside Ra2.

Bruno