SOLVED

New Contributor

# 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

7 Replies

# Re: Help w/ Formula/Function

=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 (New Contributor)
Solution

# Re: Help w/ Formula/Function

As variant

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

# Re: Help w/ Formula/Function

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

# Re: Help w/ Formula/Function

Many Thanks! This is perfect

Thanks!

# Re: Help w/ Formula/Function

@MelKunz , you are welcome