0

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?

  • Could you add a mockup of what the data looks like and an example of a lookup given a specific set of values? It isn't clear how the keys or key combinations relate to the apparent 150+ columns of data you want to pull from, – fixer1234 Jan 21 '15 at 18:39
1

Your formula looks OK to me except for some issues with parentheses, try this version

=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))

It needs to be confirmed with CTRL+SHIFT+ENTER

For simplicity the generic form is

=INDEX(Matrix,MATCH("x",row_labels,0),MATCH(1,("y"=headers1)*("z"=headers2),0))

which is what you have above

....or you can add another INDEX function to avoid "array entry", i.e. in the generic version:

=INDEX(Matrix,MATCH("x",row_labels,0),MATCH(1,INDEX(("y"=headers1)*("z"=headers2),0),0))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.