Index/match formula to return data to summary sheet

Hi,

I have a spreadsheet which I am creating and I have a new requirement...

In the attached sheet on the “Cost Summary” sheet I require a formula in the cell range highlighted in blue – B57:D84

Here I need the formula to pull data from the relevant sheet based on the user selection C53 (this will tell it which branch data sheet to look in) and C54 (this will tell it what to match). The index will be the branch number and always column B:E. I need the formula to find the first instance of the match in cell C54 and report the GL Code (Column B), Month Actual (Column D) and Month Budget (column E). Then I need it to find the next instance, report it, and find the next and so on and so on. I would only need it to return values greater than 1.

Its hard for me to explain so in green I have included an example with explanations of where the data needs to be pulled from.

Can anyone help please?

I tried this in B57 but it isnt correct - returns an error.

=INDEX(INDIRECT("'"&$C$53&" Data'!$B:$F"),MATCH($C$54,INDIRECT("'"&$C$53&" Data'!$D$5:$F$500"),0),1)

I was thinking that I would need the index row to add 1 each time it finds a match - unsure how to do this.

Any ideas?

How about using PivotTables?

I inserted one so that you can see what I mean. Pivottables are good for things like that.

I wouldnt be able to use a pivot table as the data could be held on any one of 15 sheets from "Branch1 Data" to "Branch15 Data" - which sheet will be dependandt on the user's selection

I am always amazed by the level of help on this forum - espically you NBVC

That worked brilliantly.

I may have one last match formula I need help on (famous last words) but I want to try and work it out myself first before I post - Its related to the same spreadsheet - pulling the M2 information (row 7 on the summary sheets) form a data table

I must be doing something wrong as I can not get the row to match...

Here is my formula – what am I doing wrong?

Please Login or Register  to view this content.

I have uploaded the file – I am trying to match the Branch and Profit centre on the sheet "summary" with Column A of the "M2 Data" sheet and matching the month which is in Cell B1 on the "summary" sheet with Row 4 on the “M2 Data” sheet

If there is a better formula than I have used above please say.

The data in the M2 sheet doesn’t have to be laid out this way if there is an easier way?

I have uploaded the sheet. Formula required in the yellow cells.

Thanks NBVC - this is now solved!