Forum Discussion
Solved
vlookup for a vertical MAX returning name
hi, CUSTOMER CODE SALES SALES ADVISOR 123456 3400000 CARLOTTA 123456 300000 MERCO 123456 60000 CATERINA 123456 60000 CATERINA 123456 23500 MATTEO 123456 23500 ...
With the customer code in E2, the maximum sales amount in F1 is given by
=MAXIFS($B$2:$B$11,$A$2:$A$11,E2)
and the corresponding sales advisor in G1 is given by
=INDEX($C$2:$C$11,MATCH(1,INDEX(($A$2:$A$11=E2)*($B$2:$B$11=F2),,),0))
These formulas can be filled down, if required.
With the customer code in E2, the maximum sales amount in F1 is given by
=MAXIFS($B$2:$B$11,$A$2:$A$11,E2)
and the corresponding sales advisor in G1 is given by
=INDEX($C$2:$C$11,MATCH(1,INDEX(($A$2:$A$11=E2)*($B$2:$B$11=F2),,),0))
These formulas can be filled down, if required.
