Using Max with Index & Match across two arrays of data

Hi there,

I'm trying to show the max amount for the year for each region for either the actual or forecast but am struggling with the basic syntax for finding the max within just the one array, let alone across two different arrays!

I've attached a sample workbook, and the basic premise for the formula in P2 is such:
Where the region matches cell O2 across both the named ranges "Act" & "FC", return the maximum value out of Act & FC for that row for the year.

There's a further twist in there may not be a corresponding lookup value in one of the arrays that matches O2 so it may only be referencing one array...

Any ideas please???

Thanks,

Mike

Hello
Alternative to sktneer's solution, slightly shorter:

Please Login or Register  to view this content.

DBY

Thank you both, both solutions work perfectly!!

Going to go with DBY's though, as more dynamic and allows movement on the rows and shorter which helps as I will be adding in several more tables of data to reference, thank you.

Quick follow up question: trying to duplicate this formula to bring back the MIN as well, but it only returns 0 due to the array multiplying out by zero. How could I tweak this so that I can show the MAX and MIN side by side please?

Thanks as always,

Mike

Thanks XOR LX for helping out. I was about to reply with a similar array formula. The Index method works great with Max but is difficult with Min as it returns zeros for no match. It could probably be made to work with some adjustments but if your're happy with arrays then it's perhaps the simplest way to go.

DBY

Hi XOR LX and hi DBY again,

More than happy to go down the array path and astounded at how simply you've both made this work, saved me a lot of frustration and head-scratching, much appreciated!

Thanks a million,

Mike

Glad we could help.