VLOOKUP With Multiple Criteria?

Question

I've been trying to create a VLOOKUP to return a value based on if another column's "Submission" date falls in between date periods. My aim is to provide a view of all projects who have not submitted data in a certain column (For that week)

In one document I have an archive of projects submissions, adding a new line of values every time someone submits their project (Along with the time/date of submission). (Macro copies across information and saves). I've assigned every day of the year a week number, and vlookup'd TODAY() to allow me to pull off the current weeks information.

I'm trying to return column HN if the time it has been submitted is between the date periods, pulling off the NEWEST submission for that week, for that project. Each project has a unique number assigned to it.

(There can be multiple submissions in a week, so SUMif wouldn't work - multiplying the values by the amount of times the project has been submitted.)

If any more information is required, please let me know

Example of Archive Data:

Project OBR        Last Report Submitted:       Costs
9977               29/01/2016 11:19             1050 
9977               30/01/2016 12:30             1070

(The costs are kept as a subtotal)

Example of what I am trying at the moment:

=SUMIFS('[TUR Master Report.xlsm]Archive'!$HN:$HN,'[TUR Master Report.xlsm]Archive'!$B:$B,"<="&$DP:$DP,'[TUR Master Report.xlsm]Archive'!$B:$B,">="&$DO:$DO,'[TUR Master Report.xlsm]Archive'!$A:$A,$DT$2)

However, as I am using a sumif, the cost can be duplicated if multiple submissions are on the same week.

Then I have VLOOKUPs comparing TODAY() with the week numbers to pull the relevant week off

Answer 1

First, get the earliest date for that period. This will be needed as criteria to retrieve the associated cost.

For the sample image below in B2 as,

=AGGREGATE(15, 6, '[TUR Master Report.xlsm]Archive'!$B$2:$B$13/
                  (('[TUR Master Report.xlsm]Archive'!$B$2:$B$13>=DO2)*
                   ('[TUR Master Report.xlsm]Archive'!$B$2:$B$13<=DP2)*
                   ('[TUR Master Report.xlsm]Archive'!$A$2:$A$13=A2)), 1)

Now use that as one criteria in another two column match to retrieve the cost.

For the sample image below in C2 as,

=INDEX('[TUR Master Report.xlsm]Archive'!$HM:$HM,
           AGGREGATE(15, 6, ROW('[TUR Master Report.xlsm]Archive'!$1:$13)/
                           (('[TUR Master Report.xlsm]Archive'!$B$1:$B$13=B2)*
                            ('[TUR Master Report.xlsm]Archive'!$A$1:$A$13=A2)), 1))

Nothing to it.