Defoes,

With your list of last names in column A, and your first names in column B,
enter the desired last name in cell D1. Then in E1, enter 1, E2, enter 2,
etc, and in cell F1, array enter (enter using Ctrl-Shift-Enter) the
following formula.

=IF(ISERROR(INDEX(B:B,SMALL(IF($A$1:$A$1000=$D$1,R OW($A$1:$A$1000),100000),E
1))),"",INDEX(B:B,SMALL(IF($A$1:$A$1000=$D$1,ROW($ A$1:$A$1000),100000),E1)))

Watch line wraps, which should be taken out.

If you have problems getting it to work, contact me privately, and I will
send you a working version.

HTH,
Bernie
MS Excel MVP


"Defoes Right Boot" wrote in
message ...
My first column is a list of surnames so non-unique data. My second is a
list
of first names, again non-unique but there is no duplication of the
combination of surnames and first names. I need to list all the first
names
which go with an individual surname.

eg
Brown John
Brown Peter
Brown Michael
Smith John
Smith David
Jones Peter
Jones David

(but about 500 rows altogether) and I need to be able to, using the above
example, enter Smith in a cell and get a list showing "John" and "David",
or
enter Jones and get "Peter" and "David"

Can someone help? Please?