SOLVED

# IF Function with more criteria

Occasional Contributor

# IF Function with more criteria

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.

4 Replies

# Re: IF Function with more criteria

@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())

# Re: IF Function with more criteria

@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

# Re: IF Function with more criteria

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

 base type color value narrow open blue 1.75 narrow open red 2.25 narrow door blue 2.75 narrow door red 3.25 narrow closed blue 1 narrow closed red 3.75 medium open blue 6.5 medium open red 9.25 medium door blue 4.25 medium door red 2.5 medium closed blue 0.75 medium closed red -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.