Search for a text in a cell and give the outcome later

Dear expert,
C30 to C60 contains some data.
Say 
I wan to find piano and use =MATCH("*piano*",C30:C60,0) in cell D31
It gives me the anwer 20 in D31. 20 is the squence of piano.
How can I use the function and use return the the whole cell content which 
is "Play Piano"? I only know where it is. How to locate where and give the 
whole content in D32 please?
Thanks
Elton


Play games
Play golf
Play guitar
Play piano
Play Kite
Play Sccoer
Play cards
Play WII
Play music


0
Utf
1/14/2010 11:06:02 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
537 Views

Similar Articles

[PageSpeed] 34

=INDEX(C30:C60,D31)

-- 
Regards!
Stefi



„Elton Law” ezt írta:

> Dear expert,
> C30 to C60 contains some data.
> Say 
> I wan to find piano and use =MATCH("*piano*",C30:C60,0) in cell D31
> It gives me the anwer 20 in D31. 20 is the squence of piano.
> How can I use the function and use return the the whole cell content which 
> is "Play Piano"? I only know where it is. How to locate where and give the 
> whole content in D32 please?
> Thanks
> Elton
> 
> 
> Play games
> Play golf
> Play guitar
> Play piano
> Play Kite
> Play Sccoer
> Play cards
> Play WII
> Play music
> 
> 
0
Utf
1/14/2010 12:23:01 PM
Thanks for help ...

"Stefi" wrote:

> =INDEX(C30:C60,D31)
> 
> -- 
> Regards!
> Stefi
> 
> 
> 
> „Elton Law” ezt írta:
> 
> > Dear expert,
> > C30 to C60 contains some data.
> > Say 
> > I wan to find piano and use =MATCH("*piano*",C30:C60,0) in cell D31
> > It gives me the anwer 20 in D31. 20 is the squence of piano.
> > How can I use the function and use return the the whole cell content which 
> > is "Play Piano"? I only know where it is. How to locate where and give the 
> > whole content in D32 please?
> > Thanks
> > Elton
> > 
> > 
> > Play games
> > Play golf
> > Play guitar
> > Play piano
> > Play Kite
> > Play Sccoer
> > Play cards
> > Play WII
> > Play music
> > 
> > 
0
Utf
1/15/2010 1:23:01 AM
You are welcome! Thanks for the feedback!
Stefi

-- 
Regards!
Stefi



„Elton Law” ezt írta:

> Thanks for help ...
> 
> "Stefi" wrote:
> 
> > =INDEX(C30:C60,D31)
> > 
> > -- 
> > Regards!
> > Stefi
> > 
> > 
> > 
> > „Elton Law” ezt írta:
> > 
> > > Dear expert,
> > > C30 to C60 contains some data.
> > > Say 
> > > I wan to find piano and use =MATCH("*piano*",C30:C60,0) in cell D31
> > > It gives me the anwer 20 in D31. 20 is the squence of piano.
> > > How can I use the function and use return the the whole cell content which 
> > > is "Play Piano"? I only know where it is. How to locate where and give the 
> > > whole content in D32 please?
> > > Thanks
> > > Elton
> > > 
> > > 
> > > Play games
> > > Play golf
> > > Play guitar
> > > Play piano
> > > Play Kite
> > > Play Sccoer
> > > Play cards
> > > Play WII
> > > Play music
> > > 
> > > 
0
Utf
1/15/2010 8:28:01 AM
Reply:

Similar Artilces:

count cells using multiple criteria
Can anyone show me how to count the number of rows containing two or more criteria? For example, if one column has the text "DO" and another column has the text "RD11" how do I count only those rows that contain both DO and RD11? One way: =SUMPRODUCT(--(A1:A1000="DD"),--(B1:B1000="RD11")) In article <0F41177E-45A0-4B0B-AE33-6BCCF2F3B8D1@microsoft.com>, Alex68 <Alex68@discussions.microsoft.com> wrote: > Can anyone show me how to count the number of rows containing two or more > criteria? For example, if one column has the te...

