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"
<[email protected]> wrote in message
news:[email protected]...
> 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"
> <[email protected]> wrote in message
> news:[email protected]...
> > 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"
<[email protected]> wrote in message
news:[email protected]...
> 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"
> <[email protected]> wrote in message
> news:[email protected]...
> > 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.

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.

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"
> <[email protected]> wrote in message
> news:[email protected]...
> > 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"
<[email protected]> wrote in message
news:[email protected]...
> 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"
<[email protected]> wrote in message
news:[email protected]...
> 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.

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"
> <[email protected]> wrote in message
> news:[email protected]...
> > 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"
> <[email protected]> wrote in message
> news:[email protected]...
> > 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"
<[email protected]> wrote in message
news:[email protected]...
> 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

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.

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"
> <[email protected]> wrote in message
> news:[email protected]...
> > 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"
<[email protected]> wrote in message
news:[email protected]...
> 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

I have been searching for over an hour for a solution to my problem. This
post is the closest to my issue but slightly different. Instead of showing
True or False I want it to show “Y” or “N”.

Example:
I am trying to answer the question is this a school day.
I have a date in cell C2 that I comparing with a list of non-school days at
I12:I34 (it’s one column). If the date matches something in the list I want
a “N” to appear for NON-School Day. If the date does not match it must be a
school day so I want to see “Y”.

Any help would be greatly appreciated.


"Remote Desktop Connection hotkey" wrote:

> 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

HI
Try
=ISNUMBER(MATCH(C2,L12:L34,0))

--
Regards

Roger Govier


"JICDB" <[email protected]> wrote in message
news:[email protected]...
>I have been searching for over an hour for a solution to my problem.
>This
> post is the closest to my issue but slightly different. Instead of
> showing
> True or False I want it to show "Y" or "N".
>
> Example:
> I am trying to answer the question is this a school day.
> I have a date in cell C2 that I comparing with a list of non-school
> days at
> I12:I34 (it's one column). If the date matches something in the list
> I want
> a "N" to appear for NON-School Day. If the date does not match it
> must be a
> school day so I want to see "Y".
>
> Any help would be greatly appreciated.
>
>
> "Remote Desktop Connection hotkey" wrote:
>
>> 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 but it didn't work for some reason. I get FALSE in every instance
even if the data is located in the non-school day column.

"Roger Govier" wrote:

> HI
> Try
> =ISNUMBER(MATCH(C2,L12:L34,0))
>
> --
> Regards
>
> Roger Govier
>
>
> "JICDB" <[email protected]> wrote in message
> news:[email protected]...
> >I have been searching for over an hour for a solution to my problem.
> >This
> > post is the closest to my issue but slightly different. Instead of
> > showing
> > True or False I want it to show "Y" or "N".
> >
> > Example:
> > I am trying to answer the question is this a school day.
> > I have a date in cell C2 that I comparing with a list of non-school
> > days at
> > I12:I34 (it's one column). If the date matches something in the list
> > I want
> > a "N" to appear for NON-School Day. If the date does not match it
> > must be a
> > school day so I want to see "Y".
> >
> > Any help would be greatly appreciated.
> >
> >
> > "Remote Desktop Connection hotkey" wrote:
> >
> >> 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
>
>
>

Hi

It returns True or False for me depending upon whether the date is the
list or not.
Are you using proper Excel dates, or are they Text representations of
the date.
If you type =C2+1 and format the cell as date, do you get a date which
is one day greater than the date in C2?
similarly, what happens if you add 1 to each of the date values in
L12:L34.

To return what you are looking for in terms of "Y or "N" than the
formula needs to be
=IF(ISNUMBER(MATCH(C2,L12:L34,0)),"N","Y")

--
Regards

Roger Govier


"JICDB" <[email protected]> wrote in message
news:[email protected]...
> Thanks but it didn't work for some reason. I get FALSE in every
> instance
> even if the data is located in the non-school day column.
>
> "Roger Govier" wrote:
>
>> HI
>> Try
>> =ISNUMBER(MATCH(C2,L12:L34,0))
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "JICDB" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have been searching for over an hour for a solution to my problem.
>> >This
>> > post is the closest to my issue but slightly different. Instead of
>> > showing
>> > True or False I want it to show "Y" or "N".
>> >
>> > Example:
>> > I am trying to answer the question is this a school day.
>> > I have a date in cell C2 that I comparing with a list of non-school
>> > days at
>> > I12:I34 (it's one column). If the date matches something in the
>> > list
>> > I want
>> > a "N" to appear for NON-School Day. If the date does not match it
>> > must be a
>> > school day so I want to see "Y".
>> >
>> > Any help would be greatly appreciated.
>> >
>> >
>> > "Remote Desktop Connection hotkey" wrote:
>> >
>> >> 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
>>
>>
>>

I'm just getting back here to check your message. I did get it to work in
sort of a convaluted way -
=IF(ISNA(VLOOKUP(C3,$I$12:$I$34,1,FALSE)),"S","N"). I'm sure your way is
better more logical so I will try it. Thanks for your quick response and
help!

"Roger Govier" wrote:

> Hi
>
> It returns True or False for me depending upon whether the date is the
> list or not.
> Are you using proper Excel dates, or are they Text representations of
> the date.
> If you type =C2+1 and format the cell as date, do you get a date which
> is one day greater than the date in C2?
> similarly, what happens if you add 1 to each of the date values in
> L12:L34.
>
> To return what you are looking for in terms of "Y or "N" than the
> formula needs to be
> =IF(ISNUMBER(MATCH(C2,L12:L34,0)),"N","Y")
>
> --
> Regards
>
> Roger Govier
>
>
> "JICDB" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks but it didn't work for some reason. I get FALSE in every
> > instance
> > even if the data is located in the non-school day column.
> >
> > "Roger Govier" wrote:
> >
> >> HI
> >> Try
> >> =ISNUMBER(MATCH(C2,L12:L34,0))
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "JICDB" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have been searching for over an hour for a solution to my problem.
> >> >This
> >> > post is the closest to my issue but slightly different. Instead of
> >> > showing
> >> > True or False I want it to show "Y" or "N".
> >> >
> >> > Example:
> >> > I am trying to answer the question is this a school day.
> >> > I have a date in cell C2 that I comparing with a list of non-school
> >> > days at
> >> > I12:I34 (it's one column). If the date matches something in the
> >> > list
> >> > I want
> >> > a "N" to appear for NON-School Day. If the date does not match it
> >> > must be a
> >> > school day so I want to see "Y".
> >> >
> >> > Any help would be greatly appreciated.
> >> >
> >> >
> >> > "Remote Desktop Connection hotkey" wrote:
> >> >
> >> >> 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
> >>
> >>
> >>
>
>
>