SOLVED

Help w/ Formula/Function

Copper Contributor

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:

MelKunz_0-1640712492263.png

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

 

7 Replies

@MelKunz 

=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.

best response confirmed by MelKunz (Copper Contributor)
Solution

@MelKunz 

As variant

=XLOOKUP(1, (B24 = $A$33:$A$38) * (B25 = $B$33:$B$38 ), $F$33:$F$38 ) * (B26 >= B27)

@MelKunz 

=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.

 

Thank you!
Many Thanks! This is perfect :)
Thanks!

@MelKunz , you are welcome

1 best response

Accepted Solutions
best response confirmed by MelKunz (Copper Contributor)
Solution

@MelKunz 

As variant

=XLOOKUP(1, (B24 = $A$33:$A$38) * (B25 = $B$33:$B$38 ), $F$33:$F$38 ) * (B26 >= B27)

View solution in original post