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.





0
Utf
4/9/2010 4:27:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
1081 Views

Similar Articles

[PageSpeed] 6

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," ",D1:D3," 
",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.
> 
> 
> 
> 
> 
0
Utf
4/9/2010 5:31:01 PM
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," ",D1:D3," 
> ",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.
> > 
> > 
> > 
> > 
> > 
0
Utf
4/9/2010 6:10:03 PM
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," ",D1:D3," 
> > ",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.
> > > 
> > > 
> > > 
> > > 
> > > 
0
Utf
4/10/2010 2:25:01 AM
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," ",D1:D3," 
> > > ",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.
> > > > 
> > > > 
> > > > 
> > > > 
> > > > 
0
Utf
4/14/2010 3:31:02 PM
Reply:

Similar Artilces:

Function to encapsulate code block into a constant?
I writing more and more apps where the application generates an Excel spreadsheet and pushes various VBA routines into it. I seem to be spending too many man hours on the details (placement of vblf's, "_", quotes, and such when creating constants. I'm thinking I should be able to develop the routine in an Excel spreadsheet, test it... and then feed the routine to a function that returns a monster constant like the one below - even checking for excessive continuations and breaking it up into multiple constants as needed. For example, this: --------------------------------...

users can't login on one PC
I have 1 PC that was just upgraded to GP 10 SP 2 and now only the SA can login. When a regular user account tries to login they get the error: "The login failed. Attempt to login again or contact your system administrator" That same id is able to login on every other PC so I know it is not SQL DB security and the SA can login from this PC so i am pretty sure it is not the ODBC. Any help would be appreciated. Fliehigh OK So I figured it out this DSN was setup using the IP address of the SQl server and the rest where using the DNS name. I changed it and it worked great. Fli...

External Link-help!
Hi there, I am trying to get rid of an external link to my excel spreadsheet that I created. When I email the excel file to a coworker, the file asks to update links. But, I want to get rid of all the links so that when others open it, the message will not come up. Under the Edit menu, the Links option is not highlighted. Somehow, the external link got embedded into the file, and I need a solution to delete it out. My job is depending on this resolution! Thanks, Tina I could really use some help! Try Bill Manville's addin. It'll find those pesky ones. Findlink.zip ...

Question about moving Outlook 2003 from an old computer to a new one
Microsoft Office Assistance has the following article, which describes the details for moving Outlook 2000 to a new computer: http://office.microsoft.com/en-us/assistance/HA010549451033.aspx I was wondering what, if anything, has changed for Outlook 2003? I'm asking only becuase I can't find a similar article, and I'm going to need to do this later next week. Thanks! Larry Larry Kahm <lkahm@nospam_heliotropicsystems.com> wrote: > Microsoft Office Assistance has the following article, which > describes the details for moving Outlook 2000 to a new computer: &g...

!!HELP!! OWA ans Outlook do not synch
OK so I rebuilt my SBS 2003 server and used Exmerge to exmerge all my users mailboxes out. Successfully exmerged them back into Exchange. Had to recreate new profiles on the users workstations but all appears to be good. One slight problem. For my external users who use Citrix and log onto the Citrix server desktop they cannot use Outlook there. I do have a case open with Microsoft regarding this issue however my immediate issue to resolve is why I can log on as a user here in the office, open Outlook and all their mail is there. However when I use OWA the mailbox is incomplete. Does no...

forgot password?please help
I FORGOT MY PASSWORD ON MY MICROSOFT MONEY 2000 BUSINESS - PERSONAL I TRYED EVERY NAME AND NUMBER FOR 2 DAYS tsger@aol.com wrote: > I FORGOT MY PASSWORD ON MY MICROSOFT MONEY 2000 BUSINESS - > PERSONAL I TRYED EVERY NAME AND NUMBER FOR 2 DAYS That's got to be VERY FRUSTRATING! Not only that, but your CAPS LOCK KEY is stuck! I'm sorry for you! ...

Error when using RMS EDC Settle function
Hello. We run RMSsp2, and use PCCharge PaymntSvr EDC software/First Data processor. PCCharge settles fine, if trying from within RMS, receive error on: 1st attempt: "Invalid Merchant" box error appears. 2nd and subsequent attempts "Invalid Destination Zip" box error appears. Erro occurs withing few seconds of settle attempt, settle progress doesnt go beyond 1/10 of progress meter and this error pops up. Any ideas? Our EDC config is fine and all batches and items are ok in PCCharge. thanks luke ...

countif function with multiple criteria
Is it possible to have multiple criteria for a countif function? Thanks. Not COUNTIF, but SUMPRODCT will =SUMPRODUCT(--(rng1="value1"),--(rng2=num2)) rng1 and rng2 must be the same size, and cannot be complete columns. -- HTH RP (remove nothere from the email address if mailing direct) "Geoff" <geoffreynd@yahoo.com> wrote in message news:1123798529.834293.79080@g14g2000cwa.googlegroups.com... > Is it possible to have multiple criteria for a countif function? > Thanks. > ...

need a bit of help.....
I have generated a form that shows money amounts. yet i want two colums that separates the dollar amount from cents. I have for example colum *H* as the dollar amount and colum *I* as the cent amounts. Now i have tried Format Cell to set it up but it doesnt work. for example i tried the custom format for the dollar amount and it rounds it. and if there's an amount of 1108.83 the cell will show 1109. so it does not show the true value amount. i also tried the same approach with the =RIGHT(H1,2) but with the left and Mid but still didnt work, because i would have to enter the same amount o...

MSP 2007 resources help
Hi all, Three questions: 1. What the constraint A<B,C means and how can I entering in the MSP? 2. I have 3 kinds of human resources. Lets say for example, kind A are engineers. I have 8 of them and everyone is costing 1500 Euros/month. Task A needs 4 engineers. How can I assign them to the task and add the appropriate cost? 3. I have 3 machines as a resource. Each machine has a usage cost of 3000 euros per task and function cost 2400 euros per month. How can I assign them in the appropriate tasks? And how can I distribute their cost? Thank you in advance -- - Hello...

Help! page range prob in print
Hi I worked on print.Everythings working perfectly except one problem.ie. When i set the PageRange option to 'ALL' option and press print button..instead of displaying only the pages which has information ..its displaying that all pages from 1 to 65535 r going to be printed. How do i remove this problem? thanks, vani I thing you may failed to set the maximum number of pages to print. Check CPrintInfo::SetMaxPage(nMaxPage); method regds Jibesh -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspecto...

Link Help!!!
hi, How can I add or remove link from “My Work” under workplace, like Calendar or Reports...? And where the file sitelog.xml?? thnx, ...

display only one formula
i'm taking a computer test and i need to know how to display my formula when it prints so the formula doesn't calculate, but i only need one of the formulas to be shown the rest need to stay in calculated form. How would i do this? Hi Format the cell as Text, select the cell, press F2, and then Enter keys. -- When sending mail, use address arvil<at>tarkon.ee Arvi Laanemets "norcalchick2207" <norcalchick2207@discussions.microsoft.com> wrote in message news:6F223ADF-4546-44C9-8BC8-6D2825872C10@microsoft.com... > i'm taking a computer test and i need ...

Round Function
I am using Excel 2007 and need some helping on a rounding formula. I would like my cell to either round to the nearest whole num or ".5" of the number but it needs to round down. For example: 5.1 would round to 5.0 5.4 would round to 5.0 5.5 would round to 5.5 5.7 would round to 5.5 5.9 would round to 5.5 6.0 would round to 6.0 I am using the Mround function but that rounds up so 5.8 and 5.9 rounds to 6.0 but I need them both to round to 5.5. Any thoughts? Thanks! I think I may have got it: =ROUNDDOWN(A1/0.5,0)*0.5 "duketter" wrote: > ...

trendline HELP!
I have a chart that i am making of PPMs for the year of 2006. I have jan-may in the data series and the rest are left blank. I am trying to get a trendline in where it will stop at whatever month I am at but update when a new month's data is entered into the box. However, I cannot figure out how to do this, when I do have a trend line it goes to like -4000. can anyone help me please? Make a dynamic chart: http://peltiertech.com/Excel/Charts/Dynamics.html http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html http://www.stfx.ca/people/bliengme/ExcelTips/Dynamic.htm...

Help what kind of formula?
I've used Excel formulas in a basic way (sums, averages, divisions, and multiplication of cell data) for about a year. I came across this formula today for a project I need to complete. I've never seen the "SUMIF" or the dollar symbol, OR the ampersand! After hilighting the formula to see if I could just figure it out, I'm stumped. Any chance someone could explain what function these symbols serve Here is the formula =SUMIF($D$3:$D$28,"="&$C38,H$3:H$28) :confused: -- Emil0 ------------------------------------------------------------------------ Emil...

Was this post helpful to you?
Hi gang, Just curious about the Yes, No selection on the question, "Was this post helpful to you? " In a case where I posted a question and received numerous helpful responses, which collectively provided the final solution, do I select Yes for each response that was helpful, or just select Yes on the very last post when the topic has ended? Thanks Peter On Sun, 12 Aug 2007 13:44:01 -0700, Petermgr <Petermgr@discussions.microsoft.com> wrote: >Hi gang, > >Just curious about the Yes, No selection on the question, "Was this post >helpful to you? "...

Need help from fellow Admin about Mailbox sizes
I need a little help from some fellow Admins that have successfully created some policies about mailbox limits. Currently I am fighting the small company mentality in a growing company. We have about 150-200 mailboxes and I am looking for some suggestions on at what size is a good idea to start the warning messages, then stop send and then stop all traffic. I have some users that are getting to the 2GB size. What do you guys suggest? Thanks in advance. There is no standard or best practice for quota settings except that you should set a limit, even if a high one, for "Prohibi...

Slideshow function
In the new Media Center for Win7, I can't seem to find a way to set the slideshow to only show the .jpgs in the currently playing music folder. I have many artwork scans (cover, tray, booklet, etc) in each albums music folder. I've used slideshow plugin's for Winamp and WMP 10 that do this very well (only show images in currently playing folder). Is there a setting somewhere (registry possibly) to tell Media Center's slideshow to do this. It seems ridiculous to have pictures of my family vacation as a slideshow when listening to a Beatles album when I have scan...

Default workbook not coming up help
The workbook I have made is in Excel startup folder and used to come u on launch and file, new or command new. Now only comes up on relaunch and command N, brings up a default workbook. Any ideas appreciated thanks -- Message posted from http://www.ExcelForum.com ...

Function Help?
I need a function that will count back all previous rows for th farthest digit back in the R, S and T cells and place it in the column. The furthest digit back: each of the 3 cells being evaluated ( S and T) have single digit values which have occurred in previous R, T cells, but the value which last appeared in the earliest row is th value which the furthest back. I want the function to count how man rows since the last time that value last occurred. See sample below the N column has the correct answers: Example: .................R..S...T........N row 11:....9...0...6 row 12:....7...5.....

Newbie needs help with first scatter plot
I have a query with two fields: a date and a list of repeating words. Something like 1/1/2007 Red 1/3/2007 Green 2/9/2007 Blue 3/1/2007 Red 3/3/2007 Blue I want to create a simple scatter plot with Date on the X-axis and Color on the Y-axis (there's only seven possible values for the words, so I'd like each word on the Y-axis and a dot in the plot above each date where it occurs). When I use the Chart wizard and select Scatter Plot, the wizard is generating charts that plot *counts*, rather than showing a single dot for each time it occurs. I thought what I wanted to do was pre...

matrix function to do this?
I have a column of data. 12.3 3.5 7.8 8.5 ...etc. 9.4 It is 16 elements in length, but the number 16 may be different for different cases. These are the values of a diagonal 16x16 matrix. 12.3 0 0 0 etc. 0 3.5 0 0 etc. 0 0 7.8 0 etc. etc. What I want is a way to store the data as the column, but have it behave like the diagonal matrix when I do things like mmult, transpose, minverse, etc. It would be cool if there was something like mdiag(a,b,c,d) that would create a matrix that was zero off diagonal, and had the values a, b, c, d, down the diagonal. Is there anyt...

Countif Function??
I would like a formula which will allow me to calculate the number of mondays in a month which are either C- closed, H- holiday. or TT- term from the following list. This will be duplcated for each month. I have tried to use: =COUNTIF(B$2:B$32,"Monday")+COUNTIF(C$2:C$32,"H") but this adds the day and the letter. Can anyone help. Regards Kevan Date April '10 1 Thursday H 2 Friday C 3 4 5 Monday C 6 Tuesday H 7 Wednesday H 8 Thursday H 9 Friday H 10 11 12 Monday H 13 Tuesday TT 14 Wednesday TT 15 Thursday TT 16 Friday TT 17 ...

Desperately need help!!
Is it impossible to do an inventory system in excel? I've tried on numerous occasions for the past month to get a simple inventory system to work and i'm having no luck. I've gotten to the point where my only problem is having a correct record of stock available. I want to be able to see the correct number of a particular item available whenever i do stock taking. So far, what i've done is use iteration to update the stock figure and have sales deducted from that and the new figure shown in quantity avalable. The problem is 1. the iteration does not stop and its set to 1....