Salesman with highest billing amount

[Excelmania21]

Salesman Month Amount
A November 1234
B November 345
A september 34
C November 456
B December 765
D November 766
E November 988
C January 544


I want to retrieve name of the Salesman with highest bill amount in a easy formula in month of november- without using combination of
1.Maxif Index & Match
2. Large & Filter Xlookup
3. Sumproduct & Max Index & Xmatch
4. Index, sort, filter

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Month] = "November")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Amount", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Added Index", each ([Index] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Index"})
in
    #"Removed Columns"

 

[Fluff]

What's wrong with the formula you got in your previous thread?

 

[Excelmania21]

What's wrong with the formula you got in your previous thread?

Nothing is wrong, the task is to find another method. This time we are looking for individual highest bill not cummulative for each salesman

 

[Excelmania21]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Month] = "November")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Amount", Order.Descending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Filtered Rows1" = Table.SelectRows(#"Added Index", each ([Index] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Index"})
in
    #"Removed Columns"

I want simple excel formula

 

[Fluff]

In that case, how about

=TAKE(SORT(FILTER(A2:C5000,B2:B5000="november"),3,-1),1,1)

 

[Excelmania21]

In that case, how about

=TAKE(SORT(FILTER(A2:C5000,B2:B5000="november"),3,-1),1,1)

Not working, is there any othe formula combination to use.

 

[Fluff]

In what way?

 

[Excelmania21]

In what way?

Through Aggregate?

 

[alansidman]

I want simple excel formula

why?