 # INDEX MATCH

Highlighted
Occasional Contributor

# INDEX MATCH

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

4 Replies
Highlighted

# Re: INDEX MATCH

`=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

Highlighted

# Re: INDEX MATCH

`=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`
Highlighted

# Re: INDEX MATCH

Thank you this worked well for me.

Highlighted

Thank you!