I have a worksheet in which I am looking to utilize the Index/Matching formula to pull in exact values for the unique intersection of Department & Account values on a separate data sheet.
However I need the Index Array to be flexible as I'm looking for the same "unique" intersection of department & account values across multiple columns (columns represent months in my data sheet).
I've tried to use an Indirect formula by referring to the range of values in the data sheet, I've also tried another variation of the Indirect formula by naming my range - both formulas do not return results, though I know that values exist at my intersection point. If I hard code my Index Array, the formula works and I get the results to populate on my worksheet.
Here are my formulas now:
=IFERROR(INDEX(INDIRECT(_2015_December_Act), MATCH(AB$15&$G17, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")
Where _2015_December_Act is a named range on my Essbase data tab for values in the range of Q8:Q356.
=IFERROR(INDEX(INDIRECT(""&$A$8&"!"&$B$8), MATCH(AB$15&$G16, ('Essbase Data'!$C$8:$C$356&'Essbase Data'!$A$8:$A$356), 0)), "")
Where A8 = Essbase Data, and b8 = Q8:Q356 - this range represent the values in the month of December on my Essbase Data sheet.
What am I doing wrong?
I am trying to automate the update of the Array reference, so that the report could be refreshed monthly with minimal manual updates on the Index Array.