Macro to delete an entire row with cells missing a specific chartacter #2
Hey Harald, This is the script I am using to remove "IS" Sub Findanddelete_IS() Dim rng As Range Dim what As String what = "IS" Do Set rng = ActiveSheet.UsedRange.Find(what) If rng Is Nothing Then Exit Do Else Rows(rng.Row).Delete End If Loop End Sub So...my programming experience is a tiny bit limited, not sure how t delete anything missing the IS as opposed to containing the IS. understand in theory but my knowledge of syntax is the limiter. Harald Staff Wrote: > Hi Asf > > This is far more useful if you figure it out yourself, since you'r > pr...

Excel text box #2
Is there a way to set the default text alignment to "center" for both vertical and horizontal in the draw text box? I am using Excel 2000. I found it for the cell default. That's what I want to do with the draw text box. -- Thanks To get the horizontal centering--just select your text and hit the Center Icon on the format toolbar--just like you would to center text in a cell. I had to hit enter a few times (at the beginning of the text) to center it vertically. rvrl wrote: > > Is there a way to set the default text alignment to "center" for both > ...

Getting a cell reference to move laterally instead of vertically?
I have data that is sorted in columns, but the data I want to graph is a cross section of this data and therefore it comes from one row across several columns. I'm trying sort my graph/equation data in a column, however. Is there a way to get the equation cell references to move across a row even while the cell is descending a column? You can do it on a range using the TRANSPOSE function (array entered), or you may want to take a look at the OFFSET function, which takes a row and column argument. Use the ROW() function to generate the column argument and you will be able to copy...

is there a faster way to get text
Everytime I want to type something, I have to go back up to the Text Box, is there a quick key stroke that will allow me to keep typing without having to click on the Text Box over and over again? Denise, If you have PPT 2003, double click the textbox icon on the drawing toolbar. From that moment on you can click and type as many textboxes as you will. To stop just press escape or double click the icon again. If you have PPT 2007, you can do the same thing by right-clicking the textbox icon to lock the button. Luc Sanders MVP - PowerPoint "Denise" wrote: > ...

text wrapping #6
How do I get text to wrap around a gallery object? The same way as any wrapping. What problem are you having? Select the object, Arrange, Order, bring to the front (alt+F6). If you have a border around your publication, send it to the back first. (alt+shift+F6). -- Mary Sauer http://msauer.mvps.org/ "rita alford" <rita alford@discussions.microsoft.com> wrote in message news:B08694CB-BB1C-43E5-B9F7-250D8ECF6448@microsoft.com... > How do I get text to wrap around a gallery object? ...

Help in displaying in a cell
I had type in a number of words (< 400 word count based of microsoft works) in a excel spreadsheet, but the cell only display ############ wrap text option is checked, What's the exact problem? Using excel 2007 Is the cell formatted as Text? Apply a General format to it instead. Also, you can insert manual line breaks as you enter the text using Alt-Enter. Hope this helps. Pete On Jul 3, 9:59=A0am, "crapit" <biggerc...@yahoo.com> wrote: > I had type in a number of words (< 400 word count based of microsoft works= ) > in a excel spreadsheet, but the c...

Merging cells with the same values
I am looking to merge all the horizontal cells in a sheet that display the same value answer as the cell directly along side it. The values are generated from a lookup formular that expresses the answers as words. Is it possible to get a group of cells alongside each other to merge and display the same answer automatically. Thanks ...

Calculate total number off cells with data in
Hi All Hope I find you well I have a spreadsheet that has a number of cells that may or may not have data in them depending on curtain criteria. What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? Hi Gazza, > What I would like to be able to do is count the number of cells with data in (data is alphanumerical) and display the total number of cells with data in in a different cell. Any ideas? > Check out the COUNTA worksheet function. Regards, Jan Kare...

