Vlookup, index match vertical data set

Hi,

I have a data set for which I need to extract the worst case values for the bottom most stack for each (building) column. The columns in the analysis program have been named A/1, A/2 etc and for each column there are 6 stacks (the stacks are the different building storeys with stack 1 being the stack of interest); in the sample workbook, we can see the excel export from the analysis program; building column A/1 and 6 stacks associated with it are presented before it moves onto the next column which is A/3.

At the moment for the bottom most stack I have been overwriting the values in the position column for the bottom most stack to read ‘Stack 1 A/1’ and then with the simple IF statement it returns the max value out of those values. However, doing this for all of the columns and all the different types of analyses will not be fun!

Is there a way I can automate this further, I have tried basic xlookup and index match but to no avail. Essentially if I could get the formula to recognise the building column and then recognise the various values for stack 1 and return the max out of these that would be great.

I have colour coded things to help show the required output values etc.

Any help is much appreciated.

Thanks

This proposal employs two helper columns which may be moved and/or hidden for aesthetic purposes.
Column J keeps track of the current A/# using: =IF(ISERROR(SEARCH("A/",A5)),J4,A5)
Column K keeps track of the Stack using: =IF(ISERROR(SEARCH("stack",A5)),K4,LEFT(A5,7))
The formula for R6 and down is: =SUMPRODUCT((J$5:J$118=Q6)*(K$5:K$118="stack 1")*(A$5:A$118=0),F$5:F$118)
Let us know if you have any questions.