FIND or SEARCH Returning Erroneous #VALUE?

Hello group.

I've delved into the acrhives for this topic but haven't seen it
specifically addressed.

I have two columns of values ("E" is 480 rows and "J" is 2200 rows).
Some values in E appear in J; this I've verified using <ctrl>+<f>. For
each row in E, I am entering
=SEARCH(E5, J$5:J$2238)
to simply result in a 1 if there's a match and #VALUE if not. In
another column I am using =IF(ISERROR(K5), "new", "existing") to
determine my result so I am expecting _some_ #VALUEs.

To create the two coumns, I had concatenated other columns. In an
effort to eliminate any formatting concerns, I copied the columns and
pasted special as values.

I've entered the formula using <enter> and the array method
<ctrl>+<shift>+<enter>.

Regardless of the entry method or formatting (general, text, number), I
am getting #VALUE in every row, including rows where the value in E
_does_ appear in J$5:J$2238.

I imagine I am missing something extraordinarily simple; anyone know
what it is? I know from online help when #VALUE is to result but none
of the three cases apply to this.


Regards,
Patrick

Would the following do?

=IF(E5<>"",IF(COUNTIF(J$5:J$2238,E5),"Existing","New"),"")

In article <[email protected]>,
"Patrick McDonald" <[email protected]> wrote:

> Hello group.
>
> I've delved into the acrhives for this topic but haven't seen it
> specifically addressed.
>
> I have two columns of values ("E" is 480 rows and "J" is 2200 rows).
> Some values in E appear in J; this I've verified using <ctrl>+<f>. For
> each row in E, I am entering
> =SEARCH(E5, J$5:J$2238)
> to simply result in a 1 if there's a match and #VALUE if not. In
> another column I am using =IF(ISERROR(K5), "new", "existing") to
> determine my result so I am expecting _some_ #VALUEs.
>
> To create the two coumns, I had concatenated other columns. In an
> effort to eliminate any formatting concerns, I copied the columns and
> pasted special as values.
>
> I've entered the formula using <enter> and the array method
> <ctrl>+<shift>+<enter>.
>
> Regardless of the entry method or formatting (general, text, number), I
> am getting #VALUE in every row, including rows where the value in E
> _does_ appear in J$5:J$2238.
>
> I imagine I am missing something extraordinarily simple; anyone know
> what it is? I know from online help when #VALUE is to result but none
> of the three cases apply to this.
>
>
> Regards,
> Patrick

Domenic,
Yes, this works; thank you very much! I didn't know you could use
COUNTIF with an IF statement like that but it's beginning to make more
sense the more I look at it. Thank you again, Domenic.

I don't know what is wrong with my original formula, though, and these
things tend to disturb me until I understand them.

Patrick McDonald wrote...
....
>I have two columns of values ("E" is 480 rows and "J" is 2200 rows).
>Some values in E appear in J; this I've verified using <ctrl>+<f>. For
>each row in E, I am entering
>=SEARCH(E5, J$5:J$2238)
>to simply result in a 1 if there's a match and #VALUE if not. In
>another column I am using =IF(ISERROR(K5), "new", "existing") to
>determine my result so I am expecting _some_ #VALUEs.

The formula =SEARCH(E5,J$5:J$2238) very likely doesn't do what you seem
to believe it does. SEARCH returns the first/leftmost position of its
1st argument in its 2nd argument, both interpretted as strings. If you
pass it a range or array 2nd argument, it returns an array containing
the positions of its 1st argument in each of the items in its 2nd
argument. It looks like you want

=MATCH(E5,J$5:J$2238,0)

instead. You should then replace the ISERROR in your second formula
with ISNA.

>I've entered the formula using <enter> and the array method
><ctrl>+<shift>+<enter>.
>
>Regardless of the entry method or formatting (general, text, number), I
>am getting #VALUE in every row, including rows where the value in E
>_does_ appear in J$5:J$2238.

If all of your formulas really do look like

E#:
=SEARCH(E#,J$5:J$2238)

then if you're entering each & every such formula in a single cell,
each & every such formula is returning the *same* result as if you had
used

E#:
=SEARCH(E#,J$5)

As I said above, it appears you need to use MATCH rather than SEARCH.