| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching and returning only one cell | Excel Discussion (Misc queries) | |||
| Matching Three columns and returning a value | Excel Discussion (Misc queries) | |||
| Matching & Returning values | Excel Discussion (Misc queries) | |||
| using a formula inside a sting of text | Excel Discussion (Misc queries) | |||
| Returning a Value by Matching Two Columns of Data | Excel Worksheet Functions | |||
Linear Mode
