export the header name for column if find a specific value

[Afarag]

hello there,

please i need help in a function that get the header name if the cell value is greater than "0:00:00"

as per the mentioned pic at cell ("U2") the value is greater than "0:00:00" so i got the header name at "Comment" column , i need to get this comment via function,
then we can find for one "Login ID" more than one activity column greater than "0:00:00", need to CONCATENATE the 2 headers

thanks a lot,

 

[Deepak]

Image is not visible.

 

[Afarag]

 

[Deepak]

U can get 1st query by lookup but second seems easy with VBA.

 

[Deepak]

Check this in AC2

=IFERROR(IF(COUNTIF(Table1[[#This Row],[Planned sick]:[Vacation]],">0")>1,INDEX(Table1[[#Headers],[Planned sick]:[Vacation]],1,MATCH(TRUE,Table1[[#This Row],[Planned sick]:[Vacation]]>0,0))&","&LOOKUP(1,0.1/Table1[[#This Row],[Planned sick]:[Vacation]],Table1[[#Headers],[Planned sick]:[Vacation]]),LOOKUP(1,0.1/Table1[[#This Row],[Planned sick]:[Vacation]],Table1[[#Headers],[Planned sick]:[Vacation]])),"??")

 

[Deepak]

Its an array formula so Ctrl+Shift+Enter

 

[Deepak]

Without table it will be like!

CSE

=IFERROR(IF(COUNTIF(Sheet1!$U2:$AB2,">0")>1,INDEX(Sheet1!$U$1:$AB$1,1,MATCH(TRUE,Sheet1!$U2:$AB2>0,0))&","&LOOKUP(1,0.1/Sheet1!$U2:$AB2,Sheet1!$U$1:$AB$1),LOOKUP(1,0.1/Sheet1!$U2:$AB2,Sheet1!$U$1:$AB$1)),"??")