If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with function, not sure which one
I don't want to use a macro...is it possible to do the following with
functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start. |
#2
|
|||
|
|||
Help with function, not sure which one
Assuming your data in A1:E3 and the person name in "one word"
Criteria in G1 In H1: =SUBSTITUTE(TRIM(INDEX(CONCATENATE(B1:B3," ",C1:C3," ",D13," ",E1:E3),MATCH(G1,A1:A3,)))," ",", ") "excelrookie" wrote: I don't want to use a macro...is it possible to do the following with functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start. |
#3
|
|||
|
|||
Help with function, not sure which one
Thank you. I have 90 rows and 12 columns so I will work on making the
formula longer, however when i tried the short version it only had 1 of the answers instead of two and it showed that one over and over? Any thoughts? Thanks "Teethless mama" wrote: Assuming your data in A1:E3 and the person name in "one word" Criteria in G1 In H1: =SUBSTITUTE(TRIM(INDEX(CONCATENATE(B1:B3," ",C1:C3," ",D13," ",E1:E3),MATCH(G1,A1:A3,)))," ",", ") "excelrookie" wrote: I don't want to use a macro...is it possible to do the following with functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start. |
#4
|
|||
|
|||
Help with function, not sure which one
Increase your column width
"excelrookie" wrote: Thank you. I have 90 rows and 12 columns so I will work on making the formula longer, however when i tried the short version it only had 1 of the answers instead of two and it showed that one over and over? Any thoughts? Thanks "Teethless mama" wrote: Assuming your data in A1:E3 and the person name in "one word" Criteria in G1 In H1: =SUBSTITUTE(TRIM(INDEX(CONCATENATE(B1:B3," ",C1:C3," ",D13," ",E1:E3),MATCH(G1,A1:A3,)))," ",", ") "excelrookie" wrote: I don't want to use a macro...is it possible to do the following with functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start. |
#5
|
|||
|
|||
Help with function, not sure which one
I increased the column width and that is how I saw that only 1 of the 2
correct answers was repeating over and over. Any other suggestions? You have gotten me the closest to the correct formula, it's just not working yet Thank you "Teethless mama" wrote: Increase your column width "excelrookie" wrote: Thank you. I have 90 rows and 12 columns so I will work on making the formula longer, however when i tried the short version it only had 1 of the answers instead of two and it showed that one over and over? Any thoughts? Thanks "Teethless mama" wrote: Assuming your data in A1:E3 and the person name in "one word" Criteria in G1 In H1: =SUBSTITUTE(TRIM(INDEX(CONCATENATE(B1:B3," ",C1:C3," ",D13," ",E1:E3),MATCH(G1,A1:A3,)))," ",", ") "excelrookie" wrote: I don't want to use a macro...is it possible to do the following with functions? New York Joe Tim Kim Jane San Diego Tim Jane St Louis Joe Tim Kim If I enter New York I want excel to return Joe, Tim, Kim, Jane in a cell by looking at the table above which will be in the same worksheet If I enter St Louis I want it to Return Joe, Tim, Kim in a cell by looking at the above etc. Thanks so much I am having a hard time deciding where to start. |
Thread Tools | |
Display Modes | |
|
|