Finding a Name on a list and returning all matching rows
 

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.

Finding a Name on a list and returning all matching rows
 

Would DataFilterAutofilter be of any assistance?


Gord Dibben MS Excel MVP

On Tue, 18 Aug 2009 08:44:01 -0700, Wh0o
wrote:

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.

Finding a Name on a list and returning all matching rows
 

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

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
Wh0o wrote:

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.