How to use MOD as a criteria in MAXIFS formula or any other solution to my problem

0 cat
2 elephant
7 doll
8 apple
9 ball

My Data is in above form in excel.
I want to extract the the figure corresponding to maximum value in column 1 which is also divisible by 2 which is apple as 8 is divisible by 2 and max value.
My data is lot more than this but I want a formula which can cater to this need.

Array
=MAX(IF(MOD(A1:A5,2)=0,A1:A5))
non array
=AGGREGATE(14,6,A1:A5/(MOD(A1:A5,2)=0),1)

Thank you. I wanted to have corresponding value so I used VLOOKUP along with MAX IF MOD.

Originally Posted by Glenn Kennedy

I suspect that Govind0186 wants the value from column B... in which case modify the second formula above:

=VLOOKUP(AGGREGATE(14,6,($A$2:$A$6)/(MOD($A$2:$A$6,2)=0),1),$A:$B,2,FALSE)

and just set using ENTER

Thank you Glenn. Really helpful

Another option, if you have the dynamic array functions.
=INDEX(SORT(FILTER(A2:B6,MOD(A2:A6,2)=0),1,-1),1,2)

Hi,

if sorted:

=XLOOKUP(-1,-ISEVEN(-A2:A6),B2:B6,,,-1)

for sorted and old excel version
array formula
=VLOOKUP(1=1,CHOOSE({1,2},MOD(A2:A6,2)=0,B2:B6),2)
or
=VLOOKUP(1=1,IF({1,0},MOD(A2:A6;2)=0,B2:B6),2)

Originally Posted by Glenn Kennedy

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Sure. I ll do that