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>)