Home

Two formulas that work dont work when combined with IFs or IF

%3CLINGO-SUB%20id%3D%22lingo-sub-645033%22%20slang%3D%22en-US%22%3ETwo%20formulas%20that%20work%20dont%20work%20when%20combined%20with%20IFs%20or%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-645033%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20A%20is%3C%2FP%3E%3CP%3E%3DIFS(MATCH(%24AA15%2C%24AJ%245%3A%24AJ%2414%2C0)%2CINDEX(RA%5BFLOP%5D%2CMATCH(1%2C(RA%5BTYPE%5D%3D%24AA15)*(ABS((AVERAGEIF(RA%5BTYPE%5D%2C%24AA15%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%3DAGGREGATE(15%2C6%2C1%2F(RA%5BTYPE%5D%3D%24AA15)*(ABS(AVERAGEIF(RA%5BTYPE%5D%2C%24AA15%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%2C1))%2C0)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20B%20is%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(MATCH(%24AA21%2C%24AL%245%3A%24AL%2413%2C0)%2CINDEX(RA%5BFLOP%5D%2CMATCH(1%2C((INDEX(RA%2C0%2CMATCH(%24AA21%2CRA%5B%23Headers%5D%2C0)))%3DTRUE)*(ABS((AVERAGEIF((INDEX(RA%2C0%2CMATCH(%24AA21%2CRA%5B%23Headers%5D%2C0)))%2CTRUE%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%3DAGGREGATE(15%2C6%2C1%2F((INDEX(RA%2C0%2CMATCH(%24AA21%2CRA%5B%23Headers%5D%2C0)))%3DTRUE)*(ABS(AVERAGEIF((INDEX(RA%2C0%2CMATCH(%24AA21%2CRA%5B%23Headers%5D%2C0)))%2CTRUE%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%2C1))%2C0)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20combine%20then%20whichever%20comes%20second%20is%20returning%20%23NA%20but%20whcever%20is%20first%20works%20fine%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEx1%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(MATCH(%24AA15%2C%24AJ%245%3A%24AJ%2414%2C0)%2CINDEX(RA%5BFLOP%5D%2C%20MATCH(1%2C%20(RA%5BTYPE%5D%3D%24AA15)*(ABS((AVERAGEIF(RA%5BTYPE%5D%2C%24AA15%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%3D%20AGGREGATE(15%2C6%2C1%2F(RA%5BTYPE%5D%3D%24AA15)*(ABS(AVERAGEIF(RA%5BTYPE%5D%2C%24AA15%2CRA%5BEQUITY%5D)-%20RA%5BEQUITY%5D))%2C1))%2C0))%2CMATCH(%24AA15%2C%24AL%245%3A%24AL%2413%2C0)%2CINDEX(RA%5BFLOP%5D%2CMATCH(1%2C((INDEX(RA%2C0%2CMATCH(%24AA15%2CRA%5B%23Headers%5D%2C0)))%3DTRUE)*(ABS((AVERAGEIF((INDEX(RA%2C0%2CMATCH(%24AA15%2CRA%5B%23Headers%5D%2C0)))%2CTRUE%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%3DAGGREGATE(15%2C6%2C1%2F((INDEX(RA%2C0%2CMATCH(%24AA15%2CRA%5B%23Headers%5D%2C0)))%3DTRUE)*(ABS(AVERAGEIF((INDEX(RA%2C0%2CMATCH(%24AA15%2CRA%5B%23Headers%5D%2C0)))%2CTRUE%2CRA%5BEQUITY%5D)-%20RA%5BEQUITY%5D))%2C1))%2C0)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEx2%20reversed%20order%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(MATCH(%24AA15%2C%24AL%245%3A%24AL%2413%2C0)%2CINDEX(RA%5BFLOP%5D%2CMATCH(1%2C((INDEX(RA%2C0%2CMATCH(%24AA15%2CRA%5B%23Headers%5D%2C0)))%3DTRUE)*(ABS((AVERAGEIF((INDEX(RA%2C0%2CMATCH(%24AA15%2CRA%5B%23Headers%5D%2C0)))%2CTRUE%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%3DAGGREGATE(15%2C6%2C1%2F((INDEX(RA%2C0%2CMATCH(%24AA15%2CRA%5B%23Headers%5D%2C0)))%3DTRUE)*(ABS(AVERAGEIF((INDEX(RA%2C0%2CMATCH(%24AA15%2CRA%5B%23Headers%5D%2C0)))%2CTRUE%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%2C1))%2C0))%2CMATCH(%24AA15%2C%24AJ%245%3A%24AJ%2414%2C0)%2CINDEX(RA%5BFLOP%5D%2CMATCH(1%2C(RA%5BTYPE%5D%3D%24AA15)*(ABS((AVERAGEIF(RA%5BTYPE%5D%2C%24AA15%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%3DAGGREGATE(15%2C6%2C1%2F(RA%5BTYPE%5D%3D%24AA15)*(ABS(AVERAGEIF(RA%5BTYPE%5D%2C%24AA15%2CRA%5BEQUITY%5D)-RA%5BEQUITY%5D))%2C1))%2C0)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20also%20try%20using%20IF%20and%20then%20whichever%20one%20was%20in%20the%20if%20false%20criteria%20would%20return%20an%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-645033%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-653650%22%20slang%3D%22en-US%22%3ERe%3A%20Two%20formulas%20that%20work%20dont%20work%20when%20combined%20with%20IFs%20or%20IF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-653650%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F322032%22%20target%3D%22_blank%22%3E%40Dichotomy66%3C%2FA%3E%26nbsp%3B%2C%20if%20I%20understood%20correctly%20your%20formula%20looks%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFS(MATCH(%26lt%3B1%26gt%3B)%2C%26lt%3Bvalue%201%26gt%3B%2C%20MATCH(%26lt%3B2%26gt%3B)%2C%20%26lt%3Bvalue%202%26gt%3B)%3C%2FPRE%3E%0A%3CP%3EIMHO%2C%20entire%20logic%20is%20incorrect.%20If%20you%20have%20no%20first%20match%2C%20MATCH(%26lt%3B1%26gt%3B)%20returns%20%23N%2FA%20and%20entire%20formula%20also%20returns%20%23N%2FA.%20If%20first%20matches%2C%20you%20calculate%20and%20return%20value%201.%20You%20may%20go%20on%20second%20match%20if%20only%20first%20one%20returns%20zero%2C%20but%20it%20can't%20return%20zero%20position.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20check%20if%20MATCH%20finds%20something%20or%20not%2C%20use%20it%20in%20form%3C%2FP%3E%0A%3CPRE%3E%3DIFS(ISNUMBER(MATCH(%26lt%3B1%26gt%3B))%2C%26lt%3Bvalue%201%26gt%3B%2C%20ISNUMBER(MATCH(%26lt%3B2%26gt%3B))%2C%20%26lt%3Bvalue%202%26gt%3B)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Dichotomy66
Contributor

@Sergei Baklan 

Formula A is

=IFS(MATCH($AA15,$AJ$5:$AJ$14,0),INDEX(RA[FLOP],MATCH(1,(RA[TYPE]=$AA15)*(ABS((AVERAGEIF(RA[TYPE],$AA15,RA[EQUITY])-RA[EQUITY]))=AGGREGATE(15,6,1/(RA[TYPE]=$AA15)*(ABS(AVERAGEIF(RA[TYPE],$AA15,RA[EQUITY])-RA[EQUITY])),1)),0)))

 

Formula B is

 

=IFS(MATCH($AA21,$AL$5:$AL$13,0),INDEX(RA[FLOP],MATCH(1,((INDEX(RA,0,MATCH($AA21,RA[#Headers],0)))=TRUE)*(ABS((AVERAGEIF((INDEX(RA,0,MATCH($AA21,RA[#Headers],0))),TRUE,RA[EQUITY])-RA[EQUITY]))=AGGREGATE(15,6,1/((INDEX(RA,0,MATCH($AA21,RA[#Headers],0)))=TRUE)*(ABS(AVERAGEIF((INDEX(RA,0,MATCH($AA21,RA[#Headers],0))),TRUE,RA[EQUITY])-RA[EQUITY])),1)),0)))

 

When I combine then whichever comes second is returning #NA but whcever is first works fine

 

Ex1 

=IFS(MATCH($AA15,$AJ$5:$AJ$14,0),INDEX(RA[FLOP], MATCH(1, (RA[TYPE]=$AA15)*(ABS((AVERAGEIF(RA[TYPE],$AA15,RA[EQUITY])-RA[EQUITY]))= AGGREGATE(15,6,1/(RA[TYPE]=$AA15)*(ABS(AVERAGEIF(RA[TYPE],$AA15,RA[EQUITY])- RA[EQUITY])),1)),0)),MATCH($AA15,$AL$5:$AL$13,0),INDEX(RA[FLOP],MATCH(1,((INDEX(RA,0,MATCH($AA15,RA[#Headers],0)))=TRUE)*(ABS((AVERAGEIF((INDEX(RA,0,MATCH($AA15,RA[#Headers],0))),TRUE,RA[EQUITY])-RA[EQUITY]))=AGGREGATE(15,6,1/((INDEX(RA,0,MATCH($AA15,RA[#Headers],0)))=TRUE)*(ABS(AVERAGEIF((INDEX(RA,0,MATCH($AA15,RA[#Headers],0))),TRUE,RA[EQUITY])- RA[EQUITY])),1)),0)))

 

Ex2 reversed order

 

=IFS(MATCH($AA15,$AL$5:$AL$13,0),INDEX(RA[FLOP],MATCH(1,((INDEX(RA,0,MATCH($AA15,RA[#Headers],0)))=TRUE)*(ABS((AVERAGEIF((INDEX(RA,0,MATCH($AA15,RA[#Headers],0))),TRUE,RA[EQUITY])-RA[EQUITY]))=AGGREGATE(15,6,1/((INDEX(RA,0,MATCH($AA15,RA[#Headers],0)))=TRUE)*(ABS(AVERAGEIF((INDEX(RA,0,MATCH($AA15,RA[#Headers],0))),TRUE,RA[EQUITY])-RA[EQUITY])),1)),0)),MATCH($AA15,$AJ$5:$AJ$14,0),INDEX(RA[FLOP],MATCH(1,(RA[TYPE]=$AA15)*(ABS((AVERAGEIF(RA[TYPE],$AA15,RA[EQUITY])-RA[EQUITY]))=AGGREGATE(15,6,1/(RA[TYPE]=$AA15)*(ABS(AVERAGEIF(RA[TYPE],$AA15,RA[EQUITY])-RA[EQUITY])),1)),0)))

 

 

I did also try using IF and then whichever one was in the if false criteria would return an error

 

1 Reply

@Dichotomy66 , if I understood correctly your formula looks like

=IFS(MATCH(<1>),<value 1>, MATCH(<2>), <value 2>)

IMHO, entire logic is incorrect. If you have no first match, MATCH(<1>) returns #N/A and entire formula also returns #N/A. If first matches, you calculate and return value 1. You may go on second match if only first one returns zero, but it can't return zero position.

If you want to check if MATCH finds something or not, use it in form

=IFS(ISNUMBER(MATCH(<1>)),<value 1>, ISNUMBER(MATCH(<2>)), <value 2>)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies