Here's a solution for 2 sheets. It becomes much easier if you follow JB's advice and move all tables to a single sheet.
I inserted a column A into your tables which you can hide later
=C4&B4
For fixed cost
Formula:
=IF($A2="Sydney",INDEX(SYD!$E$4:$E$7,MATCH(B2&C2,SYD!$A$4:$A$500,0)),INDEX(BNE!$E$4:$E$7,MATCH(B2&C2,BNE!$A$4:$A$500,0)))
for adjustable cost
Formula:
=IF($A2="Sydney",INDEX(SYD!$E$4:$G$7,MATCH(B2&C2,SYD!$A$4:$A$500,0),MATCH(D2,SYD!$F$3:$H$3)),INDEX(BNE!$E$4:$G$7,MATCH(B2&C2,BNE!$A$4:$A$500,0),MATCH(D2,BNE!$F$3:$H$3)))
Bookmarks