Third Parameter in Array Formula to check for highest(max) occurence but only if the other conditions are true first

0
votes

Ok, this is the updated linkenter image description hereI have multiple criteria to look through in my arrayformula(index(match())). The first two are simple as they reference the row the formula is calculating on. The last conditional I have is to find the highest occurrence in a given range, but ONLY if the other conditions are met...something like a filtered maxifs..any ideas?

Here is my code in column P =iferror(ArrayFormula(index($F:$F,match(1,("Fee Taken"=$C:$C)*(H12=$H:$H)(maxifs($M:$M,$H:$H,H2,$C:$C,"Fee Taken"),0))),""))

The result that I would like is to return from column F if the name matches that rows name, the transaction type is "Fee Taken" from column C, and THEN if those conditions are true I want it to find the max value from column M based on those two criterias and return the column F value for that max value row..

Ive attached some pictues to show my data.

The last part of the Match function where I have the Maxifs equaling to eachother is where I am confused; my thoughts were to see if the maxifs for the item in Column "M" can be used as a criteria..but I do not think so....I only want the highest occurence F:F if both conditions are met and it is the highest value for both those criteria in column M..

Please let me know if you need anymore info..Thanks![enter image description here

1
google-sheetsgoogle-sheets-formula
Remove sensitive data and share your sheet. – basic
Done! Let me know if there is anything else. – yossup
Your sheet is not accessible. – basic
Ok I changed the settings to be able to view. – yossup

1 Answers

2
votes

Working formula will be:

=ArrayFormula(index($F:$F,match(1,--(M:M=(maxifs($M:$M,$H:$H,H2,$C:$C,"Fee Taken"))),0)))

enter image description here