Multiple if and drop down formula?

[kwalker]

Hi.

I'm trying to see if there's a formula that would solve for the following:

I have multiple selections in a drop-down that each correspond with different values within a range.

For example, an education facility that's between 1001-10000 square feet costs v to build but costs x to build if between 10001-100000 square feet and a retail facility costs y between 1001-10000 and z to build between 10001-100000 square feet, and so on. Ideally, I would choose from the drop down and enter the precise square footage in one cell and the cost would automatically populate in another.

Is there a way to do this? I've been playing around with another different options but I can't seem to solve for both the multiple costs per square feet or the multiple building types.

Thanks!

[Melvinrobb]

Does the attached work?
Enter the square footage and building type in the grey cells, and the blue cell will give you the cost per square foot.

[kwalker]

That looks like it should work wonderfully, thanks!!

[Melvinrobb]

Glad to help.
I never thought to use the MATCH function in a VLOOKUP before. Pretty handy, especially in this scenario.

[FDibbins]

just fyi, a regular index/match would also have worked...
=INDEX($B$1:$E$6,MATCH(L3,B1:B6,1),MATCH(L4,B1:E1,0))

Nice solution though

[scott.s.fower]

you can definitely do this, however, i'm a bit confused when you say:

Ideally, I would choose from the drop down and enter the precise square footage in one cell and the cost would automatically populate in another.

what would you like to choose in the drop down? is it the building type, then you will type in the exact sq footage? this makes a difference in implementation.

i'm guessing that you will have a drop down for building type, and a drop down for sq footage in a range (ie 1001 - 10000) and use those to give an estimated cost,
then you will use the building type and a different cell in which you enter the exact sq footage to give you an actual cost?

let me know if i'm on the right track so i can better help you out

[Melvinrobb]

Re: FDibbins
Would the INDEX function work if you are not putting in exact matches for the square footage? I figured you would need to use VLOOKUP with TRUE as the range lookup in order for it to be able to return the correct $ if you entered in 10050, for example.

[FDibbins]

try it on the file you uploaded, and note that I used 1, not ) for the row match....

=INDEX($B$1:$E$6,MATCH(L3,B1:B6,1),MATCH(L4,B1:E1,0))

[Melvinrobb]

Of course!
I've always just used "0" in the Match function, so it didn't occur to me to use "1".