Trying to use =AGGREGATE to find the highest number

[tom hatten]

Hello.
I'm trying to search columns I and J to find the highest number. Also the same for columns S and T. I want the highest number found in columns I and J to go in cell AC4 and the highest number found in columns S and T to go in cell AD4.
Thank you

[PaulM100]

something like this?

=MAX(INDEX(($B$4:$B$27=$W4)*$I$4:$J$27,0),INDEX(($B$4:$B$27=$X4)*$I$4:$J$27,0))

[Glenn Kennedy]

or...

=AGGREGATE(4,6,$I$4:$J$27) and

=AGGREGATE(4,6,$S$4:$T$27)

[Jonmo1]

Try this for AC4
=AGGREGATE(14,6,((B4:B27=W4)+(B4:B27=X4))*I4:J27,1)

for AD4
=AGGREGATE(14,6,((B4:B27=W4)+(B4:B27=X4))*S4:T27,1)

[tom hatten]

Sorry, it seems I didn't explain it correctly. I cell AC4 I want the highest number from columns I and J for Barnsley (1.21). And in cell AD4 I want the highest number from column S and T for Fulham (1.64).
Thank you for your help.

[Glenn Kennedy]

=AGGREGATE(14,6,$I$4:$J$27/($B$4:$B$27=W4),1) and

=AGGREGATE(14,6,$S$4:$T$27/($B$4:$B$27=X4),1)

That's the second time you've not described that requirement...

[tom hatten]

Yes, I'm sorry. It sounds right when I make the post but realise it's not when people post soultions
Thank you.

[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'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.