Lookup with multiple results between two workbooks
 

I hope someone can help me.

I have two different workbooks, one to track "tickets" written during the
month, this will correspond with a sheet in another workbook for each
employee. For ease of entry I am try to get the employee workbook do a
lookup of the name in the first workbook and return all results. I did get a
formula to work with lookup; however, it only returns the last ticket
written, there will be instances where more than one ticket is written to an
indivdiual within the month.

This is the function that I have that returns 1 result:
=LOOKUP('[Ticket Tracking.xls]March'!$I$89,'[Ticket
Tracking.xls]March'!$B$4:$B$600,'[Ticket Tracking.xls]March'!$D$4:$D$600)

How can I adjust this or write a new formula to return all results.

Thanks in advance

Lookup with multiple results between two workbooks
 

In Cell F2, enter this

=MATCH('[Ticket Tracking.xls]March'!$I$89,OFFSET('[Ticket
Tracking.xls]March'!$B$5,SUM($F$1:F1),0,COUNTA('[Ticket
Tracking.xls]March'!$B$4:$B$600)-SUM($F$1:F1),1),0)

and in cell G2, enter this

=IF(ISERROR(OFFSET('[Ticket Tracking.xls]March'!$B$5,SUM($F$2:F2)-1,2)),"",OFFSET('[Ticket
Tracking.xls]March'!$B$5,SUM($F$2:F2)-1,2))

Copy those two cells down for at least as many row as you expect possible duplicates. This assumes
there are headers in row 4 of the sheet March, and that you don't want to extract them.

--
HTH,
Bernie
MS Excel MVP



"SafetyIntern" wrote in message
...
I hope someone can help me.

I have two different workbooks, one to track "tickets" written during the
month, this will correspond with a sheet in another workbook for each
employee. For ease of entry I am try to get the employee workbook do a
lookup of the name in the first workbook and return all results. I did get a
formula to work with lookup; however, it only returns the last ticket
written, there will be instances where more than one ticket is written to an
indivdiual within the month.

This is the function that I have that returns 1 result:
=LOOKUP('[Ticket Tracking.xls]March'!$I$89,'[Ticket
Tracking.xls]March'!$B$4:$B$600,'[Ticket Tracking.xls]March'!$D$4:$D$600)

How can I adjust this or write a new formula to return all results.

Thanks in advance