Mar 22 2022 08:52 AM
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 :)
Mar 22 2022 09:14 AM
@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())
Mar 22 2022 10:26 AM
@mtarler Thank you so much.
How can I add another criteria?
I need to analyze three criteria.
Thanks a lot
Mar 22 2022 12:33 PM
Solution@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
Mar 22 2022 12:33 PM
Solution@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