cannot change part of a merged cell
This is the error a user receives when he tries to copy one cell into another made up of 3 merged cells. Sometimes it works and other times it doesn't. Is this a fluke? thanks, dominique Copying one cell to a merge of three cells should work. Are you "sure" it is only one cell that is being copied each time? Maybe "sometimes" it is one cell and "other times" more than one. Gord Dibben Excel MVP On Tue, 8 Jun 2004 14:02:57 -0400, "Dominique Schroeder" <dmschroe@woodmeistercorp.com> wrote: >This is the error a user receives when he...

Rich Text
Hi, One of my clients wants to record job position description in CRM. However, lack of rich text functionality in CRM makes it harder for them to use CRM. Is there any way I can provide rich text within CRM - whether by configuration, .NET customization or a third party add on? Please advise. Thanks, Shikhar have you tried using the data type (ntext) and format "text area" ? ------ Aamir Blog = http://mscrmsupport.wordpress.com/ Hi Aamir, How do you format the "text area" to give a rich text functionality? Thanks for taking time to respond. Shikhar "A...

Vanishing text boxes
I'm trying to copy, as pictures, a considerable number of charts from different sheets onto a consolidated sheet that I can then distribute without the large quantity of underlying data and calculations. All the charts were generated by the same VBA routine and each includes three text boxes. These are unavoidable. When I copy the charts as pictures one at a time by hand I've not met any problems. However, doing the copying within a VBA loop results in some of the copied charts appearing without their text boxes. Whenever this happens all three text boxes are missing, even though ...

Change XY tick labels from numeric to text
Hi all. Hope you can help. I am exploring a huge dataset of data by U.S. zip codes. Each row is a zip code, the state the zip belongs to, the state number, and a count of widgets. 91234 AK 2 0.123 91872 AK 2 0.234 91232 AK 2 0.345 26271 AZ 3 0.111 23111 AZ 3 0.222 Although this might sound strange, I am graphing the entire dataset as an XY scatter plot --- with the state number as the x values and the widgets as the y-values. The resultant plot shows the distribution of widgets for each state very quickly and easily and all on one graph. I&#...

How can you reference a picture from a worksheet cell?
I have an image or picture within a worksheet. I want to be able to reference it from a cell so that depending on a validation list a different picture is shown. Is this possible in Microsoft Exec 2000 Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html He has a neat way of doing things like this. scott56hannah wrote: > > I have an image or picture within a worksheet. > > I want to be able to reference it from a cell so that depending on a > validation list a different picture is shown. > > Is this possible in Microsoft...

Shading text using keyboard shortcuts
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am editing formatted text. I want to be able to select words and shade them yellow, green, no shade, etc. without changing the formatting (bold, italics) - and do this entirely using customized keyboard shortcuts and the shift and arrow keys (that is, no mouse). How can I do this? <br><br>Thanks, <br> - Steve Use Control+U to edit content in the cell. You can then use the arrow keys to navigate left & right. Shift+Control+L/R Arrow selects by word but you're on your own as far as se...

Pivot Table -- Text??
I don't think this is possible, but thought I'd check to be sure. Can a pivot table display text values or must a pivot table do calculations and only display numbers?? I am basically trying to create a report from data I pulled from MS Project... I've got a resource name, a list of dates, and a list of projects. I'm looking for a solution to the cross tab report "who does what when." I want to create a pivot table that will display the dates as columns, and the resource names as rows... and then in the data section the project name. I want it to look somethin...

Copy ranges into email
Hi, How do i add more than one item to the body of a outlook mail using vba. I am using named range to select the items. i want to add Text, the leave two lines HTMLBody then leave two lines and add another HTMLBody and leace two lines and add Text I have a function that does the RangetoHTML after passing myRange to it. I am getting confused here. With OutMail .To = s .CC = "SH-DI-List" .BCC = "" .Subject = n & " - Trades in Pending Queue in SW " & Format(Now, "dd-Mmm-yyyy") & _ "...

