Forum Discussion
Help w/ Formula/Function
Hello
I am using a PC with Windows/Office 365.
Looking to build a formula that can compare B24 & B25 together against the table A33:38 & B33:B38 and if B26>B27 returns F33:38 or if B26<B27 returns “0”. Here is the table for reference:
I have tried two different formulas, but can’t get the final result, as needed.
I have tried IF/AND statements, =IF(AND(B24=1,B25=50%,B26>B27),242,0) but can’t get it nested correctly with additional IF/AND statements.
I have tried XLookup statement, =XLOOKUP(B24&B25,A33:A38&B33:B38,F33:F38) but can’t get the if statement placed correctly where if B26>B27 returns F33:38 or if B26<B27 returns “0”
Can anyone help with either of these formulas? Or suggest another option?
Thank you!
Melissa
As variant
=XLOOKUP(1, (B24 = $A$33:$A$38) * (B25 = $B$33:$B$38 ), $F$33:$F$38 ) * (B26 >= B27)
7 Replies
=IF(B26<B27,0,
IF(AND(B24=A33,B25=B33),F33,
IF(AND(B24=A34,B25=B34),F34,
IF(AND(B24=A35,B25=B35),F35,
IF(AND(B24=A36,B25=B36),F36,
IF(AND(B24=A37,B25=B37),F37,
IF(AND(B24=A38,B25=B38),F38,"")))))))
Above IF formula achieves the same result. With Office365 or 2021 you can apply IFS formula as well.
- Thanks!
As variant
=XLOOKUP(1, (B24 = $A$33:$A$38) * (B25 = $B$33:$B$38 ), $F$33:$F$38 ) * (B26 >= B27)- Many Thanks! This is perfect 🙂
MelKunz , you are welcome
=IF(B26<B27,0,VLOOKUP(B24&B25,CHOOSE({1,2},A33:A38&B33:B38,F33:F38),2,0))
This could be what you are looking for. Enter formula as matrix with ctrl+shift+enter if you don't work with Office365 or 2021.
- Thank you!
