Autofill of cells after a lookup?

[Anthony Slater]

I already posted something similar but the answer I
received didn't quite make sense.

A1:A200 contains surnames
B1:B200 contains first names

C1 is a cell waiting for an input

After entering a Surname in cell C1, how can I produce a
complete list of First names that relate to the surname
(in cells D1x)

Someone said "offset'...does that help at all?

Any ideas/solutions would be greatly appreciated

 

[Jim Rech]

I'd suggest using Data, Filter, Advanced Filter.

First you would have to add a title row. Insert a new row 1 and enter
"Surname" in A1 and C1. Enter "First Name" in B1 and D1.

Enter the Surname you are interested in in C2.

Next select your list (A1:Bxxx).

Do Data, Filter, Advance Filter.

Select C1:C2 as the Criteria range.

Check the "Copy to Another Location" option and select D1 as that location.

Click OK.

--
Jim Rech
Excel MVP
|I already posted something similar but the answer I
| received didn't quite make sense.
|
| A1:A200 contains surnames
| B1:B200 contains first names
|
| C1 is a cell waiting for an input
|
| After entering a Surname in cell C1, how can I produce a
| complete list of First names that relate to the surname
| (in cells D1x)
|
| Someone said "offset'...does that help at all?
|
| Any ideas/solutions would be greatly appreciated