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

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

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

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

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

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

Works great. Thank you

"Pete_UK" wrote in message
oups.com...
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

Thanks for feeding back.

Pete

I've realised that the MATCH function needs to reference a few different
worksheets so I need to add multiple data locations in the MATCH function. I
have tried MATCH(Woksheet1!A1,Worksheet2!A1:A10&Worksheet3!A1 :A10) but not
sure of the correct syntax to search for different locations and what I have
tried fails to work. I have tried the above and it always returns a 0, which
means it is probably creating an error. I checked the result of the MATCH
statment above and it returns a #N/A result.

Is there a way to reference several locations to look for a particular word
in 3 different tables on 3 different worksheets? The word can appear in any
of the worksheets.

Steve

"Pete_UK" wrote in message
ups.com...
Thanks for feeding back.

Pete

Hi Steve,

If you want your SUM Formula evaluated when the Sheet1!A1 value is in
any of the worksheet arrays you could use the AND formula. Using your
previous example...

=IF(AND(ISERROR(MATCH(Sheet1!A1,Sheet2!A1:A10,FALS E)),
ISERROR(MATCH(Sheet1!A1,Sheet3!A1:A10,FALSE))),0,S UM Formula))

If you want your SUM Formula evaluated when the Sheet1!A1 value is in
all the worksheet arrays you could use the OR formula. Using your
previous example...

=IF(OR(ISERROR(MATCH(Sheet1!A1,Sheet2!A1:A10,FALSE )),
ISERROR(MATCH(Sheet1!A1,Sheet3!A1:A10,FALSE))),0,S UM Formula))

Ken Johnson

I see what you have done here and I'm quite impressed: Search for the word
in the array and returns a true or false depending on whether an error comes
from the MATCH and ISERROR statements. Using the AND function, if both
return errors then the sum formula is not run and if 1 ISERROR/MATCH
statement is true then the sum formula is run.

Its probably the longest set of functions I've ever set out. I think when
I'm done, the worksheet will be a hefty size. I might want to think about
cutting the worksheets into workbooks in the same folder. I presume it will
be a case of changing the formula to linking to the physical location of the
workbook instead of the worksheet.

Thanks for all your help

Hi Steve,
I've never had to write a formula that refers to other workbooks. I
know it is done but I'm not aware of the pitfalls. I can vaguely recall
reading that some functions require the referred to workbooks be open,
but that's all I know about that.
Hopefully splitting up the workbook won't be necessary.

Ken Johnson

Hi Steve,
Just for fun I figured out another way:-)
It doesn't use MATCH or ISERROR, however, it is an array formula and
must be entered using Ctrl + Shift + Enter.
If you require that the Sheet1!A1 value be in both Sheet2 and Sheet3
lists the array formula would be...

=IF(AND(OR(Sheet1!A1=Sheet2!A1:A10),OR(Sheet1!A1=S heet3!A1:A10)),SUM
Formula,0)

If the Sheet1!A1 value only needs to be in at least one of the Sheet2
and Sheet3 lists the array formula would be...


=IF(OR(OR(Sheet1!A1=Sheet2!A1:A10),OR(Sheet1!A1=Sh eet3!A1:A10)),SUM
Formula,0)

If you enter the formula correctly as an array formula it will appear
in the Formula Bar enclosed by {, and }.
I'm notorious for forgetting to enter array formulas the correct way,
especially after doing a bit of editing. When the formula then gives me
wrong results the first thing I do is check out the formula in the
Formula Bar. Most of the time the {}s are missing. To correct my error
I have to click in the Formula then go Ctrl + Shift + Enter to get the
{}s back in place. Typing in the {}s yourself does not have the desired
effect, Excel has to insert them via Ctrl + Shift + Enter.

Ken Johnson