Home > Enterprise >  Excel : Display Column name from another sheet if its written as 'Y'
Excel : Display Column name from another sheet if its written as 'Y'

Time:04-28

I am trying to modify this. Sheet 1 hasName and category column and sheet 2 has Name and category names as column i want to display as following way i tried Vlookup and match however no luck.

Output should be like this

Another sheet from where have to match values

CodePudding user response:

You may try-

=INDEX($G$3:$J$3,1,MATCH("y",INDEX(TRANSPOSE($G$4:$J$8),,MATCH(A2,$F$4:$F$8,0)),0))

Edit: See below updated formula

=FILTER($G$3:$J$3,MMULT(SEQUENCE(1,ROWS($G$4:$J$8),1,0),($G$4:$J$8="y")*($F$4:$F$8=A2)))

If you want to show result in same cell by comma separated then try-

=TEXTJOIN(", ",TRUE, FILTER($G$3:$J$3,MMULT(SEQUENCE(1,ROWS($G$4:$J$8),1,0),($G$4:$J$8="y")*($F$4:$F$8=A2))))

enter image description here

  • Related