Is there a simple way to determine if a cell value is an element of an array?
I've tried Lookup, Vlookup, Match to no avail. They return false positives or
#N/A errors due to the way they test for matches. I posted this message a few
moments ago, somewhere, but it went to never-never land, I'm afraid.

Norm

One possibility is to write a wrapper function for VBA's HasArray property.
I don't know if Excel has any non-VBA solution.

Function IsArray(Rng As Range)
IsArray = Rng.HasArray
End Function




"Lighthouseman" wrote:

Is there a simple way to determine if a cell value is an element of an array?
I've tried Lookup, Vlookup, Match to no avail. They return false positives or
#N/A errors due to the way they test for matches. I posted this message a few
moments ago, somewhere, but it went to never-never land, I'm afraid.

Norm

Thanks for the reply. Sadly, my VBA is lacking. It's hard to believe that a
an "identical" lookup rather than "close to" is lacking. So much for set
theory.

"JMB" wrote:

One possibility is to write a wrapper function for VBA's HasArray property.
I don't know if Excel has any non-VBA solution.

Function IsArray(Rng As Range)
IsArray = Rng.HasArray
End Function




"Lighthouseman" wrote:

Is there a simple way to determine if a cell value is an element of an array?
I've tried Lookup, Vlookup, Match to no avail. They return false positives or
#N/A errors due to the way they test for matches. I posted this message a few
moments ago, somewhere, but it went to never-never land, I'm afraid.

Norm

My apologies, I think I misunderstood your original question. You want to
know if a cell value is included in a specified range, not whether or not a
specific cell is part of an array?

Say, for example, cell A1 contains the value 52 and you want to know if this
value appears in the range E1:E3, then


=ISNA(VLOOKUP(A1,E1:E3,1,0))

If you want to return something other than True or False, combine with an if
statement

=IF(ISNA(VLOOKUP(A1,E1:E3,1,0)),"Not Found","Found")

"Lighthouseman" wrote:

Thanks for the reply. Sadly, my VBA is lacking. It's hard to believe that a
an "identical" lookup rather than "close to" is lacking. So much for set
theory.

"JMB" wrote:

One possibility is to write a wrapper function for VBA's HasArray property.
I don't know if Excel has any non-VBA solution.

Function IsArray(Rng As Range)
IsArray = Rng.HasArray
End Function




"Lighthouseman" wrote:

Is there a simple way to determine if a cell value is an element of an array?
I've tried Lookup, Vlookup, Match to no avail. They return false positives or
#N/A errors due to the way they test for matches. I posted this message a few
moments ago, somewhere, but it went to never-never land, I'm afraid.

Norm

Yes, That is what I meant. Sorry to have not been more clear in the question.
I did try several different ways to deal with the ERROR.TYPE(7) but even
though I followed the process suggested by "help" verbatim, the function
failed to work. I shall try your suggestion. Thank you very much!
--
Norm S. (Formerly, "Lighthouseman")


"JMB" wrote:

My apologies, I think I misunderstood your original question. You want to
know if a cell value is included in a specified range, not whether or not a
specific cell is part of an array?

Say, for example, cell A1 contains the value 52 and you want to know if this
value appears in the range E1:E3, then


=ISNA(VLOOKUP(A1,E1:E3,1,0))

If you want to return something other than True or False, combine with an if
statement

=IF(ISNA(VLOOKUP(A1,E1:E3,1,0)),"Not Found","Found")

"Lighthouseman" wrote:

Thanks for the reply. Sadly, my VBA is lacking. It's hard to believe that a
an "identical" lookup rather than "close to" is lacking. So much for set
theory.

"JMB" wrote:

One possibility is to write a wrapper function for VBA's HasArray property.
I don't know if Excel has any non-VBA solution.

Function IsArray(Rng As Range)
IsArray = Rng.HasArray
End Function




"Lighthouseman" wrote:

Is there a simple way to determine if a cell value is an element of an array?
I've tried Lookup, Vlookup, Match to no avail. They return false positives or
#N/A errors due to the way they test for matches. I posted this message a few
moments ago, somewhere, but it went to never-never land, I'm afraid.

Norm

If it doesn't work, post back with an example of what your data looks like
and what you want the function to do.

"NES" wrote:

Yes, That is what I meant. Sorry to have not been more clear in the question.
I did try several different ways to deal with the ERROR.TYPE(7) but even
though I followed the process suggested by "help" verbatim, the function
failed to work. I shall try your suggestion. Thank you very much!
--
Norm S. (Formerly, "Lighthouseman")


"JMB" wrote:

My apologies, I think I misunderstood your original question. You want to
know if a cell value is included in a specified range, not whether or not a
specific cell is part of an array?

Say, for example, cell A1 contains the value 52 and you want to know if this
value appears in the range E1:E3, then


=ISNA(VLOOKUP(A1,E1:E3,1,0))

If you want to return something other than True or False, combine with an if
statement

=IF(ISNA(VLOOKUP(A1,E1:E3,1,0)),"Not Found","Found")

"Lighthouseman" wrote:

Thanks for the reply. Sadly, my VBA is lacking. It's hard to believe that a
an "identical" lookup rather than "close to" is lacking. So much for set
theory.

"JMB" wrote:

One possibility is to write a wrapper function for VBA's HasArray property.
I don't know if Excel has any non-VBA solution.

Function IsArray(Rng As Range)
IsArray = Rng.HasArray
End Function




"Lighthouseman" wrote:

Is there a simple way to determine if a cell value is an element of an array?
I've tried Lookup, Vlookup, Match to no avail. They return false positives or
#N/A errors due to the way they test for matches. I posted this message a few
moments ago, somewhere, but it went to never-never land, I'm afraid.

Norm

Your solution worked perfectly!! Thanks so very much. The purpose of this was
to select specific telephone lines (based upon phone exchanges [which are
extracted automatically]) for outbound calls in order to conserve on long
distance charges.
--
Norm S. (Formerly, Lighthouseman)


"JMB" wrote:

If it doesn't work, post back with an example of what your data looks like
and what you want the function to do.

"NES" wrote:

Yes, That is what I meant. Sorry to have not been more clear in the question.
I did try several different ways to deal with the ERROR.TYPE(7) but even
though I followed the process suggested by "help" verbatim, the function
failed to work. I shall try your suggestion. Thank you very much!
--
Norm S. (Formerly, "Lighthouseman")


"JMB" wrote:

My apologies, I think I misunderstood your original question. You want to
know if a cell value is included in a specified range, not whether or not a
specific cell is part of an array?

Say, for example, cell A1 contains the value 52 and you want to know if this
value appears in the range E1:E3, then


=ISNA(VLOOKUP(A1,E1:E3,1,0))

If you want to return something other than True or False, combine with an if
statement

=IF(ISNA(VLOOKUP(A1,E1:E3,1,0)),"Not Found","Found")

"Lighthouseman" wrote:

Thanks for the reply. Sadly, my VBA is lacking. It's hard to believe that a
an "identical" lookup rather than "close to" is lacking. So much for set
theory.

"JMB" wrote:

One possibility is to write a wrapper function for VBA's HasArray property.
I don't know if Excel has any non-VBA solution.

Function IsArray(Rng As Range)
IsArray = Rng.HasArray
End Function




"Lighthouseman" wrote:

Is there a simple way to determine if a cell value is an element of an array?
I've tried Lookup, Vlookup, Match to no avail. They return false positives or
#N/A errors due to the way they test for matches. I posted this message a few
moments ago, somewhere, but it went to never-never land, I'm afraid.

Norm