Reputation: 3
i need to get multiple column names (header) in table associated with particular value in to a cell
as i explained, i need to get the heading names corresponding to value "n" to column E.
=INDEX((A$1:D$1),MATCH("n",A2:D2,0))
here. but it only give one column name.
i am open to vba scripts also. but i think it doesn't need vba. just improve the the above formula, may be. i tried and failed. any help. thank you guys
Upvotes: 0
Views: 693
Reputation: 662
if you are really "open" to vba, I'll use one simple UDF like:
Function HeatherNames(rg As Range, rf As String) As String
For Each cell In rg
If cell = rf Then HeatherNames = HeatherNames & Cells(1, cell.Column).Value & "-"
Next cell
HeatherNames = Left(HeatherNames, Len(HeatherNames) - 1)
End Function
you can use it in the column E `=HeatherNames(A2:D2;"n") now you can select the arg.1 (range) and type (or referring to another cell) the arg.2
Upvotes: 1
Reputation: 7762
Assuming you have Excel 2010 or later, in E2:
=IF(COLUMNS($A:A)>COUNTIF($A2:$D2,"n"),"",INDEX($1:$1,AGGREGATE(15,6,COLUMN($A2:$D2)/($A2:$D2="n"),COLUMNS($A:A))))
Copy to the right and down as required.
It would actually be slightly more efficient (and certainly if your dataset in reality is quite large) to have the initial IF
clause held within its own cell, such that it is calculated for each row only once, rather than for each instance of the formula within that row. So a better set-up would be, in E2:
=COUNTIF($A2:$D2,"n")
copied down. Then, in F2:
=IF(COLUMNS($A:A)>$E2,"",INDEX($1:$1,AGGREGATE(15,6,COLUMN($A2:$D2)/($A2:$D2="n"),COLUMNS($A:A))))
copied to the right and down again.
Regards
Upvotes: 0