Two way lookup with multiple if/and variables - my mind is blown on this one

Worksheet No.1 is a summary - Worksheet No.2 is a pricing matrix.

Anticipated Functionality:

The estimated cost is the function of an area (square feet of a room for example) multiplied by a dollar value (cost / square foot).
The cost is derived by ranking 3 components (architectural, mechanical and electrical). The rankings of these components yield a percentile value and are categorized into columns on the pricing matrix, as follows: No. 1 - <33%, No. 2 - >33%, <66%, and No. 3 - >66%.
The area of a room is a variable that will constantly change, as will the pricing category based upon the percentile score.

For Example:

Step 1
On the Summary Sheet, the Estimated Cost (cell I8), will need to reference the area from E8 (188.8 in this case). Depending on the range of this area, it will need to reference the correct row from the Pricing Matrix Sheet.

Step 2
The architectural, mechanical and electrical percentile rankings from the Summary Sheet, will each need to reference their respective columns from the Pricing Matrix Sheet. These dollar values are added to one another to form a total cost / square foot. Note, the selection from within each of these 3 columns would need to correspond to the area row previously established in step 1.

Step 3
The estimated cost is simply the area, multiplied by the total cost / square foot.

Not so simple here... The complexities of the relationships have eluded all of my attempts to date, perhaps, this is cut and dry for some well seasoned people out there.

Any help, commentary or direction would be tremendously appreciated.

I've put together a possible solution; take a look at it and let me know what you think. I've kept it in multiple columns so you can follow the logic, but you can always combine it into one formula if you want.

I should note that this isn't my cleanest work... but it does work.

00-WCR-Summary_test_jc - Solution.xlsx

Dan

Thanks Dan,

How do I go about combining into one formula?

Originally Posted by jason_campigotto

Thanks Dan,

How do I go about combining into one formula?

Simple substitution!

Let's say

A2=5

.

And

B2=A2*10

And

C2 = B2*8

How do we combine B2 and C2? Well, we just take every reference to B2 and change it to whatever its formula is. So:

C2=A2*10*8

Obviously this formula is a little heavier, but it's the same general principle. The formula is:

=(INDEX('WCR Pricing Matrix'!$B$7:$D$14,MATCH(INDEX('WCR Pricing Matrix'!$A$7:$A$14,1+COUNTIF('WCR Pricing Matrix'!$A$7:$A$14,"<"&E10)),'WCR Pricing Matrix'!$A$7:$A$14,0),IF(F10=1,3,ROUNDDOWN(F10/(1/3),0)+1))+INDEX('WCR Pricing Matrix'!$E$7:$G$14,MATCH(INDEX('WCR Pricing Matrix'!$A$7:$A$14,1+COUNTIF('WCR Pricing Matrix'!$A$7:$A$14,"<"&E10)),'WCR Pricing Matrix'!$A$7:$A$14,0),IF(G10=1,3,ROUNDDOWN(G10/(1/3),0)+1))+INDEX('WCR Pricing Matrix'!$H$7:$J$14,MATCH(INDEX('WCR Pricing Matrix'!$A$7:$A$14,1+COUNTIF('WCR Pricing Matrix'!$A$7:$A$14,"<"&E10)),'WCR Pricing Matrix'!$A$7:$A$14,0),IF(H10=1,3,ROUNDDOWN(H10/(1/3),0)+1)))*E10

I've attached an example for you as well.

00-WCR-Summary_test_jc - Solution - Combined.xlsx

Good luck!

Dan