Search code examples
arraysexcelif-statementexcel-formulamode

Excel - Modal value in list with IF function

I want to find the most common value in a list, matching the most common outcome by name.

I know it revolves around an index with mode and match function, with an IF function in there. But can't get it down.

Raw Data 

USER NAME       Items
James Ile       Flat White 
James Ile       Flat White 
Michael Pane    Latte 
Lily Wilk       Iced Drinks 
Louise Coset    Millionaire Shortbread 
Louise Wyli     Flat White 
Louise Wyli     Millionaire Shortbread 
Louise Wyli     English Breakfast 
Louise Cosett   Cheese Toastie 
Louise Wyli     Flat White
Louise Cosett   Pineapple Sunset 
James Court     Espresso

Desired Result 

USER    
James Ile        Flat white
Michael Pane     Latte
Lily Wilk        Iced Drinks
Louise Cosett    Cheese Toastie
Louise Wyli      Flat white
James Court      Espresso
Solution

  • Being a Excel Formula's enthusiast and considering that this question has not attracted many responses, I tried to get results by using a combination of Mode.Mult Function, Index and Match Functions.

    1. Mode.Mult is available from Excel 2010 onwards. Mode Function can be used for earlier versions but may give error if mode values have a tie.
    2. Mode.Mult Function Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data. For horizontal arrays, use TRANSPOSE(MODE.MULT(number1,number2,...)).This will return more than one result if there are multiple modes. Because this function returns an array of values, it must be entered as an array formula. Arguments can either be numbers or names, arrays, or references that contain numbers.If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.Mode.Mult Function
    3. By using IF conditions Mode.Mult function has been utilized for text strings.

    I have partially succeeded in getting desired results, in the sense if there are more than one occurrence of mode value, it will be indicated in the results. Notwithstanding this I am posting my answer with the hope that more experienced persons will guide and help in overcoming the shortcomings in my attempt.

    Results Array formula in C2 copied down

    =IFERROR(INDEX( $B$2:$B$13,INDEX(MODE.MULT(IF($A$2:$A$13=$A2,MATCH($B$2:$B$13,$B$2:$B$13,0))),ROWS($C2:C2))  ),"")

    confirmed with Ctrl+Shift+Enter, not just Enter.

    Results