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.

 

[jasonb75]

Is this what you mean? It is always a good idea to show your expected results with your example in order to clarify your description.

Note that the lookup range extends 1 row below the data.

Book1
	A	B	C	D	E
1	Reporting_Date	Asset_Number	Market_Value	Inputs combined	Output
2	30/09/2018	21975	6,822,164.00
3	30/09/2018	21979	14,355,242.00	21977	33,506,866.00
4	30/09/2018	21977	33,506,866.00	21975	6,470,263.00
5	31/12/2018	21975	6,470,263.00
6	31/12/2018	21979	13,971,294.00	21977	38,220,950.00
7	31/12/2018	21977	38,220,950.00	21975	6,916,398.00
8	31/03/2019	21975	6,916,398.00
9	31/03/2019	21979	13,971,294.00	21977	38,220,950.00
10	31/03/2019	21977	38,220,950.00	21975
Sheet2

Cell Formulas
Range	Formula
E2:E10	E2	=IF($D2="","",IFERROR(VLOOKUP($D2,$B3:$C$11,2,0),""))

 

[SubtleNoob]

Thank you for getting back so quickly. Apologies for not adding the solution column. It should look like the below.

Reporting_Date	Asset_Number	Market_Value	Inputs combined	Expected
30/09/2018​	21979​	14,355,242	21977​	21975​
31/12/2018​	21977​	38,220,950	21975​	21975​
31/03/2019​	21975​	6,916,398		21975​

 

[jasonb75]

How should the results look on the larger example? Is it just those rows that should have a result, with the rest blank?

 

[SubtleNoob]

All rows should have a result. The look up will look from one result to the next quarter, then from that result to the next quarter. The rows which have a blank value in column "Inputs combined" should show column "Asset_Number".

There will be roughly 170,000 lines.

 

[jasonb75]

look from one result to the next quarter, then from that result to the next quarter.

This is the part that I'm having trouble making sense of, I would need to see the larger example from post 1 with the full range of expected results and some indication of how you arrive at each one.

Remember, you understand what you need, we can only go on the information that you provide.

 

[SubtleNoob]

Okay, sorry I am finding it a little hard to articulate. I will try a different approach. I have made an example of values 1 to 3. Below is a formula which would work if the sample was not so big. Column "CalculationRequired" is the column that I need help with.

I need to find the final corresponding value. The final values of 1, 2 and 3 are 1, 2, 3. But before then they might be called 2, 3, 1. The column lookup value is something I have made that tells me the value for three months later to look up.

It creates a chain to follow, in the case of Jan-10 for starting value 1:

Value 1, says look up 2 in Apr-10.
In Apr-10 value 2 says look up 1 in Jul-10.
In Jul-10 value 1 says look up 3.
In Oct-10 value 3 says your value is 3.

Date	Value	LookupValue	CalculationRequired	Formula
Jan-10​	1​	2​	3​	=SUMPRODUCT(($A$2:$A$13=EOMONTH(A2,9))*($B$2:$B$13=SUMPRODUCT(($A$2:$A$13=EOMONTH(A2,6))*($B$2:$B$13=SUMPRODUCT(($A$2:$A$13=EOMONTH(A2,3))*($B$2:$B$13=C2),$C$2:$C$13)),$C$2:$C$13)),$C$2:$C$13)
Jan-10​	2​	3​	1​	=SUMPRODUCT(($A$2:$A$13=EOMONTH(A3,9))*($B$2:$B$13=SUMPRODUCT(($A$2:$A$13=EOMONTH(A3,6))*($B$2:$B$13=SUMPRODUCT(($A$2:$A$13=EOMONTH(A3,3))*($B$2:$B$13=C3),$C$2:$C$13)),$C$2:$C$13)),$C$2:$C$13)
Jan-10​	3​	1​	2​	=SUMPRODUCT(($A$2:$A$13=EOMONTH(A4,9))*($B$2:$B$13=SUMPRODUCT(($A$2:$A$13=EOMONTH(A4,6))*($B$2:$B$13=SUMPRODUCT(($A$2:$A$13=EOMONTH(A4,3))*($B$2:$B$13=C4),$C$2:$C$13)),$C$2:$C$13)),$C$2:$C$13)
Apr-10​	1​	2​	2​
Apr-10​	2​	1​	3​
Apr-10​	3​	3​	1​
Jul-10​	1​	3​	3​
Jul-10​	2​	2​	2​
Jul-10​	3​	1​	1​
Oct-10​	1​	1​	1​
Oct-10​	2​	2​	2​
Oct-10​	3​	3​	3​

 

[jasonb75]

That helps a lot, I follow what you 're trying to do now. I don't think that it's going to be easy with any approach.

I agree with your original statement that it will most likely require vba (a macro) but I'm going to have a look at formula methods first (probably not practical with ~170k rows, but it helps me think more logically with the vba).

 

[SubtleNoob]

Brilliant, thank you so much for your help.

 

[jasonb75]

I've given up on formulas and resorted to vba.

Are the dates in the first column always quarter end dates (31/3, 30/6, 30/9, and 31/12) or can they be variable?

Will there be any point where an entry for a given quarter could be missing and the loop needs to skip to the next quarter (advance 6 months instead of 3) or should such a gap always be considered as the end of the loop?

Is the data always sorted in date order (oldest first)?