SOLVED

IF Function with more criteria

Occasional Contributor

I need to calculate based in two or more criteria and with multiple return values. 

For example: 

If base is open and type is narrow, then the result must be B4-1.75

If base is open and type is medium, then the result must be B4-3.75

If base is door and type is narrow, then the result must be B4-2.25

If base is door and type is medium, then the result must be B4-4.25

If base is closed and type is narrow, then the result must be B4-1

If base is closed and type is medium, then the result must be B4-2.5

 

My operative system ins Windows 10, and Excel version is 2013. 

 

Thanks in advance

 

4 Replies

@yennyg To have multiple conditions you can use AND(...) function or (condition1)*(condition2)
But in this case it sounds like you should have a table with all the combinations and use a Lookup and/or FILTER or combination thereof

In the attached I used a table and then used and INDEX(...MATCH(), MATCH())

@mtarler Thank you so much.

How can I add another criteria?

I need to analyze three criteria.

Thanks a lot

 

best response confirmed by yennyg (Occasional Contributor)
Solution

@yennyg If you have multiple criteria you will need to have more of a list format:

basetypecolorvalue
narrowopenblue1.75
narrowopenred2.25
narrowdoorblue2.75
narrowdoorred3.25
narrowclosedblue1
narrowclosedred3.75
mediumopenblue6.5
mediumopenred9.25
mediumdoorblue4.25
mediumdoorred2.5
mediumclosedblue0.75
mediumclosedred-1

 

and then you can use a FILTER() function like:

=FILTER($D$8:$D$19,($A$8:$A$19=$F$8)*($B$8:$B$19=$G$8)*($C$8:$C$19=$H$8),"n/a")

 

see attached

 

Thank you.