dynamic xlookup / sumifs

[daveyc18]

https://docs.google.com/spreadsheets/d/1Mplda1RRNa7NW9mrWxvi6qCbz6mABAqP/edit?usp=drivesdk&ouid=116089498010699794174&rtpof=true&sd=true

doing an xlookup and getting values from sheet2

the issue is that next quarter, the data will be in column L (F25Q2), quarter after that , F25Q3 (column N), etc.

ideally, if it's feasible, I want a lookup/sumifs to dynamically get the numbers based on the quarter and year (I do already have separate formulas to get me the quarter number
and year), so it's NOT dependent on guessing the column letter

 

[Alex Blakenburg]

Do you mean something like this ?

Dummy Data.xlsx
	A	B
1		2025
2		31-Jan
3		$
4
5	ASSETS
6	Current
7	Cash	321
8	Securities	43
9	Accounts Receivable	432
10	Due From Related Parties	879879
11	Non-current Assets	76567
12	Due from related parties [note 6]	432
Sheet1

Cell Formulas
Range	Formula
B7:B12	B7	=LET(Qtr,"F"&RIGHT($B$1,2)&CHAR(10)&"Q"&ROUNDUP(MONTH($B$2&"-"&$B$1)/3,0), XLOOKUP(A7,Sheet2!$A$5:$A$48,XLOOKUP(Qtr,Sheet2!$A$4:$N$4,Sheet2!$A$5:$N$48)))

 

[Peter_SSs]

.. or perhaps this slight variation to spill the whole column, excluding blanks and upper case headings?

daveyc18_1.xlsx
	A	B
1		2025
2		31-Jan
3
4
5	ASSETS
6	Current
7	Cash	321
8	Securities	43
9	Accounts Receivable	432
10	Due From Related Parties	879,879
11	Non-current Assets	76,567
12	Due from related parties [note 6]	432
13	Income Taxes Receivable	765
14	Prepaid Expenses & Other Assets	234
15	Due from related parties [note 6]	432
16	Goodwill	3,268
17	Intangible Assets, net	324
18	Capital Assets, net	4,354
19	Deferred Sales Commissions, net	675
20	Deferred Charges	75,667
21	Other Assets	67
22	LIABILITIES AND SHAREHOLDER'S EQUITY
23	Accounts Payable & Accrued Liabilities	76
24	Due To Related Parties	56,756
25	Income taxes payable	66
26	Total current liabilities	6,756
27	Deferred Income Tax Liabilities	5,656
28	Other Liabilities & Deferred Credits	567
29	Total liabilities	67
30
31	SHAREHOLDER'S EQUITY
32	Common Shares	567
33	Contributed Surplus	756,675
34	Retained Earnings	675
35	Other Comprehensive Income	5,657
36	Total shareholder's equity	55,676
Sheet4

Cell Formulas
Range	Formula
B7:B36	B7	=LET(a,A7:A36,d,B2&B1,IF(EXACT(a,UPPER(a)),"",VLOOKUP(a,Sheet2!A1:V100,XMATCH(TEXT(d,"FYY"&CHAR(10)&"Q\"&MATCH(MONTH(d),{1,4,7,10})),Sheet2!A4:V4),0)))
Dynamic array formulas.

 

[Peter_SSs]

A bit shorter

daveyc18_1.xlsx
	A	B
1		2025
2		31-Jan
3
4
5	ASSETS
6	Current
7	Cash	321
8	Securities	43
9	Accounts Receivable	432
10	Due From Related Parties	879,879
11	Non-current Assets	76,567
12	Due from related parties [note 6]	432
13	Income Taxes Receivable	765
14	Prepaid Expenses & Other Assets	234
15	Due from related parties [note 6]	432
16	Goodwill	3,268
17	Intangible Assets, net	324
18	Capital Assets, net	4,354
19	Deferred Sales Commissions, net	675
20	Deferred Charges	75,667
21	Other Assets	67
22	LIABILITIES AND SHAREHOLDER'S EQUITY
23	Accounts Payable & Accrued Liabilities	76
24	Due To Related Parties	56,756
25	Income taxes payable	66
26	Total current liabilities	6,756
27	Deferred Income Tax Liabilities	5,656
28	Other Liabilities & Deferred Credits	567
29	Total liabilities	67
30
31	SHAREHOLDER'S EQUITY
32	Common Shares	567
33	Contributed Surplus	756,675
34	Retained Earnings	675
35	Other Comprehensive Income	5,657
36	Total shareholder's equity	55,676
Sheet4

Cell Formulas
Range	Formula
B7:B36	B7	=LET(a,A7:A36,IF(EXACT(a,UPPER(a)),"",VLOOKUP(a,Sheet2!A1:V100,XMATCH("*"&RIGHT(B1,2)&"*"&MATCH(MONTH(B2),{1,4,7,10}),Sheet2!A4:V4,2),0)))
Dynamic array formulas.