Home

Matching values and return max of adjacent column

TmM-1
Occasional Visitor

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

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

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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies