Forum Discussion

MacTeo84's avatar
MacTeo84
Copper Contributor
May 02, 2021
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 ...
  • HansVogelaar's avatar
    May 02, 2021

    MacTeo84 

    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.

     

    ā€ƒ

Resources