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 27, 2022

barank3 

=INDEX(Main!$A:$A,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"))),(Main!$AY:$AY),0))

 

This formula returns the expected result in my sheet.

 

barank3's avatar
barank3
Copper Contributor
Oct 28, 2022
Thanks for replying. With pure data for column AY of course it works, I had that working before I posted. My problem is I have a value of 59 in column AY for Alison AND Fred from column D. Then it just gets the first one for column A(does not get the max of 59 per person). The problem is that your (Main!$AY:$AY) in the 2nd part of the MATCH is not filtered so the max and filter produces the value of 59 and then it matches on the first occurrence, I need it to match on the max for a given person and I am stuck.
  • 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