Hello Paul,

See attached file for two different solutions.

For the Index/Match formula, you can read more information about the technique used in this article.
If you are not so familiar with using Pivot Tables, I can recommend this article to start with.

Hi Anders,

Sorry for not replying sooner, but work took over,

Really great solution, but when I tried moving to a different position on spreadsheet I get a #REF! error?

I can not see what is causing the error, but only part of formula I do not understand is Match(1, what does the 1 do as I would normally use a cell ref?

Thanks

Paul

Hello Paul,

Currently viewing your file from my tablet and don’t have full view or functionality, but will try to give some help anyway.

Update: I have now checked your file in Excel and can confirm that the named ranges were missing in the name manager. As soon as I created those named ranges the values shows up correct. The name manager does not allow % characters in the name, so in order to still use the "dynamic" approach using the INDIRECT() function I renamed the GP% column to GP_Percent (as spaces are not allowed either).

If you want to use the GP% as header then use absolute cell reference instead.
Current formula in cell B310:
{=AVERAGE(INDEX(INDIRECT($A310);MATCH(1;($B$305=$B$4:$B$295)*($B$306=$A$4:$A$295);0));INDEX(INDIRECT($A310);MATCH(1;($B$305=$B$4:$B$295)*($C$306=$A$4:$A$295);0)))}

New formula in cell B310 using absolute cell reference:
{=AVERAGE(INDEX($G$4:$G$295;MATCH(1;($B$305=$B$4:$B$295)*($B$306=$A$4:$A$295);0));INDEX($G$4:$G$295;MATCH(1;($B$305=$B$4:$B$295)*($C$306=$A$4:$A$295);0)))}

I believe the error is due to the INDIRECT($A307) reference, it requires that you have set a named range Occupancy for D4:D294, do create named ranges for E4:E294, F4:F294 and G4:G294 using the headers in row 3 as the name for each column. Or change so that you use the cell references instead.

The answer to your question about number 1 as lookup value in the MATCH formula is found in the blog article I linked to previously, check the section ”So, how does it all work?” https://www.myonlinetraininghub.com/excel-factor-entry-4-index-and-match-two-criteria