Returning comlumn hearder uppon matching a text sting in a row
 

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
A2:D2 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

Returning comlumn hearder uppon matching a text sting in a row
 

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

"Martin" wrote:

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
A2:D2 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

Returning comlumn hearder uppon matching a text sting in a row
 

Martin,

Use this in E2 and drag down

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

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

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

Mike

"Martin" wrote:

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

"Martin" wrote:

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
A2:D2 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

Returning comlumn hearder uppon matching a text sting in a row
 

Hi Martin

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

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

--
Regards
Roger Govier

"Martin" wrote in message
...
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
A2:D2 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

Returning comlumn hearder uppon matching a text sting in a row
 

Great, worked perfectly fine !

Thanks a lot !
Martin

"Mike H" wrote:

Martin,

Use this in E2 and drag down

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

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

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

Mike

"Martin" wrote:

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

"Martin" wrote:

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
A2:D2 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