Search column for value and return TRUE or FALSE

[Guest]

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]

=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"

 

[Guest]

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

 

[Aladin Akyurek]

=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.

 

[Guest]

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.

 

[Roger Govier]

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

 

[Guest]

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]

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")

 

[Guest]

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!