Forum Discussion
Looking up specific period financials from multiple period worksheet
Hello,
I have the following sample data:
The data from Column I onwards is in a different worksheet in the layout that you see above. I need to be able to bring in the July data as shown from Cols C to E. One easy way would be to concatenate the month with the Sector and Currency headings in rows 3 & 4. For example, JulyADRUSD and then do a nested xlookup to bring in July ADR USD Accounts Receivable information. However this would mean creating a new row for my concat field. Is there any other way we can fetch the required information?
Hope you can point me in the right direction. Thank you.
1 Reply
If you named the header row containing the month names on your second sheet and and the data itself you could use XMATCH to determine the starting column number you require from your data and use that to drop data from earlier months before selecting the data you require.
= LET( colNum, XMATCH(month, monthHdr), TAKE(DROP(monthlyData, , colNum-1), , 3) )The formula returns a range reference so functions like TRIMRANGE can be used to capture data from tables of varying length.
