Return primary value based on hierarchy

Copper Contributor

I have a list of names with groups they can belong to (one column per group). Some individuals belong to many groups but in the end, I need find their primary group, based on a hierarchy. For instance, if the person is a Board member and a donor, the "Board" group take precedence on "Donor". Is there a way to do this without embedding tons of "IF" statements? There are 18 potential groups, in decreasing priority from the left to the right.  Thanks. 




3 Replies
In Excel 365 you can use:


This does the same as @mtarler except it runs through each row and it returns a group name for the numeric values as well as text.

= BYROW(groups,
    LAMBDA(g, XLOOKUP(TRUE, g<>"", groupNames,""))

The other thing is that BYROW / LAMBDA is specific to Excel 365, as is every formula I write. 

nice catch on the number thing and clever use of TRUE, g<>"" to fix that.
what @Elva_Tanguerre needs to know is that in your formula "groups" and "groupNames" are named ranges. They can either replace those name with the range or better yet highlight those ranges and type those names into the box to the left of the formula box to name those ranges accordingly (or highlight and right click and select Define Name, or use the Names Manager)