Lookup Multiple Occurrence Of Item In Array
 

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

--
John Sharke
-----------------------------------------------------------------------
John Sharkey's Profile: http://www.excelforum.com/member.php...fo&userid=1588
View this thread: http://www.excelforum.com/showthread.php?threadid=27369

Lookup Multiple Occurrence Of Item In Array
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


John Sharkey wrote:
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.

Lookup Multiple Occurrence Of Item In Array
 

John Sharkey wrote:
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

Lookup Multiple Occurrence Of Item In Array
 

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?

--
Regards,
Tom Ogilvy



"John Sharkey" wrote in message
...

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.


--
John Sharkey
------------------------------------------------------------------------
John Sharkey's Profile:
http://www.excelforum.com/member.php...o&userid=15884
View this thread: http://www.excelforum.com/showthread...hreadid=273691