Finding a Name on a list and returning all matching rows

W

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.
 
D

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))),"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top