VLOOKUP with Dynamic LOOKUP & Return Range

[msquared99]

OK, not sure how to explain this idea, so I have uploaded a workbook with what I want to do. Basically, it is a Balance Sheet that has headers for each section of data, Cash, AR, Other AR, etc., the issue is I cannot use VLOOKUP because each "section" has Total instead of Total Cash.

My end goal is to just have to copy the balance sheet into my workbook and have the summary auto-populate instead of having to adjust the formulas or cell references each month. The data for each section varies in the number of rows month-to-month.

I am trying to stay away from VBA but may have to settle for it.

Thanks for any ideas.

 

[Chihiro]

See attached. Used intermediate steps to make formula simpler.
First, find the row where header "Cash / Cash Equivalents" etc are located.
=MATCH(Sheet2!B24,Sheet1!A:A,0)

Second, find next empty row in Column A to limit lookup range
=MATCH(TRUE,INDEX(INDIRECT("Sheet1!A"&E24&":A5000")="",0),0)-1

Third, using 2 intermediary, define the range and perform lookup
=INDEX(INDIRECT("Sheet1!C"&Sheet2!E24&":C"&SUM(Sheet2!E24:F24)),MATCH("Total*",INDIRECT("Sheet1!A"&Sheet2!E24&":A"&SUM(Sheet2!E24:F24)),0))

However, I'd recommend making "Total" row unique for each category (ex. "Cash / Cash Eq. Total") or adding helper column at end of source. It will make things much easier.

 

[msquared99]

AWESOME!!!!

I had the Row with Header part but finding the empty cell after header was what was killing me!

Thank you for your help!