SOLVED

New 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 MATTEO 987654 3436 CATERINA 987654 223344 MERCO 987654 4567 CARLOTTA 987654 3456 MATTEO

hi everyone,

Given the 'CUSTOMER CODE' with multiple 'SALES' I would like to return the 'SALES ADVISOR' with the best/max 'SALES' value.

i.e. given '987654' -----> 'MERCO'

I tried different vlookup with no success.

Matteo

8 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

Re: vlookup for a vertical MAX returning name

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.

Re: vlookup for a vertical MAX returning name

Thank you very much!!!!

Re: vlookup for a vertical MAX returning name

@Hans Vogelaar 's solution is probably simpler!

Re: vlookup for a vertical MAX returning name

@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.

Re: vlookup for a vertical MAX returning name

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!!!!

Re: vlookup for a vertical MAX returning name

@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.

Re: vlookup for a vertical MAX returning name

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",