software

I have a text in one cell (J2) and have to compare it with text in a range of
cells (B2:B999) to return an expression if true. I have tried using the IF
function e.g
IF(J2=B2:B28,quot;Orderedquot;,quot;Not Orderedquot;), but it doesn't appear to like working
with a range of cell, works fine if you I point it to a single cell. Any help
would be appreciated.

Hi John,

=IF(SUMPRODUCT(--(B2:B999=J2)),quot;Orderedquot;, quot;Not Orderedquot;)

HTH

Giz

quot;JohnDquot; wrote:

gt; I have a text in one cell (J2) and have to compare it with text in a range of
gt; cells (B2:B999) to return an expression if true. I have tried using the IF
gt; function e.g
gt; IF(J2=B2:B28,quot;Orderedquot;,quot;Not Orderedquot;), but it doesn't appear to like working
gt; with a range of cell, works fine if you I point it to a single cell. Any help
gt; would be appreciated.

=IF(COUNTIF(B2:B999,J2)gt;0,quot;Orderedquot;,quot;Not Orderedquot;)

HTH
--
AP

quot;JohnDquot; gt; a écrit dans le message de news:
...
gt;I have a text in one cell (J2) and have to compare it with text in a range
gt;of
gt; cells (B2:B999) to return an expression if true. I have tried using the IF
gt; function e.g
gt; IF(J2=B2:B28,quot;Orderedquot;,quot;Not Orderedquot;), but it doesn't appear to like
gt; working
gt; with a range of cell, works fine if you I point it to a single cell. Any
gt; help
gt; would be appreciated.
=IF(ISNA(VLOOKUP(J2,B:B,1,FALSE)),quot;Not Orderedquot;,quot;Orderedquot;)

Vaya con Dios,
Chuck, CABGx3
quot;JohnDquot; wrote:

gt; I have a text in one cell (J2) and have to compare it with text in a range of
gt; cells (B2:B999) to return an expression if true. I have tried using the IF
gt; function e.g
gt; IF(J2=B2:B28,quot;Orderedquot;,quot;Not Orderedquot;), but it doesn't appear to like working
gt; with a range of cell, works fine if you I point it to a single cell. Any help
gt; would be appreciated.

if is to compare Cells with text try this
=IF(OR(EXACT(A1,H1:H9)),quot;Not orderedquot;,quot; orderedquot;)

quot;The formula in the example must be entered as an array formula (array
formula: A formula that performs multiple calculations on one or more sets of
values, and then returns either a single result or multiple results. Array
formulas are enclosed between braces { }, and are entered by pressing
CTRL SHIFT ENTER.). After copying the example to a blank worksheet, select
each formula cell individually. Press F2, and then press CTRL SHIFT ENTER. If
the formula is not entered as an array formula, the error #VALUE! is returned.

The formula above uses the following syntax:

=OR(EXACT(TestValue, CompareRange))

TestValue refers to a cell containing a user-entered value; CompareRange
refers to a list of text values to be compared against.

cc

quot;JohnDquot; wrote:

gt; I have a text in one cell (J2) and have to compare it with text in a range of
gt; cells (B2:B999) to return an expression if true. I have tried using the IF
gt; function e.g
gt; IF(J2=B2:B28,quot;Orderedquot;,quot;Not Orderedquot;), but it doesn't appear to like working
gt; with a range of cell, works fine if you I point it to a single cell. Any help
gt; would be appreciated.