# maxif equivalent function

1. ## maxif equivalent function

attached is a sample of a problem i am working on.

i have a range of numbers that i have ranked in a tier system (column d). the process behind that is not important, but for each row, i need to return the highest value for the range of the entire tier data set for the values listed in column a.

so in this example: the 2 rows in tier 1 would each return 39 (the highest value of those two) in column e. the rows in tier 2 would each return 14 in column e, and so forth for the rest of the tiers.

can anyone point me in the right direction? these values will not often be ordered ascending like this  Register To Reply

2. ## Re: maxif equivalent function

Try: =MAX(IF(\$D\$2:\$D\$60=D2,\$A\$2:\$A\$60))

In E2 confirmed cith Ctrl+Shift+Enter as it's an array formula and copied down.

Dom  Register To Reply

3. ## Re: maxif equivalent function

this returns 61.99 (the max for the ENTIRE spreadsheet) for each individual cell unfortunately. this at least gives me a start though, so thank you for your time  Register To Reply

4. ## Re: maxif equivalent function

Did you confirm the formula with Ctrl+Sift+Enter rather than just Enter?

Dom  Register To Reply

5. ## Re: maxif equivalent function

oh thank you that was my mistake. thank you very much this works perfectly  Register To Reply