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

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