Lookup Multiple Occurrence Of Item In Array

[John Sharkey]

I have a list of names and telephone numbers. Many people on the lis
have multiple numbers. When I use Index(result,Match(looku
value,lookup array,0)), I always get the first phone number. I woul
like to be able to find the second and third phone numbers o
individuals.

Is there a way to do this?

Thanks

 

[Frank Kabel]

Hi
try the following array formula
=INDEX(result,SMALL(IF(lookup array=lookup value,ROW(lookup
array)),ROW(1:1)))
and copy this down

 

[Alan Beban]

I have a list of names and telephone numbers. Many people on the list
have multiple numbers. When I use Index(result,Match(lookup
value,lookup array,0)), I always get the first phone number. I would
like to be able to find the second and third phone numbers of
individuals.

Is there a way to do this?

Thanks.

If the freely downloadable file at http://home.pacbell.net/beban is
available to your workbook, you can array enter into a 3-cell (or 4-cell
or 5-cell or n-cell) column

=VLookups(lookup_value, lookup_array, 2)

Alan Beban

 

[Tom Ogilvy]

A B C
John Home 123
John Work 345
John Cell 678
Mary Home 321
Susan Work 657
Susan Home 111

Is the list exactly like that or are the numbers scattered or what? Does
each row have all the information for a single number?

Will you have multiple cells to which the values will be returned?