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.
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.
As variant
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group( Source, {"CUSTOMER CODE"}, {{"Count", each Table.Sort(_,{{"SALES", Order.Descending}}){0} }} ), #"Expanded Count" = Table.ExpandRecordColumn( #"Grouped Rows", "Count", {"SALES", "SALES ADVISOR"}, {"SALES", "SALES ADVISOR"} ) in #"Expanded Count"- 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!!!!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.
