Match on Name and Date Fields

[reme11]

Firstly I've tried Googling a response for days, but as I haven't been able to find anything I've signed up to post my first ever support request.

I have two sets of data, first set contains records from a timesheeting system, including:
Name Date

Where each name will be repeated mulitple times each day worked.

Second set of data relates to the invoices for the same resources, and includes:
Name Invoice No Date From Date To

And may include multiple invoices for the same resource.

What I'm trying to do is determine for each record in the timesheeting system (adding a column next to this data) what is the associated invoice number? A simple VLOOKUP returns the first match it finds, however if there are multiple invoices for the same person, I want to return the correct number based on the date fields.

I hope I've explained this well. I've attached a simple spreadsheet to outline what is required.Book1.xlsx

[Max, Singapore]

Not sure, but if you are looking to total the number of invoices,
then perhaps something like this will do ..
In C2, copied down:
=SUMPRODUCT(($H$2:$H$4=$A2)*($J$2:$J$4<=$B2)*($K$2:$K$4>=$B2),$I$2:$I$4)
------------------------------
Any worth? If yes, hit the little star at the bottom left of my response

[reme11]

Thanks for your reply Max, however I'm looking to just return the corresponding invoice number for each timesheet entry, not total the number of invoices.

[Max, Singapore]

Sorry, I don't have any other idea to offer you. If nobody else pops by your thread, try another post in programming forum

[Toonies]

hi this should put you on the right track

http://www.excelforum.com/excel-prog...-userform.html

http://www.ozgrid.com/VBA/advanced-find.htm

[reme11]

FYI I was able to get the correct formula using INDEX and SUMPRODUCT:

=INDEX($E$2:$H$5,SUMPRODUCT((A2=$E$2:$E$5)*(B2>=$G$2:$G$5)*(B2<=$H$2:$H$5)*ROW($F$2:$F$5)-ROW($F$1)+1)-1,2)

Where Table1 data was located in cells A2:C7 and table2 data was located in cells E2:H5.

Thank you!