Finding a value then find max value for a range of cells above

[ewhenth]

I have a data set of monthly data for nine different districts. What I am looking to do is find a district's value for a particular month than find the max value for the 36 cells above. But I need the formula to be dynamic so when I select a different district it looks up the correct month value then again finds the max for the 36 cells above.

In an effort to make it the lookup easier, I concatenate the districts and months together (District1January, District1February..., District2January,District2February...,etc.) but i don't know what formulas to nest together to have the formula move when i select a new district.

[oeldere]

You get better result on your question if you add a small excel file, without confidential information.

Please also add the expected results manualy in your file.

[ewhenth]

You get better result on your question if you add a small excel file, without confidential information.

Please also add the expected results manualy in your file.

Sorry about that. Attached is an example with an explanation.

[oeldere]

with a pivot table.

[Green Crocodile]

You can use this formula.

Please Login or Register  to view this content.

[ewhenth]

You can use this formula.

Please Login or Register  to view this content.

Thank you! Not sure I fully understand what the "<2" portion of the first match formula, or what the "B" indicates. Any further explanation would be much appreciated!

[oeldere]

@ewhenth

Please also reply on the solution in #4.