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
|
|||
|
|||
Finding text
I am using Excel 2007, I have a column containing formulas that would return
a name if true and a zero if false. I want to search the column for the text entry and return that text as the answer. Can I do this or should the column formulas be changed to something else? The "T" formula seems to work only for one cell not the whole column. |
#2
|
|||
|
|||
Finding text
It's not real clear what you want to do. Here's my best guess...
Assume you want to know if John is in the range. =IF(COUNTIF(A1:A100,"John"),"John","not found") Better to use a cell to hold the criteria: C1 = John =IF(COUNTIF(A1:A100,C1),C1,"not found") -- Biff Microsoft Excel MVP "JOHN" wrote in message ... I am using Excel 2007, I have a column containing formulas that would return a name if true and a zero if false. I want to search the column for the text entry and return that text as the answer. Can I do this or should the column formulas be changed to something else? The "T" formula seems to work only for one cell not the whole column. |
#3
|
|||
|
|||
Finding text
Hi John,
Assuming the text you want to search is there in Cell C1, and the data in which search is to be conducted is there in Column A, Now use the following formula:- =IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is There","Name Is Not There") Let me know if it works.. Thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "JOHN" wrote: I am using Excel 2007, I have a column containing formulas that would return a name if true and a zero if false. I want to search the column for the text entry and return that text as the answer. Can I do this or should the column formulas be changed to something else? The "T" formula seems to work only for one cell not the whole column. |
#4
|
|||
|
|||
Finding text
Let me try to make it clearer. column A contains names, column B contains
sales totals, below in column B is a MIN formula, if the sales total equals the MIN, the formula in column C returns the name, if not a zero. I want the formula to search column C to find the text entry (name) and return that name as the result. In Column C there will be only one text entry and the rest numerical. The text could be in any cell in column C. "DILipandey" wrote: Hi John, Assuming the text you want to search is there in Cell C1, and the data in which search is to be conducted is there in Column A, Now use the following formula:- =IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is There","Name Is Not There") Let me know if it works.. Thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "JOHN" wrote: I am using Excel 2007, I have a column containing formulas that would return a name if true and a zero if false. I want to search the column for the text entry and return that text as the answer. Can I do this or should the column formulas be changed to something else? The "T" formula seems to work only for one cell not the whole column. |
#5
|
|||
|
|||
Finding text
the formula in column C returns the name, if not a zero
there will be only one text entry and the rest numerical. Ok, that clears it up nicely! Try this: =INDEX(C2:C20,MATCH("*",C2:C20,0)) Adjust the range to suit. -- Biff Microsoft Excel MVP "JOHN" wrote in message ... Let me try to make it clearer. column A contains names, column B contains sales totals, below in column B is a MIN formula, if the sales total equals the MIN, the formula in column C returns the name, if not a zero. I want the formula to search column C to find the text entry (name) and return that name as the result. In Column C there will be only one text entry and the rest numerical. The text could be in any cell in column C. "DILipandey" wrote: Hi John, Assuming the text you want to search is there in Cell C1, and the data in which search is to be conducted is there in Column A, Now use the following formula:- =IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is There","Name Is Not There") Let me know if it works.. Thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "JOHN" wrote: I am using Excel 2007, I have a column containing formulas that would return a name if true and a zero if false. I want to search the column for the text entry and return that text as the answer. Can I do this or should the column formulas be changed to something else? The "T" formula seems to work only for one cell not the whole column. |
#6
|
|||
|
|||
Finding text
Thank you Biff,
That works perfectly, just what I was looking for. That you all for your input, the success of my worksheet is the direct result of the people here taking the time to help others. Thank you all again. "T. Valko" wrote: the formula in column C returns the name, if not a zero there will be only one text entry and the rest numerical. Ok, that clears it up nicely! Try this: =INDEX(C2:C20,MATCH("*",C2:C20,0)) Adjust the range to suit. -- Biff Microsoft Excel MVP "JOHN" wrote in message ... Let me try to make it clearer. column A contains names, column B contains sales totals, below in column B is a MIN formula, if the sales total equals the MIN, the formula in column C returns the name, if not a zero. I want the formula to search column C to find the text entry (name) and return that name as the result. In Column C there will be only one text entry and the rest numerical. The text could be in any cell in column C. "DILipandey" wrote: Hi John, Assuming the text you want to search is there in Cell C1, and the data in which search is to be conducted is there in Column A, Now use the following formula:- =IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is There","Name Is Not There") Let me know if it works.. Thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "JOHN" wrote: I am using Excel 2007, I have a column containing formulas that would return a name if true and a zero if false. I want to search the column for the text entry and return that text as the answer. Can I do this or should the column formulas be changed to something else? The "T" formula seems to work only for one cell not the whole column. |
#7
|
|||
|
|||
Finding text
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "JOHN" wrote in message news Thank you Biff, That works perfectly, just what I was looking for. That you all for your input, the success of my worksheet is the direct result of the people here taking the time to help others. Thank you all again. "T. Valko" wrote: the formula in column C returns the name, if not a zero there will be only one text entry and the rest numerical. Ok, that clears it up nicely! Try this: =INDEX(C2:C20,MATCH("*",C2:C20,0)) Adjust the range to suit. -- Biff Microsoft Excel MVP "JOHN" wrote in message ... Let me try to make it clearer. column A contains names, column B contains sales totals, below in column B is a MIN formula, if the sales total equals the MIN, the formula in column C returns the name, if not a zero. I want the formula to search column C to find the text entry (name) and return that name as the result. In Column C there will be only one text entry and the rest numerical. The text could be in any cell in column C. "DILipandey" wrote: Hi John, Assuming the text you want to search is there in Cell C1, and the data in which search is to be conducted is there in Column A, Now use the following formula:- =IF(ISNUMBER(MATCH("*"&TEXT(C1,"@")&"*",$A:$A,0)), "Name Is There","Name Is Not There") Let me know if it works.. Thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "JOHN" wrote: I am using Excel 2007, I have a column containing formulas that would return a name if true and a zero if false. I want to search the column for the text entry and return that text as the answer. Can I do this or should the column formulas be changed to something else? The "T" formula seems to work only for one cell not the whole column. |
Thread Tools | |
Display Modes | |
|
|