Search column for value and return TRUE or FALSE
 

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

Search column for value and return TRUE or FALSE
 

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 (its 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

Search column for value and return TRUE or FALSE
 

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

--
Regards

Roger Govier


"JICDB" wrote in message
...
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

Search column for value and return TRUE or FALSE
 

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

Search column for value and return TRUE or FALSE
 

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

Search column for value and return TRUE or FALSE
 

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