May 24 2019 12:14 PM - edited May 24 2019 12:16 PM
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
May 28 2019 01:34 PM
@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>)