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.
5 Replies
I would second the request from peiyezhu that you provide a working copy of your workbook.
In addition, I'm speculating here, but this appears to be a situation where you are wanting to pull data (for reporting purposes) from data that has already been summarized elsewhere in the workbook. This leads me to wonder whether it mightn't be more effective to produce your higher level, more focused summary, directly from the raw data. What led me to speculate along those lines was your suggestion that you might try concatenating headers into "JulyADRUSD" in order to extract the desire set of numbers; that sounded like making something that should be simple -- extracting data from a database according to certain identifiers --and making it overly complicated.
This forum makes it difficult to see the images that are included in a reply. (It works OK for images in the initial post.) So I will ignore the conversation you have started with Peter.
See the attached workbook for two other possibilities.
Edit: The forum software again fails to retain the attached file. So...
Hi Peter,
Thank you for your response above. I am unable to follow through the steps that you have outlined above. I tried another function to no success: a combination of Index and XMatch as follows:
The monthly data has been moved to another worksheet: Sheet1. I am most definitely not understanding the argument logic in the above function. Please let me know if you can nudge me a little further in the right direction. Thank you.
Please attach a sample workbook and show your expected result clearly.
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.
