Feb 01 2021 10:32 AM
Hi!
I have the below table, and I would like on the last column: to return the column name of the cell with the highest number. If the highest number is in more than one column, it should show the list of those columns, like below:
Name | Playstation | Xbox | Switch | Preferred Console |
James Holden | 5 | 5 | 1 | Playstation, Xbox |
Naomi Nagata | 3 | 5 | 1 | Xbox |
Julie Mao | 0 | 0 | 0 | Playstation, Xbox, Switch |
Feb 01 2021 11:25 AM
SolutionIt depends on which version of Excel you are, as variant
=TEXTJOIN(", ",1,FILTER($B$1:$D$1,B2:D2=MAX(B2:D2)))
Feb 01 2021 11:31 AM
If you have Excel in Office 2019 or Microsoft 365, enter the following formula in E2 and confirm it with Ctrl+Shift+Enter to turn it into an array formula:
=TEXTJOIN(", ",TRUE,IF(B2:D2=MAX(B2:D2),$B$1:$D$1,""))
Fill or copy down.
Feb 01 2021 11:25 AM
SolutionIt depends on which version of Excel you are, as variant
=TEXTJOIN(", ",1,FILTER($B$1:$D$1,B2:D2=MAX(B2:D2)))