Search column for value and return TRUE or FALSE

=NOT(ISNA(MATCH("x",A1:A10,0)))
or
=NOT(ISNA(MATCH("-x",A1:A10,0)))
or
=NOT(ISNA(MATCH(B1,A1:A10,0))) where B1 contains the criteria to search

Bob Umlas
Excel MVP

"Remote Desktop Connection hotkey"
<RemoteDesktopConnectionhotkey@discussions.microsoft.com> wrote in message
news:46F9A463-2694-4186-9D5C-6857DD419F13@microsoft.com...
> Hi!
>
> I function that would search through column of text values (range with 1
> column) and return TRUE if column contains value given as criteria or
> FALSE
> if there is no such value in given column
>
> For example i have range A1:A10 and i want to find out whether this range
> contains value x or - x

Thanks man.... i can't understand why i couldn't come up whith this myself

"Bob Umlas" wrote:

> =NOT(ISNA(MATCH("x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH("-x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH(B1,A1:A10,0))) where B1 contains the criteria to search
>
> Bob Umlas
> Excel MVP
>
> "Remote Desktop Connection hotkey"
> <RemoteDesktopConnectionhotkey@discussions.microsoft.com> wrote in message
> news:46F9A463-2694-4186-9D5C-6857DD419F13@microsoft.com...
> > Hi!
> >
> > I function that would search through column of text values (range with 1
> > column) and return TRUE if column contains value given as criteria or
> > FALSE
> > if there is no such value in given column
> >
> > For example i have range A1:A10 and i want to find out whether this range
> > contains value x or - x
>
>
>

Bob Umlas wrote:
> =NOT(ISNA(MATCH("x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH("-x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH(B1,A1:A10,0))) where B1 contains the criteria to search

Easier (and efficient) if we substitute ISNUMBER(...) for NOT(ISNA(...).

Even better, if an optional argument in MATCH() was available:

=MATCH(LValue,LRange,0,FALSE)

instead of

=ISNUMBER(MATCH(LValue,LRange,0))

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=NOT(ISNA(MATCH("x",A1:A10,0)))
or
=NOT(ISNA(MATCH("-x",A1:A10,0)))
or
=NOT(ISNA(MATCH(B1,A1:A10,0))) where B1 contains the criteria to search

Bob Umlas
Excel MVP

"Remote Desktop Connection hotkey"
<RemoteDesktopConnectionhotkey@discussions.microsoft.com> wrote in message
news:46F9A463-2694-4186-9D5C-6857DD419F13@microsoft.com...
> Hi!
>
> I function that would search through column of text values (range with 1
> column) and return TRUE if column contains value given as criteria or
> FALSE
> if there is no such value in given column
>
> For example i have range A1:A10 and i want to find out whether this range
> contains value x or - x

Thanks man.... i can't understand why i couldn't come up whith this myself

"Bob Umlas" wrote:

> =NOT(ISNA(MATCH("x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH("-x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH(B1,A1:A10,0))) where B1 contains the criteria to search
>
> Bob Umlas
> Excel MVP
>
> "Remote Desktop Connection hotkey"
> <RemoteDesktopConnectionhotkey@discussions.microsoft.com> wrote in message
> news:46F9A463-2694-4186-9D5C-6857DD419F13@microsoft.com...
> > Hi!
> >
> > I function that would search through column of text values (range with 1
> > column) and return TRUE if column contains value given as criteria or
> > FALSE
> > if there is no such value in given column
> >
> > For example i have range A1:A10 and i want to find out whether this range
> > contains value x or - x
>
>
>

Bob Umlas wrote:
> =NOT(ISNA(MATCH("x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH("-x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH(B1,A1:A10,0))) where B1 contains the criteria to search

Easier (and efficient) if we substitute ISNUMBER(...) for NOT(ISNA(...).

Even better, if an optional argument in MATCH() was available:

=MATCH(LValue,LRange,0,FALSE)

instead of

=ISNUMBER(MATCH(LValue,LRange,0))

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Hi!

I function that would search through column of text values (range with 1
column) and return TRUE if column contains value given as criteria or FALSE
if there is no such value in given column

For example i have range A1:A10 and i want to find out whether this range
contains value x or - x

=NOT(ISNA(MATCH("x",A1:A10,0)))
or
=NOT(ISNA(MATCH("-x",A1:A10,0)))
or
=NOT(ISNA(MATCH(B1,A1:A10,0))) where B1 contains the criteria to search

Bob Umlas
Excel MVP

"Remote Desktop Connection hotkey"
<RemoteDesktopConnectionhotkey@discussions.microsoft.com> wrote in message
news:46F9A463-2694-4186-9D5C-6857DD419F13@microsoft.com...
> Hi!
>
> I function that would search through column of text values (range with 1
> column) and return TRUE if column contains value given as criteria or
> FALSE
> if there is no such value in given column
>
> For example i have range A1:A10 and i want to find out whether this range
> contains value x or - x

Thanks man.... i can't understand why i couldn't come up whith this myself

"Bob Umlas" wrote:

> =NOT(ISNA(MATCH("x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH("-x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH(B1,A1:A10,0))) where B1 contains the criteria to search
>
> Bob Umlas
> Excel MVP
>
> "Remote Desktop Connection hotkey"
> <RemoteDesktopConnectionhotkey@discussions.microsoft.com> wrote in message
> news:46F9A463-2694-4186-9D5C-6857DD419F13@microsoft.com...
> > Hi!
> >
> > I function that would search through column of text values (range with 1
> > column) and return TRUE if column contains value given as criteria or
> > FALSE
> > if there is no such value in given column
> >
> > For example i have range A1:A10 and i want to find out whether this range
> > contains value x or - x
>
>
>

Bob Umlas wrote:
> =NOT(ISNA(MATCH("x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH("-x",A1:A10,0)))
> or
> =NOT(ISNA(MATCH(B1,A1:A10,0))) where B1 contains the criteria to search

Easier (and efficient) if we substitute ISNUMBER(...) for NOT(ISNA(...).

Even better, if an optional argument in MATCH() was available:

=MATCH(LValue,LRange,0,FALSE)

instead of

=ISNUMBER(MATCH(LValue,LRange,0))

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.