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


2 Replies
best response confirmed by MyUsername1024 (Visitor)



You can try this formula with the data of the example.

category for smallest value of each row.JPG 


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.