CODEWITHSUNDEEP
excelvba
Harun24hr
Harun24hr

Reputation: 36880

How to get header row data related to left column name (whose data are not empty)

Good day to all! My sample data looks like below screenshot. So, when I will select Harun in J2 cell then my expected result is xxx, zzz, ppp. My logic to get result as Harun has value in xxx and zzz and ppp column. With the following array formula I can do that but it is not dynamic. Row (array for index function) is hard-coded here. So, can we make it dynamic so that I can get the all header item when I select any name in J2 cell? Appreciate your help.

=INDEX($C$2:$G$2,,SMALL(IF($C$3:$G$3<>"",COLUMN($C$3:$G$3),""),ROWS($B$3:$B3))-2)

Sample Data Screenshot
enter image description here

Only excel formula is preferable. If not possible then we can go with VBA.

Upvotes: 0

Views: 87

Answers (1)

Sixthsense
Sixthsense

Reputation: 1975

In J3 Cell

=IFERROR(INDEX($C$2:$G$2,,SMALL(IF(OFFSET($B$2,MATCH(J$2,$B$3:$B$8,0),1,,5)<>"",COLUMN(OFFSET($B$2,MATCH(J$2,$B$3:$B$8,0),1,,5))-2,""),ROWS($1:1))),"")

Drag the formula down and right

Upvotes: 2

Related Questions