Returning comlumn hearder uppon matching a text sting in a row

[Martin]

Good morning,

I would like to have a formula that would enable me to receive back the
column header uppon matching a text criteria within a row.

Example:
A B C D E
1 - ROLE1 - ROLE2 - ROLE3 - ROLE 4 - LEADER (R)
2 - R A C I ROLE1
3 - I C A R ROLE4

In other word, cell E should contain a formula that search for the "R" in
A22 and return the column header (A1) so I can display the label
automatically and update it.

I made it work with "IF ("R",A2,A1,IF("R",B2,B1,UNAVAILBLE))" but I can not
embbed more than 8 x IF within a single formula and I have over 20 columns to
manage.

I have also made it work with SEARCH but if there is instance of letters
after the matching criteria, it gives me the last column containing a letter
(vice versa with numbers). I can not put columns in alphabetical order which
might be the reason why I have this behavior. Putting them in alphabetical
order is not a viable option anyway....

Thanks a lot in advance for your help
Martin

 

[Martin]

Slight correction:

The formula I made worked is the following but it is limited to 8 IF in the
formula and I have a need for at least 20 comlumns.

=IF(A2="R";$A$1;IF(B2="R";$B$2;UNAVAILABLE))...

Thanks again
Martin

 

[Mike H]

Martin,

Use this in E2 and drag down

=INDEX($A$1:$D$1,MATCH("x",A22,0))

In practice you would use a cell reference for the match value

=INDEX($A$1:$D$1,MATCH(H1,A22,0))

Mike

 

[Roger Govier]

Hi Martin

Try
=INDEX($A$1:$D$1,MATCH("R",A22,0))

or maybe for your location
=INDEX($A$1:$D$1;MATCH("R";A22;0))

--
Regards
Roger Govier

Good morning,

I would like to have a formula that would enable me to receive back the
column header uppon matching a text criteria within a row.

Example:
A B C D E
1 - ROLE1 - ROLE2 - ROLE3 - ROLE 4 - LEADER (R)
2 - R A C I ROLE1
3 - I C A R ROLE4

In other word, cell E should contain a formula that search for the "R" in
A22 and return the column header (A1) so I can display the label
automatically and update it.

I made it work with "IF ("R",A2,A1,IF("R",B2,B1,UNAVAILBLE))" but I can
not
embbed more than 8 x IF within a single formula and I have over 20 columns
to
manage.

I have also made it work with SEARCH but if there is instance of letters
after the matching criteria, it gives me the last column containing a
letter
(vice versa with numbers). I can not put columns in alphabetical order
which
might be the reason why I have this behavior. Putting them in
alphabetical
order is not a viable option anyway....

Thanks a lot in advance for your help
Martin





__________ Information from ESET Smart Security, version of virus
signature database 4527 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4527 (20091020) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Martin]

Great, worked perfectly fine !

Thanks a lot !
Martin