Forum Discussion
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...
- 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))
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))
- 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))