You could concatenate items of each list in a new range and adapt the
formulas system to the new ranges.

John wrote:
If I wanted to check that the Surname AND the Firstnames were the same in each of four columns,
what changes would I make? Ideally I would wwish that the output was as below:-

Column A Column B Column C Column D
Surname Forename Common entry Surname Common entry Forname
Wilson Bob Wilson Bob



Jonah
----------------------------------------
Aladin Akyurek wrote:


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.



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.