Forum Discussion
MacTeo84
May 02, 2021Copper Contributor
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 ...
- May 02, 2021
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.
ā
Riny_van_Eekelen
May 02, 2021Platinum Contributor
MacTeo84 And one more. This time with Power Query, as long as you are not on a Mac.
Add sales data to the blue table and refresh the green one to update the summary.
File attached.
MacTeo84
May 02, 2021Copper Contributor
Irony, of course I'm on Mac... :), but I'll try to apply it on the PC I have at work!!!
Thank you very much!!!!
Thank you very much!!!!
- Riny_van_EekelenMay 02, 2021Platinum Contributor
MacTeo84 Actually, you can add data to the blue table and refresh the green one in this file on a Mac, but you can't edit the queries. You can't even look at them, which makes it quite useless.