Forum Discussion
IF AND OR THEN ???
=($B$11*(100%+INDEX($B$3:$E$5,MATCH($B$12,$A$3:$A$5,0),MATCH($B$11,$B$2:$E$2,1))))Does this return your expected result?
What version of Excel do you have? If it's the most up-to-date (which is recommended) there are some new functions that could enable a more efficient formula.
For the time being, to show you what's possible -- albeit getting long and therefore harder to decipher -- I've attached a formula that uses the IFS function. So far, because I'm awaiting your answer with regard to the version of Excel you have, I've just dealt with the first column of values (i.e., column B).
=IFS(
AND(B11>=B2,B11<C2,B12="Good"),B11*(1+B3),
AND(B11>=B2,B11<C2,B12="Average"),B11,
AND(B11>=B2,B11<C2,B12="Bad"),B11*(1+B5)
)
And as I write that, I see what may be a better way even with an older version of Excel. That would be via a lookup or match. But let me send you this for now. Let me (and anyone else looking in) what version of Excel you have.
- I have version 2210