using a popup to make percenatges on selected cells
Hi I'm hoping someone can help with some code. I'm selecting a group of cells containing numbers. I want via a popup to change the selected numbers to the percentage entered in the popup. For example - I select my cells and run the macro. The popup asks for a number and I enter '50'. All the selected numbers then change to 50 percent of their previous value. It would apply equally to a selected whole column of numbers. Grateful for any help. Best Wishes hi Sub changenumber() Dim n As Double n = InputBox("enter a number") n = n / 100 ...

How can a calculated value be displayed wiyhin a text string
I am trying to display calculated value in a text string. The value could change as the variables change. the use of the Ampersand "&" ="all " & (2+3) & " people" will show as all 5 people If you want the number to be in a given format check the text() funciton in help. "Furrukh" wrote: > I am trying to display calculated value in a text string. The value could > change as the variables change. You don't give any details, but perhaps something like ="The sum of the first 10 values is " & SUM(A1:A10) &a...

Import Text to specific cell
I'd like to import a set of data to specific, non-sequential, cells in a spreadsheet. Is there a way of doing this without using VBA? e.g. 3 fields worth per row of source data with the first being Column and the second being row and third being the text (or number) to put in a cell. Data: A 3 "3.5 Course1" C 5 " 3 Course 3" etc. This would put "3.5 Course1" in cell A:3, " 3 Course 3" in C:5 etc. It seems as though it ought to be doable. Any help would be appreciated. Jim Mitchell I don't think you can without VBA Assuming your ...

searching for mails
Hello we just change our system from netscape to outlook. Know i'm not used to the program outlook and i must search for mails a lot. With netscape you could search by date, is this also possible in outlook (version 2000)? Than i've another little problem with importing the mail files. Some mails have no date anymore. There just stand (none), while other mails in the same folder have a correct date. Does anybody know how this may come? Thanks in advance Kind regards Erwin Bormans Docent Aron www.aron.be erwin.bormans@aron.be I've found the solutions to my problems! Greetz ...

Creating a graduation program border using text
Hi, I am creating a program for a graduation event. On the front cover I would like to create a border using text from a poem or a bible verse. I have no clue how to create a border using text. Please share any ideas you have on how to do this. Thank you so much! You would have to create text boxes to fit and rotate the sides and bottom. Your best solution is a draw program. DrawPlus is free. Usually draw programs have *fit text to path*. http://www.freeserifsoftware.com/ -- Mary Sauer http://msauer.mvps.org/ "werjones" <werjones@discussions.microsoft.com> wrote in...

text running together
When I use a pulldown on a webpage, the text is not showing fully. It looks like the top word is running into the word below it. It looks like your trying to fit text into a too small space. Another example is when you look at a search box on a web page and the work "search" is already typed in it, it looks like the word "search" is showing the top half only. It's hard to explain. It takes a word such as microsoft and the bottom of the m and the i and the other letters are cut off. Also, when I try to print the page or if I try to print a document,...

Unlock cells based on colour
Hi I have input cells coloured blue and I want to unlock these cells. I know I need to use Colorindex, but I can't work out how to put it all together. Many thanks Here's a procedure that will change your 'Blue' cells to unprotected. HOWEVER, you need to decide which blue you want to unprotect. I've also attached a UDF [ColorOfCell] that will tell you the # associated with the BLUE you want. Change the 'BlueColor' variable in the procedure to that #. '/============================/ Public Sub MakeBlueCellsUnProtected() Dim BlueColor As ...

Searching for Date Range
I'd like to set up a function to allow the user to search for files based on a due date. I would like it so that the user can search for files due in the following three weeks of the current date. Does anyone know how I would be able to set this up? Use BETWEEN DateAdd("ww", -3, Date) AND Date as the criteria for your date field. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) <jtshockey@gmail.com> wrote in message news:1194892751.925372.73750@e9g2000prf.googlegroups.com... > I'd like to set up a function to allow the user ...