dynamic xlookup / sumifs

daveyc18

Well-known Member
Joined
Feb 11, 2013
Messages
791
Office Version
  1. 365
  2. 2010
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
 
Do you mean something like this ?

Dummy Data.xlsx
AB
12025
231-Jan
3$
4
5ASSETS
6Current
7Cash321
8Securities43
9Accounts Receivable432
10Due From Related Parties879879
11Non-current Assets76567
12Due from related parties [note 6]432
Sheet1
Cell Formulas
RangeFormula
B7:B12B7=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)))
 
Upvote 0
Solution
.. or perhaps this slight variation to spill the whole column, excluding blanks and upper case headings?

daveyc18_1.xlsx
AB
12025
231-Jan
3
4
5ASSETS
6Current
7Cash321
8Securities43
9Accounts Receivable432
10Due From Related Parties879,879
11Non-current Assets76,567
12Due from related parties [note 6]432
13Income Taxes Receivable765
14Prepaid Expenses & Other Assets234
15Due from related parties [note 6]432
16Goodwill3,268
17Intangible Assets, net324
18Capital Assets, net4,354
19Deferred Sales Commissions, net675
20Deferred Charges75,667
21Other Assets67
22LIABILITIES AND SHAREHOLDER'S EQUITY
23Accounts Payable & Accrued Liabilities76
24Due To Related Parties56,756
25Income taxes payable66
26Total current liabilities6,756
27Deferred Income Tax Liabilities5,656
28Other Liabilities & Deferred Credits567
29Total liabilities67
30
31SHAREHOLDER'S EQUITY
32Common Shares567
33Contributed Surplus756,675
34Retained Earnings675
35Other Comprehensive Income5,657
36Total shareholder's equity 55,676
Sheet4
Cell Formulas
RangeFormula
B7:B36B7=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.
 
Upvote 0
A bit shorter

daveyc18_1.xlsx
AB
12025
231-Jan
3
4
5ASSETS
6Current
7Cash321
8Securities43
9Accounts Receivable432
10Due From Related Parties879,879
11Non-current Assets76,567
12Due from related parties [note 6]432
13Income Taxes Receivable765
14Prepaid Expenses & Other Assets234
15Due from related parties [note 6]432
16Goodwill3,268
17Intangible Assets, net324
18Capital Assets, net4,354
19Deferred Sales Commissions, net675
20Deferred Charges75,667
21Other Assets67
22LIABILITIES AND SHAREHOLDER'S EQUITY
23Accounts Payable & Accrued Liabilities76
24Due To Related Parties56,756
25Income taxes payable66
26Total current liabilities6,756
27Deferred Income Tax Liabilities5,656
28Other Liabilities & Deferred Credits567
29Total liabilities67
30
31SHAREHOLDER'S EQUITY
32Common Shares567
33Contributed Surplus756,675
34Retained Earnings675
35Other Comprehensive Income5,657
36Total shareholder's equity 55,676
Sheet4
Cell Formulas
RangeFormula
B7:B36B7=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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top