INDEX MATCH

[brady]

Hi,

I am trying to write a formula based on 3 different types of variables. I started by using an extremely long If(and statement and eventually excel wouldn't let me keep going. You can see what I was typing in the sheet I attached.

I believe that I need to use Index / Match for the 3 variables but I'm not sure how to write it. Basically, I want to calculate the Sale price in column CH based on the city (column i), story (column BE), and bedrooms (column R). When these 3 variables match the variables in the assumptions tab I want the value in the assumptions tab to multiply by the square footage (column BA) in the sold tab. 

Let me know if this makes sense and how to do this! Thanks  

[#2]

Hi Brady,

Please test this formula:

=INDEX((assumptions!$C$4:$I$7,assumptions!$C$10:$I$13),MATCH(R2,assumptions!$B$4:$B$7,0),MATCH(I2,assumptions!$C$3:$I$3,0),BE2)*BA2

And find it the attached file.

Regards

[#3]

Brady,

=SUMPRODUCT(CHOOSE(BE2,assumptions!$C$4:$I$7,assumptions!$C$10:$I$13)*(assumptions!$B$4:$B$7=R2)*(assumptions!$C$3:$I$3=I2))*BA2

[#4]

Thank you this worked well for me.

[#5]

Thank you!