Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP VALUE OCCURS ONCE IN TABLE ARRAY - RETURN LAST OCCURRENCE | Excel Worksheet Functions | |||
Lookup for the first occurrence | Excel Worksheet Functions | |||
Multiple Item Lookup | Excel Worksheet Functions | |||
Multiple Item Lookup | Excel Worksheet Functions | |||
V Lookup 2nd Occurrence | New Users to Excel |