Dynamic Lookup Array

[DaveyP]

I am trying to sum stock dividends between two dates.
I have created an XLOOKUP formula that does this based on inputting the stock symbol, however the Lookup array remains the same which means the result is inaccurate.
I have attached an image of my progress so far. Any help in creating a Dynamic Lookup Array would be appreciated, I want to avoid any volatile functions as my workbook is already quite large. Thx.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(f,FILTER(B1:G23,LEFT(B1:G1,3)=J6),SUM(FILTER(INDEX(f,,2),(INDEX(f,,1)>=J5)*(INDEX(f,,1)<=J4))))

 

[DaveyP]

Thank you very much for your reply, your solution works perfectly with the data I provided to you. Unfortunately, stock symbols are not all just 3 letters in length, my apologies for not considering this fact when I posted the request for assistance. The revised attachment shows what happened when I changed the symbols to: 1) a single letter 2) an .F suffix. Thanks again, next time I will try to use the XL2BB, which I admit I had never heard of until today.

 

[Alex Blakenburg]

This will only work in MS 365.

20220114 Sum Filter Lookup 2 dimensions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		A_Dates	A	AQN_dates	AQN	ZPAY.F_dates	ZPAY.F
2		20211230	0.21	20211214	0.88	20211229	0.12
3		20210928	0.21	20210914	0.88	20211126	0.1
4		20210629	0.21	20210614	0.88	20211027	0.1		Current Date	20220113	20220113	20220113
5		20210330	0.19	20210312	0.88	20210928	0.1		Sum from Date	20200929	20200929	20200929
6		20201230	0.19	20201214	0.83	20210827	0.1		Stock	A	AQN	ZPAY.F
7		20200929	0.19	20200914	0.83	20210728	0.1		Stock_Dates
8		20200629	0.19	20200612	0.83	20210628	0.1
9		20200330	0.18	20200313	0.83	20210526	0.1			1.2	4.35	1.52
10						20210428	0.1
11						20210329	0.1
12						20210224	0.1
13						20210127	0.1
14						20201229	0.1
15						20201127	0.1
16						20201028	0.1
17						20200928	0.1
18						20200827	0.1
19
Sheet1

Cell Formulas
Range	Formula
J9:L9	J9	=LET(dtcol,INDEX($B$2:$G$18,0,MATCH(J$6,$B$1:$G$1,0)-1), divdcol,INDEX($B$2:$G$18,0,MATCH(J$6,$B$1:$G$1,0)), SUM(FILTER(divdcol,(dtcol>=J$5)*(dtcol<=J$4),"")))

 

[Fluff]

Another option

=LET(f,FILTER(B2:G23,LEFT(B1:G1,FIND("_",B1:G1&"_")-1)=J6),SUM(FILTER(INDEX(f,,2),(INDEX(f,,1)>=J5)*(INDEX(f,,1)<=J4))))

 

[DaveyP]

Thank you very much to both of you, both solutions work great!
I had been using 2019 on my PC, but I have recently been experimenting using the online version of Excel to see if the XLOOKUP/XMATCH functions would do what I was trying to accomplish. Now that you both have opened my eyes to LET & FILTER, I can see that they are more powerful alternatives.
Time for me to upgrade. Thanks again for your solutions and for the introduction to LET & FILTER.

 

[Fluff]

Glad we could help & thanks for the feedback.