Not sure if anyone can help me but thought I'd give it a shot...
I'm trying to create a formula to retrieve information from a data sheet. The data sheet contains a significant amount of information, splitting data based on store location, customer location, and information type (Revenue, GP1, GP2 information).
I would like a formula that is dynamic so that when it is copied over in my summary sheet it will automatically look at the applicable store location, customer location and information type and retrieve it accordingly. I have currently set up the formula as;
=INDEX('[Profitability Analysis FY13.xlsx]Data Sheet S13'!$B$8:$EH$85,MATCH($A17,('[ Profitability Analysis FY13.xlsx] Data Sheet S13'!$E$8:$E$85),0),MATCH(1,$B17=' Profitability Analysis FY13.xlsx] Data Sheet S13'!$B$4:$EH$4)*(J14='[Profitability Analysis FY13.xlsx] Data Sheet S13'!$B$7:$EH$7),0)
The idea being that the formula should look within the cells in B8 - EH85
in the data sheet, and then retrieve information based on the vertical look up value (store location) specified in cell A17
, and the horizontal look up value based on the customer location (cell B17) and information type (cell J14) are aligned.
The problem with this formula appears to be in the multiple criteria aspect (the horizontal look up). Does anyone have any suggestions how I can rephrase the formula so it will function correctly?