Forum Discussion
Using IF, AND based on multiple criteria
Hello,
In my attached spreadsheet, I need a formula for the values in column E.
If C=H2S and D=EDM, then the value needs to be B*150
If C=H2S and D=FTM, then the value needs to be B*225
If C=H2S-BR and D=EDM, then the value needs to be B*125
If C=H2S-BR and D=FTM, then the value needs to be B*150
I can't figure out if or how the OR function fits in.
Thank you!
6 Replies
Monica Bossert I'd do this with an INDEX MATCH MATCH instead, as it will be easier to extend to more codes in the future - see attached.
This worked, thank you!
You can accomplish using SWITCH.
=SWITCH( TRUE, AND(C2="H2S",D2="EDM"),B2*150, AND(C2="H2S",D2="FTM"),B2*225, AND(C2="H2S-BR",D2="EDM"),B2*125, AND(C2="H2S-BR",D2="FTM"),B2*150 )Monica Bossert - Guess you don't have SWITCH. Check your version https://support.office.com/en-us/article/switch-function-47ab33c0-28ce-4530-8a45-d532ec4aa25e.
