Ok, so I'm good on how Index/Match works. I've used it successfully in the past, understand the examples that I can find online, I'm good. Where I'm having a problem is trying to incorporate a Max function. Here's my simplified data:
<tbody>
</tbody>
Basically I need to search column A to match a customer number, and then search for the maximum sales volume and then return the appropriate class. So in the example I would search for Cust# 2, have it find that the max value is 200 and return the corresponding class which is Middle. I need some help because I can't get the Max portion to work.
This is the closest I've gotten
and this just returns a #REF!
Any help is appreciated since I'm currently doing this manually and I'm getting sick of it.
Cust | Class | Sales |
1 | Low | 100 |
2 | Middle | 200 |
2 | Low | 75 |
3 | High | 50 |
4 | None | 125 |
<tbody>
</tbody>
Basically I need to search column A to match a customer number, and then search for the maximum sales volume and then return the appropriate class. So in the example I would search for Cust# 2, have it find that the max value is 200 and return the corresponding class which is Middle. I need some help because I can't get the Max portion to work.
This is the closest I've gotten
Code:
=INDEX(B21:F1959,MATCH(J22,B21:B1959,0),3)
Code:
=INDEX(B20:F1958,MATCH(J21,B20:B1958,0),MATCH(MAX(F20:F1958),F20:F1958,0))
Any help is appreciated since I'm currently doing this manually and I'm getting sick of it.