Elements and Arrays in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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")
 
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!
 
If it doesn't work, post back with an example of what your data looks like
and what you want the function to do.
 
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.
 
Back
Top