Finding a Name on a list and returning all matching rows

[Wh0o]

I have a spreadsheet with a list of names numbers and emails. I am trying to
create that I would be able to type in the last name to a cell and it will
retrieve that rows information. The problem I am running into is that I am
only familiar with Vlookup and that only returns the first match that it
finds even though there may be 3 Williams on the list. Please use the
example below for your answer as I am new to excel..

A B C
1 Wallace Will 555-5555
2 Williams John 555-5555
3 Williams Carl 555-5555
4 Williams Marc 555-5555

I wouls appreciate and help with this.

 

[Gord Dibben]

Would Data>Filter>Autofilter be of any assistance?


Gord Dibben MS Excel MVP

 

[Domenic]

Here's a formula approach... Assuming that A2:C5 contains the data, and
E2 contains the last name of interest, try...

F2:

=COUNTIF(A2:A5,E2)

G2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IF(ROWS(G$2:G2)<=$F$2,INDEX(A$2:A$5,SMALL(IF($A$2:$A$5=$E$2,ROW($A$2:$A$
5)-ROW($A$2)+1),ROWS(G$2:G2))),"")