Forum Discussion

barank3's avatar
barank3
Copper Contributor
Oct 27, 2022
Solved

INDEX MATCH with MAX and FILTER erroring out with #N/A

Getting #N/A error on the below. The below evaluates to =INDEX(Main!$A$A,MATCH(59,59,0) and gives #N/A the MATCH is using MAX and FILTER. The 59 is the correct MAX value per person   I have to fil...
excel
  • barank3's avatar
    barank3
    Oct 28, 2022
    Thanks SO much for your reply. I took your formula and got calc errors for empty strings from the filters. I kept your basic framework and changed the filters and it worked fine with what seems the same logic that you had, just different order for each equality. This was what I ended with:
    =INDEX(FILTER(Main!$A:$A,("GSO - GDPR - FY23"=Main!$F:$F)*("Allison"=Main!$D:$D)*("No"=Main!$AI:$AI)*("In Scope"=Main!$H:$H)),
    MATCH(MAX(FILTER(Main!$AY:$AY,("GSO - GDPR - FY23"=Main!$F:$F)*("Allison"=Main!$D:$D)*("No"=Main!$AI:$AI)*("In Scope"=Main!$H:$H))),
    FILTER(Main!$AY:$AY,("GSO - GDPR - FY23"=Main!$F:$F)*("Allison"=Main!$D:$D)*("No"=Main!$AI:$AI)*("In Scope"=Main!$H:$H)),0))
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 28, 2022

barank3 

Sorry i was wrong. Actually MATCH has to compare MAX(FILTER(Main!$AY:$AY,.... with FILTER(Main!$AY:$AY,.... . The row from MATCH then returns the result from INDEX(FILTER(Main!$A:$A,.... .

 

=INDEX(FILTER(Main!$A:$A,(Main!$F:$F="GSO-GDPR-FY23")*(Main!$D:$D="Allison")*(Main!$AI:$AI="No")*(Main!$H:$H="In Scope")),MATCH(MAX(FILTER(Main!$AY:$AY,(Main!$F:$F="GSO-GDPR-FY23")*(Main!$D:$D="Allison")*(Main!$AI:$AI="No")*(Main!$H:$H="In Scope"))),FILTER(Main!$AY:$AY,(Main!$F:$F="GSO-GDPR-FY23")*(Main!$D:$D="Allison")*(Main!$AI:$AI="No")*(Main!$H:$H="In Scope")),0))

 

 

 

barank3's avatar
barank3
Copper Contributor
Oct 28, 2022
Thanks SO much for your reply. I took your formula and got calc errors for empty strings from the filters. I kept your basic framework and changed the filters and it worked fine with what seems the same logic that you had, just different order for each equality. This was what I ended with:
=INDEX(FILTER(Main!$A:$A,("GSO - GDPR - FY23"=Main!$F:$F)*("Allison"=Main!$D:$D)*("No"=Main!$AI:$AI)*("In Scope"=Main!$H:$H)),
MATCH(MAX(FILTER(Main!$AY:$AY,("GSO - GDPR - FY23"=Main!$F:$F)*("Allison"=Main!$D:$D)*("No"=Main!$AI:$AI)*("In Scope"=Main!$H:$H))),
FILTER(Main!$AY:$AY,("GSO - GDPR - FY23"=Main!$F:$F)*("Allison"=Main!$D:$D)*("No"=Main!$AI:$AI)*("In Scope"=Main!$H:$H)),0))

Resources