Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,441
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup with Multiple results Quovardis via OfficeKB.com Excel Worksheet Functions 7 October 12th 07 06:28 AM
Lookup multiple results with gaps Fin Fang Foom Excel Worksheet Functions 3 November 12th 06 01:18 AM
Lookup Multiple results has gaps Fin Fang Foom Excel Worksheet Functions 0 November 12th 06 12:49 AM
Lookup multiple results that has gaps Fin Fang Foom Excel Worksheet Functions 0 November 12th 06 12:38 AM
How can I do a lookup and get multiple row results? Rashmi Excel Worksheet Functions 10 August 19th 05 02:04 AM


All times are GMT +1. The time now is 03:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"