I'm trying to pull monthly data from separate sheets using a combination of the index match and indirect functions. The formula works, but this formula doesn't work for matching the dates in my summary sheet.
The summary sheet is ordered January through December, but the individual sheets are ordered differently based on the individual project. Some are September through August or December through January. The formula that I'm using is not able to match the month on the project pages to the months on the summary page.
The formula I've been using is below. The project numbers are in column B, which identifies the spreadsheet. The variables I'm pulling are in column C. The E:E portion is what I need help with. This is the portion I need to be dynamic so that it pulls the data from the correct month in each project spreadsheet.
=INDEX(INDIRECT("'"&$B5&"'!E:E"),MATCH($C5,INDIRECT("'"&$B5&"'!A:A"),0))
Try something along the lines of:
=IFNA(INDIRECT("'"&$B4&"'!"&ADDRESS(XMATCH($C4,INDIRECT("'"&$B4&"'!A:A")),XMATCH(D$3,TEXT(INDIRECT("'"&$B4&"'!8:8"),"mmmm")))),"")