Can't find corrext Index Match formula where different options have same sub-options

As shown on the attached, pricing list has four criteria, three (Supplier, Product Types and Finishes) are in columns, and the Size is in rows.

Challenge is that each Product Type has the same two choices for Finish.

Currently calculating the correct price by "nesting ifs" matching every permutation of Supplier, Type, Finish and Size to each column of prices.

I know this can be done by one overall Index/Match, but just can't work out the correct structure!

Hope someone can see the wood for the trees.

Any suggestions, pointers and solutions are welcome as ever.

Ochimus

You can put this formula in M12:

=INDEX(B5:I13,IF(M8*M10/1000000<A5,1,MATCH(M8*M10/1000000,A5:A13)+1),MATCH(M4,B3:I3,0)+(M6="Premium"))

I've assumed that you want to match the area with the next highest value in the table.

Hope this helps.

Pete

5. Why to the "Type" headers have an apostrophe before the number yet the type drop down values do not?

BSB

Oh yes, I forgot to mention that you need to remove the apostrophes from in front of the numbers in cells B3:H3.

Thanks for reminding me, BSB.

Pete

Works perfectly, so contributions from everyone much appreciated.

Typing "!" and leaving in the apostrophes were typos, so apologies.
Fortunately other suppliers charge on a different basis, so the formula will not have to account for them, other than starting "=If(M2="Supplier 1",index. . . .)
And sadly, can't lose the Merged Cells because the end user wants them that way in the document the table sits in in "real life"

Delighted to mark this as "Closed" - now just need to get someone to enable me to post the other qiuestion on this Forum that Sucuri have been blocking for two weeks for some bizarre reason, athough this one sailed through!

Ochimus