Chained lookup through a date series

[SubtleNoob]

Hello,

I need to loop a look up multiple times to find the final value for a range of dates.

So in an additional column in the below table i need to lookup into the next quarter the value in "Inputs Combined", then look up that value into the next quarter and so on until a max date.

Such that in rows, row 2 goes to row 6, row 6 to row 7. Outputting row 7 Asset value. But this would need to be in rows 2, 6 and 7 in the new column.

It needs to be something of a looped look up.

Reporting_Date	Asset_Number	Market_Value	Inputs combined
30/09/2018​	21975​	6,822,164
30/09/2018​	21979​	14,355,242	21977​
30/09/2018​	21977​	33,506,866	21975​
31/12/2018​	21975​	6,470,263
31/12/2018​	21979​	13,971,294	21977​
31/12/2018​	21977​	38,220,950	21975​
31/03/2019​	21975​	6,916,398
31/03/2019​	21979​	13,971,294	21977​
31/03/2019​	21977​	38,220,950	21975​

A macro solution or a formula would be welcome. I think it requires a macro.

Many thanks.

 

[SubtleNoob]

Correct it will always a Quarter end date.

There will be no need to skip, some may not have a follow on. Using the earlier example in Apr-10 there might be only 1 and 2. In which case the loop should stop there. But in Jul-10 there might be 1, 2 and 3 again.

It will be, but could have it in reverse if at all preferred.

 

[jasonb75]

It will be, but could have it in reverse if at all preferred.

There wouldn't be a lot of difference between forward and reverse to be fair, but if it wasn't in date order then that would make it more difficult.

Your comment did trigger one idea that I hadn't thought of though, I just came up with this as a quick tester to validate my theory though. I'm not sure how well this will work with the proper data, but it works with the second example. It's the blank rows that are cause for concern in my mind, if you could test the method and see if there are any anomalies in the results that will help.

In the event that it does work as required, and with acceptable efficiency then it will save me a few burnt out brain cells ? but in all honesty, I doubt that we will be that lucky.

Columns E and F are not essential, those calculations can be done within the formula in column D, but would be far less efficient. With the extra columns, the MATCH functions used to find the position of the next quarter are only calculated when the date in column A changes, the remaining rows take the result from above. Without the extra columns, they would need to be calculated in every row (multiple times, unless you have the new Office 365 LET function at your disposal).

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	Date	Value	Lookup Value	Calculation Required	Last Row Current	Last Row Next
2	31-Jan-10	1	2	3	4	7
3	31-Jan-10	2	3	1	4	7
4	31-Jan-10	3	1	2	4	7
5	30-Apr-10	1	2	2	7	10
6	30-Apr-10	2	1	3	7	10
7	30-Apr-10	3	3	1	7	10
8	31-Jul-10	1	3	3	10	13
9	31-Jul-10	2	2	2	10	13
10	31-Jul-10	3	1	1	10	13
11	31-Oct-10	1	1	1	13	13
12	31-Oct-10	2	2	2	13	13
13	31-Oct-10	3	3	3	13	13
Sheet5

Cell Formulas
Range	Formula
D2:D13	D2	=IF($F2=$E2,$B2,IFERROR(VLOOKUP($C2,INDEX($B:$B,$E2+1):INDEX($D:$D,$F2),3,0),$B2))
E2:E13	E2	=IF($A2=$A1,$E1,MATCH($A2,$A:$A))
F2:F13	F2	=IF($A2=$A1,$F1,MATCH(EOMONTH($A2,3),$A:$A))

 

[SubtleNoob]

At first glance it looks like it is working on the larger dataset, thank you very much. Will take a look over it later today and will confirm.