Dynamic range addresses, references, and calculations

[squigman]

Good day everyone! This is my first time posting, hopefully I am doing it correctly (let me know if not)...

I have searched to try and find help on variable/dynamic range selection and then referencing on those range addresses, but I couldn't adapt what I found to my needs.

Below, in the orange cells I am trying to answer questions posed in the purple cells about the table in the blue cells. Unfortunately, I couldn't figure out the formula to answer any of those questions.

Big thanks in advance for any help you can give!

Formula Testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1							Starting Quaifying Value
2							1000
3
4							Ending Qualify Value
5							2000
6
7							Date range
8		Date when first passing qualifying value	Date right before first passing the Ending Qualifying Value	Date Range Adress of the prior two values	Sum of the cells in the Address in D10		Date	2023 Q1	2023 Q2	2023 Q3	2023 Q4	2024 Q1	2024 Q2	2024 Q3	2024 Q4	2025 Q1
9							Category A	456	855	943	1031	980	1207	880	1400	2100
10	Correct Output to be returned in the Orange cells above	2023 Q4	2024 Q4	K9:O9	5498
Sheet1

 

[hagia_sofia]

Hello, please test how this might work:

=LET(
s,G2,
e,G5,
c,H9:P9,
d,H8:P8,
v_1,INDEX(d,0,XMATCH(TRUE,c>s)),
v_2,INDEX(c,0,XMATCH(TRUE,c>e)-1),
v_3,ADDRESS(ROW(c),COLUMN(v_1),4)&":"&ADDRESS(ROW(c),COLUMN(v_2),4),
v_4,SUM(v_1:v_2),
HSTACK(v_1,v_2,v_3,v_4))

 

[squigman]

Hello, please test how this might work:

=LET(
s,G2,
e,G5,
c,H9:P9,
d,H8:P8,
v_1,INDEX(d,0,XMATCH(TRUE,c>s)),
v_2,INDEX(c,0,XMATCH(TRUE,c>e)-1),
v_3,ADDRESS(ROW(c),COLUMN(v_1),4)&":"&ADDRESS(ROW(c),COLUMN(v_2),4),
v_4,SUM(v_1:v_2),
HSTACK(v_1,v_2,v_3,v_4))

Wow! Thank you Hagia! This gets me to my final answer and shows me some great LET structure that I had not thought about, but I dont quite understand the v_2 term. It returns 1400 instead of the "2024 Q4" I was expecting. I made an adjustment in your formula to return the "2024 Q4", but that then returned an incorrect value of 0 for V_4 instead of 5498.

Would you be able to explain why your v_4 or SUM(v_1:v_2) which I think substitutes to SUM("2023 Q4":1400) worked to get 5498. I would have guessed that would have produced an error, as those dont seem like a range to SUM and dont to be similar references at all. I am missing something basic but critical.

Thanks again!

Formula Testing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1							Starting Quaifying Value
2							1000
3
4							Ending Qualify Value
5							2000
6
7							Date range
8	Column1	Date when first passing qualifying value	Date right before first passing the Ending Qualifying Value	Date Range Adress of the prior two values	Sum of the cells in the Address in D10		Date	2023 Q1	2023 Q2	2023 Q3	2023 Q4	2024 Q1	2024 Q2	2024 Q3	2024 Q4	2025 Q1
9							Category A	456	855	943	1031	980	1207	880	1400	2100
10	Correct Output to be returned in the Orange cells above	2023 Q4	2024 Q4	K9:O9	5498
11
12
13	per suggested formula	2023 Q4	1400	K9:O9	5498
14
15	my second attempt	2023 Q4	2024 Q4	K9:O9	0
Sheet1 (3)

Cell Formulas
Range	Formula
B13:E13	B13	=LET( s,G2, e,G5, c,H9:P9, d,H8:P8, v_1,INDEX(d,0,XMATCH(TRUE,c>s)), v_2,INDEX(c,0,XMATCH(TRUE,c>e)-1), v_3,ADDRESS(ROW(c),COLUMN(v_1),4)&":"&ADDRESS(ROW(c),COLUMN(v_2),4), v_4,SUM(v_1:v_2), HSTACK(v_1,v_2,v_3,v_4))
B15:E15	B15	=LET( s,G2, e,G5, c,H9:P9, d,H8:P8, v_1,INDEX(d,0,XMATCH(TRUE,c>s)), v_2,INDEX(d,0,XMATCH(TRUE,c>e)-1), v_3,ADDRESS(ROW(c),COLUMN(v_1),4)&":"&ADDRESS(ROW(c),COLUMN(v_2),4), v_4,SUM(v_1:v_2), HSTACK(v_1,v_2,v_3,v_4))
Dynamic array formulas.

 

[hagia_sofia]

Hello, many thanks for your feedback I definitely "outwitted" myself and have to apologize. It should be something like:

=LET(
s,G2,
e,G5,
c,H9:P9,
d,H8:P8,
v_1,INDEX(d,0,XMATCH(TRUE,c>s)),
v_2,INDEX(d,0,XMATCH(TRUE,c>e)-1),
v_3,ADDRESS(ROW(c),COLUMN(v_1),4)&":"&ADDRESS(ROW(c),COLUMN(v_2),4),
v_4,SUM(INDEX(c,0,XMATCH(INDEX(v_1:v_2,0,0),d))),
HSTACK(v_1,v_2,v_3,v_4))

As far as why the original v_4 part worked, this is due to that v_1:v_2 returned K8:O9.

 

[squigman]

Thank you so much! This has given me additional understandings beyond the question itself.

I came up with a tweak in the v_4 that seems to also work, but I have no doubt your is better form and I will use that.

=LET(
s,G2,
e,G5,
c,H9:P9,
d,H8:P8,
v_1,INDEX(d,0,XMATCH(TRUE,c>s)),
v_2,INDEX(d,0,XMATCH(TRUE,c>e)-1),
v_3,ADDRESS(ROW(c),COLUMN(v_1),4)&":"&ADDRESS(ROW(c),COLUMN(v_2),4),
v_4,SUM(XLOOKUP(v_1,d,c):XLOOKUP(v_2,d,c)),
HSTACK(v_1,v_2,v_3,v_4))