Forum Discussion
Matching values and return max of adjacent column
I'm looking to create a formula in Column J that would match values in Column K and return the largest corresponding number in Column H for the matched values.
In the attached, K2 and K3 match, so I would want to formula to return H3, which is the largest corresponding number.
Help?
2 Replies
That could be
=AGGREGATE(14,6,1/(COUNTIF($K$2:$K$10,$K$2:$K$10)>1)*$H$2:$H$10,1)
Please see in attached
Hi TmM-1
Not sure I understand the spreadsheet but I'll take a guess and make an assumption that matching values will be adjacent to each other.
If yes, try placing this formula into cell J2 and drag downwards for the cells:
=IF(I2>1, IF(H2="00", "",H2), "")
If any events are more than 1 (I assume this means matching based on the example you have provided), then the formula will next check if H is 00. If it is not then it will show H value (e.g. 03), else it will be blank as you don't want to see 00 in J, you want to see the higher number.
Let me know if this is what you are after?
Cheers
Damien
