Sum most recent value by reference criteria

[jamesfbuk]

Hi,

I have a set of data as below, with values for individual references for each quarter. These may be updated on a weekly basis.

Data
Ref	Date	Q1	Q2	Q3
L1	01/01/2015	1	1	1
L2	01/01/2015	2	2	2
L3	01/01/2015	3	3	3
L4	01/01/2015	4	4	4
L5	01/01/2015	5	5	5
L6	01/01/2015	6	6	6
L7	01/01/2015	7	7	7
L8	01/01/2015	8	8	8
L3	08/01/2015	3	5	7
L4	08/01/2015	4	6	8
L5	08/01/2015	5	10	15
L5	15/01/2015	6	7	8
L6	15/01/2015	7	8	9
L7	15/01/2015	10	10	10

<tbody>
</tbody>

I'm looking to put together a summary table which can be defined by the date as to what data is pulled in, i.e. if the date were 1/1/15 then the result would simply be identical to the first 8 rows. If it were changed to the 15/1/15, then it would be as below:

Desired Result
	Q1	Q2	Q3
L1	1	1	1
L2	2	2	2
L3	3	5	7
L4	4	6	8
L5	6	7	8
L6	7	8	9
L7	10	10	10
L8	8	8	8

<tbody>
</tbody>

I have the formula to put together the discrete list of references, but can't put together the formula to pull in the data. I'm assuming some form of sumproduct with a max reference in, but can't get there. Help massively appreciated.

Many thanks.

 

[jamesfbuk]

Have made a potential bit of progress, using Mr Fairwinds answer to this:

http://www.mrexcel.com/forum/excel-questions/201255-vlookup-last-match.html

Based on the data above, the formula =LOOKUP(2,1/(Reference=$A2),C$2:C$15) has produced a table with the latest data, where 'Reference' is a named range for A2:A15 (i.e. all of the L1, L2, etc.).

If I'm able to create a dynamic named range for the Reference element based on the dates (which should be in descending order), and based on a cell reference into which the desired date boundary can be input, I think that might work. Not certain mind!

Any alternatives or continuations on this theme still very gratefully received.

 

[jamesfbuk]

Now resolved, using dynamic reference instead of the locked 'Reference' as per last post. Dynamic reference created using Index - Match, altered to match this data would be:

=A2:INDEX(A:A,MATCH(*Date*,B2:B15,1)+1)

where *Date* is the desired date boundary (likely as a cell reference). The +1 at the end of the Index accounts for the Header line.

Thanks to those who reviewed this and tried to come up with a solution- would still be interested in alternatives from an academic perspective, or something that might process quicker (spreadsheet at the moment quite small, so don't know how this will run with much larger data yet).