Forum Discussion

MyUsername1024's avatar
MyUsername1024
Copper Contributor
Mar 04, 2023
Solved

How do I pick the category of the smallest value in each row?

Hello, I'm working with a panel data like this. I want to pick the category name (in row 2) of the smallest value in each row. I know how to pick the smallest value of each row, but don't know how can I have the category name as the output in column AX. 

 

Thank you so much for helping! 

 

Device and OS platform: Windows 10

Excel product name and version number: 

Microsoft® Excel® 2016 MSO (Version 2301 Build 16.0.16026.20196) 64-bit

 

excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 04, 2023

    MyUsername1024 

    Increasingly my advice to users of legacy Excel is replace it as soon as you can.  In this case the formula is not so different and wouldn't justify the change,

     

    = BYROW(values, LAMBDA(v, XLOOKUP(MIN(v), v, Categories)))

     

    but the good thing is that the categories are returned as an array, so it is possible to build upon solution without spreading the calculation across ranges.  For example one could ask 'how many times does each category appear as the smallest and which occurs most frequently?'

     

    = LET(
    minCategory,  BYROW(values,   LAMBDA(v, XLOOKUP(MIN(v), v, Categories))),
    Occurences,   MAP(Categories, LAMBDA(c, SUM(SIGN(minCategory=c)))),
    mostFrequent, FILTER(Categories, Occurences=MAX(Occurences)),
    TEXTJOIN(", ",,mostFrequent)
  )

     

    The objective is to develop a solution in the most coherent and readable form possible.

Resources