Hi, I’m trying to match the names to the numbers.
I got the C7:C11 using the SORT(TRANSPOSE(B3:F3,2,FALSE)
Using INDEX() and MATCH() at B7:B11, I want to get the names that reflect the numbers but its showing me duplicated returns. As there is 2 of "2", the name reflected will show up as "B" twice.
The index and match function I’m using :
=INDEX($B$1:$F$1,MATCH(C7,$B$3:$F$3,0))
I’m fairly new to this so I’ve no idea how to go about doing this.
>Solution :
Try this way in Google Sheets, it should work,
• Formula used in cell D7
=INDEX(FILTER($B$1:$F$1,C7=$B$3:$F$3),COUNTIF($C$7:C7,C7))
And if you are using in Excel then either of the ways based on Excel Version
• Formula used in cell D7 –> Applicable to Excel 2021/MS365
=INDEX(FILTER($B$1:$F$1,$C7=$B$3:$F$3),COUNTIF($C$7:C7,C7))
• Formula used in cell E7 –> Applicable to All Excel Versions Except Excel 2007
=INDEX($B$1:$F$1,AGGREGATE(15,6,(COLUMN($B$3:$F$3)-COLUMN($B$3)+1)/($C7=$B$3:$F$3),COUNTIF($C$7:C7,C7)))