Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to search for a text value with IF
Hi,
I am trying to run an IF statement but not sure if it is appropriate because it is involves text. What I have so far is along the lines of =IF(A1:A10=Sheet2!B2,Run Numeric Statement,0). What I am trying through the function to achieve is this: 1. Look in a column for the existance of a word. The Word is referenced to a cell in another worksheet, so only cells are used in the function, which is where the whole A1:A10=Sheet2!B2 test comes in. 2. If that word appears in the column then run a truth statement (All numeric) 3. If that word doesn't appear then the value is 0 The problem I am getting with the IF function is that Excel responds with a #Value error. Can the logical test in the IF statement search through a block of text for the existance of another cell, even if that cell contains text? Is there a more suitable function in Excel for searching for text? Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to search for a text value with IF
You can use MATCH( ) or VLOOKUP( ) to search for text in a column.
Something like: =IF(MATCH(Sheet1!B2,A1:A10,0),Run Numeric Statement,0) might give you what you want, but I don't understand what you mean by "Run Numeric Statement". If the word in Sheet1!B2 does not appear in the range A1:A10, you will get the #N/A error message. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to search for a text value with IF
Yes, it helps but I have a few queries.
What I mean't by "Run numeric ststement" is that I have another table on a another worksheet that I link this formula to. That part of the formula is something like (Sheet1!E2*-2)+(Sheet3!B2*20) but it should only run on the appearance on a specific word in the array as per the initial logital test. The statement you have suggested works but I get a lot of #N/A errors when there is no match (i.e. The text value in Sheet1!B2 does not appear in the range A1:A10). Like you said there would be an error message but is there a way to remove this and run the "0" value in the IF statement or does it fail because #VALUE is inbuilt into the MATCH statement when it can't find the word in the array. I tried HLOOKUP but not too sure on the syntax. I tried =IF(VLOOKUP(Sheet2!A2,Sheet1!A1:A10,1,FALSE),1,0) to test but I always get the #VALUE error. Sheet2!B2 is the word to test Sheet1!A1:A10 is the array "Pete_UK" wrote in message ups.com... You can use MATCH( ) or VLOOKUP( ) to search for text in a column. Something like: =IF(MATCH(Sheet1!B2,A1:A10,0),Run Numeric Statement,0) might give you what you want, but I don't understand what you mean by "Run Numeric Statement". If the word in Sheet1!B2 does not appear in the range A1:A10, you will get the #N/A error message. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to search for a text value with IF
Hi Steve,
Looks pretty complicated from here! Could the ISERROR function help to replace the error message with a 0? =IF(ISERROR(whatever),0,whatever) I only use whatever because I'm uncertain which function you will be using. Ken Johnson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to search for a text value with IF
I think I need to explain myself a little further. What I have is 2
worksheets in 1 workbook. Worksheet 1 has several rows of text in 10 columns (possibly 20 different words can be possible in the cells). In worksheet 2, the first colum is the list of all the possible words that are in the table in worksheet 1. In the 2nd column (worksheet 2) I want to run a formula like this: Look for the word in Column A (Worksheet 2) and see if it is there in 1 row of the table in worksheet 1. If the word exists then run a SUM formula. The SUM formula is purely integer numbers added and multiplied together so there should be no problem with this. If the word does not exist then return either a blank cell or a "0". Firstly, I have used: =IF(Test if word is there, Run SUM furmula, 0) - =IF(Sheet2!A2=Sheet1!B1:B10,SUM Formula,0) From Pete's good advise I modified this to use the MATCH formula: =IF(MATCH(Sheet2!A2,Sheet1!B1:B10), SUM Formula,0) But this returns the #VALUE error when the word I am testing for does not exist in the row (array). When I put in the ISERROR function round the MATCH function I just get a blank cell. I tried altering the formula to: =IF(ISERROR(MATCH(Sheet2!A2,Sheet1!B1:B10)), SUM Formula,0) Was this the correct why of using the ISERROR function or can it not be used in that way? Steve "Ken Johnson" wrote in message oups.com... Hi Steve, Looks pretty complicated from here! Could the ISERROR function help to replace the error message with a 0? =IF(ISERROR(whatever),0,whatever) I only use whatever because I'm uncertain which function you will be using. Ken Johnson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unable to search for a text value with IF
I think if the word does not exist then you want to return a zero, so
you need to re-arrange your formula. Something along the lines of: =IF(ISERROR(MATCH(Sheet2!A2,Sheet1!B1:B10)),0,SUM Formula) Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How change dimensions of data label text box in pie chart? | Charts and Charting in Excel | |||
Search for Text in Excel | Excel Discussion (Misc queries) | |||
How do I search for specific text and sum the cell to the right? | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
How do I use VLOOKUP to search for text? | Excel Worksheet Functions |