SOLVED

Help w/ Formula/Function

%3CLINGO-SUB%20id%3D%22lingo-sub-3047216%22%20slang%3D%22en-US%22%3EHelp%20w%2F%20Formula%2FFunction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3047216%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20am%20using%20a%20PC%20with%20Windows%2FOffice%20365.%3C%2FP%3E%3CP%3ELooking%20to%20build%20a%20formula%20that%20can%20compare%20B24%20%26amp%3B%20B25%20together%20against%20the%20table%20A33%3A38%20%26amp%3B%20B33%3AB38%20and%20if%20B26%26gt%3BB27%20returns%20F33%3A38%20or%20if%20B26%3CB27%20returns%3D%22%22%3E%3C%2FB27%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MelKunz_0-1640712492263.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F336207i1A882E0CBE0E3C00%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MelKunz_0-1640712492263.png%22%20alt%3D%22MelKunz_0-1640712492263.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20tried%20two%20different%20formulas%2C%20but%20can%E2%80%99t%20get%20the%20final%20result%2C%20as%20needed.%3C%2FP%3E%3CP%3EI%20have%20tried%20IF%2FAND%20statements%2C%26nbsp%3B%20%3DIF(AND(B24%3D1%2CB25%3D50%25%2CB26%26gt%3BB27)%2C242%2C0)%20but%20can%E2%80%99t%20get%20it%20nested%20correctly%20with%20additional%20IF%2FAND%20statements.%3C%2FP%3E%3CP%3EI%20have%20tried%20XLookup%20statement%2C%20%3DXLOOKUP(B24%26amp%3BB25%2CA33%3AA38%26amp%3BB33%3AB38%2CF33%3AF38)%20but%20can%E2%80%99t%20get%20the%20if%20statement%20placed%20correctly%20where%20if%26nbsp%3BB26%26gt%3BB27%20returns%20F33%3A38%20or%20if%20B26%3CB27%20returns%3D%22%22%3E%3C%2FB27%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20with%20either%20of%20these%20formulas%3F%20Or%20suggest%20another%20option%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3EMelissa%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3047216%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New 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 (New 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