SOLVED

vlookup for a vertical MAX returning name

%3CLINGO-SUB%20id%3D%22lingo-sub-2314702%22%20slang%3D%22en-US%22%3Evlookup%20for%20a%20vertical%20MAX%20returning%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314702%22%20slang%3D%22en-US%22%3E%3CP%3Ehi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22333%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22125%22%20height%3D%2221%22%3ECUSTOMER%20CODE%3C%2FTD%3E%3CTD%20width%3D%2287%22%3ESALES%3C%2FTD%3E%3CTD%20width%3D%22121%22%3ESALES%20ADVISOR%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E123456%3C%2FTD%3E%3CTD%3E3400000%3C%2FTD%3E%3CTD%3ECARLOTTA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E123456%3C%2FTD%3E%3CTD%3E300000%3C%2FTD%3E%3CTD%3EMERCO%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E123456%3C%2FTD%3E%3CTD%3E60000%3C%2FTD%3E%3CTD%3ECATERINA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E123456%3C%2FTD%3E%3CTD%3E60000%3C%2FTD%3E%3CTD%3ECATERINA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E123456%3C%2FTD%3E%3CTD%3E23500%3C%2FTD%3E%3CTD%3EMATTEO%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E123456%3C%2FTD%3E%3CTD%3E23500%3C%2FTD%3E%3CTD%3EMATTEO%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E987654%3C%2FTD%3E%3CTD%3E3436%3C%2FTD%3E%3CTD%3ECATERINA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E987654%3C%2FTD%3E%3CTD%3E223344%3C%2FTD%3E%3CTD%3EMERCO%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E987654%3C%2FTD%3E%3CTD%3E4567%3C%2FTD%3E%3CTD%3ECARLOTTA%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E987654%3C%2FTD%3E%3CTD%3E3456%3C%2FTD%3E%3CTD%3EMATTEO%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Ehi%20everyone%2C%26nbsp%3B%3C%2FP%3E%3CP%3EGiven%20the%20'CUSTOMER%20CODE'%20with%20multiple%20'SALES'%20I%20would%20like%20to%20return%20the%20'SALES%20ADVISOR'%20with%20the%20%3CSTRONG%3Ebest%2Fmax%3C%2FSTRONG%3E%20'SALES'%20value.%3C%2FP%3E%3CP%3Ei.e.%20given%20'987654'%20-----%26gt%3B%20'MERCO'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20different%20vlookup%20with%20no%20success.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMatteo%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2314702%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314790%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20for%20a%20vertical%20MAX%20returning%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314790%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042924%22%20target%3D%22_blank%22%3E%40MacTeo84%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20the%20customer%20code%20in%20E2%2C%20the%20maximum%20sales%20amount%20in%20F1%20is%20given%20by%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMAXIFS(%24B%242%3A%24B%2411%2C%24A%242%3A%24A%2411%2CE2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20the%20corresponding%20sales%20advisor%20in%20G1%20is%20given%20by%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24C%242%3A%24C%2411%2CMATCH(1%2CINDEX((%24A%242%3A%24A%2411%3DE2)*(%24B%242%3A%24B%2411%3DF2)%2C%2C)%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThese%20formulas%20can%20be%20filled%20down%2C%20if%20required.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0358.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277340i64BCD235848F606C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0358.png%22%20alt%3D%22S0358.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314791%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20for%20a%20vertical%20MAX%20returning%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314791%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314794%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20for%20a%20vertical%20MAX%20returning%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042924%22%20target%3D%22_blank%22%3E%40MacTeo84%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B's%20solution%20is%20probably%20simpler!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20787px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277341iF4241F31D7117B6C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2314827%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20for%20a%20vertical%20MAX%20returning%20name%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2314827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042924%22%20target%3D%22_blank%22%3E%40MacTeo84%3C%2FA%3E%26nbsp%3BAnd%20one%20more.%20This%20time%20with%20Power%20Query%2C%20as%20long%20as%20you%20are%20%3CSTRONG%3Enot%3C%2FSTRONG%3E%20on%20a%20Mac.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-05-02%20at%2012.28.26.png%22%20style%3D%22width%3A%20573px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F277342i603F540264EAF7CC%2Fimage-dimensions%2F573x141%3Fv%3Dv2%22%20width%3D%22573%22%20height%3D%22141%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-05-02%20at%2012.28.26.png%22%20alt%3D%22Screenshot%202021-05-02%20at%2012.28.26.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAdd%20sales%20data%20to%20the%20blue%20table%20and%20refresh%20the%20green%20one%20to%20update%20the%20summary.%3C%2FP%3E%3CP%3EFile%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

hi, 

 

CUSTOMER CODESALESSALES ADVISOR
1234563400000CARLOTTA
123456300000MERCO
12345660000CATERINA
12345660000CATERINA
12345623500MATTEO 
12345623500MATTEO 
9876543436CATERINA
987654223344MERCO
9876544567CARLOTTA
9876543456MATTEO 

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.

 

Thanks in advance

 

Matteo

 

 

8 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

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

 

S0358.png

@Hans Vogelaar 

 

Thank you very much!!!!

@MacTeo84 

@Hans Vogelaar 's solution is probably simpler!

image.png

@MacTeo84 And one more. This time with Power Query, as long as you are not on a Mac.

Screenshot 2021-05-02 at 12.28.26.png

Add sales data to the blue table and refresh the green one to update the summary.

File attached.

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.

@Riny_van_Eekelen 

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"