Forum Discussion
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 filter both sides of the match as the results set MAX are only unique per person
Data is this:
Sheet Main
A AY F D H AI
Assetname1 59 GSO - GDPR-FY23 Allison In Scope No
Assetname2 25 GSO - GDPR-FY23 Allison In Scope No
Assetname3 59 GSO - GDPR-FY23 Fred In Scope No
=INDEX(Main!$A:$A,
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))),
MAX(FILTER(Main!$AY:$AY,("GSO - GDPR - FY23"=Main!$F:$F)*("Allison"=Main!$D:$D)*("No"=Main!$AI:$AI)*("In Scope"=Main!$H:$H)),0)))
and no, the below returns wrong values as there are dups in column AY, but not dups by a person
=INDEX(Main!$A:$A,
MATCH(
MAXIFS(Main!$AY:$AY,Main!$F:$F,"GSO - GDPR - FY23",Main!$D:$D,"Allison",Main!$AI:$AI,"<>Yes",Main!$H:$H,"In Scope"),Main!$AY:$AY,0))
If I could return a row number from the MAX(FILTER or MAXIFS I could get this to work
- 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))
4 Replies
=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.
- 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.
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))
