list 1 has 400 names List 2 has 4000. find manes from list 1 on 2

I need help comparing 2 lists in excel. Here is my goal; I have one list with
4500 names and one list with 400 names. I would like to find out which of the
400 names is on the 4500 name list. how do I do it? thanks for your help.

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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Ed" <[email protected]> wrote in message
news:[email protected]...
> I need help comparing 2 lists in excel. Here is my goal; I have one list
with
> 4500 names and one list with 400 names. I would like to find out which of
the
> 400 names is on the 4500 name list. how do I do it? thanks for your help.

Let column A from A3 on house the longer list and column B from B3 on
the shorter list, with headers List1 and List2 in A2:B2.

C1: 0

which is mandatory.

C2: Idx

which is a header.

C3, copied down:

=IF((B3<>"")*ISNUMBER(MATCH(B3,$A$3:$A$4503,0)),LOOKUP(9.99999999999999E+307,$C$1:C2)+1,"")

D1:

=LOOKUP(9.99999999999999E+307,C1:C403)

D2: New List

which is just a header.

D3, copied down:

=IF(ROWS($D$3:D3)<=$D$1,LOOKUP(ROWS($D$3:D3),$C$3:$C$403,$B$3:$B$403),"")

The New List will have not have any blank records in between its first
and last items.

Note that the foregoing formula system is correct, efficient (that is:
fast), and robust.

Ed wrote:
> I need help comparing 2 lists in excel. Here is my goal; I have one list with
> 4500 names and one list with 400 names. I would like to find out which of the
> 400 names is on the 4500 name list. how do I do it? thanks for your help.

"Max" <[email protected]> wrote in message
news:%[email protected]...
> 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