Elements and Arrays in Excel
 

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

Elements and Arrays in Excel
 

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

Elements and Arrays in Excel
 

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

Elements and Arrays in Excel
 

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

Elements and Arrays in Excel
 

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

Elements and Arrays in Excel
 

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

Elements and Arrays in Excel
 

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