Use Xlookup instead of index & match

[Mishoshow]

Hello Guys,

The excel sheet is too slow so is there is any way i can change this formula and use Xlookup instead of index & match ??

=IF(BDN$1=$BQ1212,$N1212,IF((IFERROR(INDEX($EW1212:$ADZ1212,MATCH(BDN$1,$EW$1:$ADZ$1,0)),0))=0,0,(INDEX($BDN1212:$CCQ1212,MATCH(@INDEX($BQ1212:$DD1212,MATCH(BDN$1,$BQ1212:$DD1212,0)-1),$BDN$1:$CCQ$1,0))+CCV1212-AED1212)))

 

[Anbuselvam]

=IF(BDN$1=$BQ1212, $N1212, IF((IFERROR(XLOOKUP(BDN$1, $EW$1:$ADZ$1, $EW1212:$ADZ1212, 0),0))=0, 0, (XLOOKUP(XLOOKUP(BDN$1, $BQ1212:$DD1212, $BDN1212:$CCQ1212, 0) + CCV1212 - AED1212, $BDN$1:$CCQ$1, $BDN1212:$CCQ1212, 0))))

Note that I made some assumptions about the column ranges used in the original formula, so please double-check if they are correct for your specific use case.

 

[Mishoshow]

=IF(BDN$1=$BQ1212, $N1212, IF((IFERROR(XLOOKUP(BDN$1, $EW$1:$ADZ$1, $EW1212:$ADZ1212, 0),0))=0, 0, (XLOOKUP(XLOOKUP(BDN$1, $BQ1212:$DD1212, $BDN1212:$CCQ1212, 0) + CCV1212 - AED1212, $BDN$1:$CCQ$1, $BDN1212:$CCQ1212, 0))))

Note that I made some assumptions about the column ranges used in the original formula, so please double-check if they are correct for your specific use case.

thanks for your effort ,, but it didn't gave me the same result

 

[Anbuselvam]

As I said modify the ranges properly. then post more information to solve it if you wish.