VBA equivalent of VLookup on an array
 

I have a large array:

Public TotalDataArray(1 To 6, 1 To 30, 1 To 65000) As Variant

(I'll shorten that last parameter once I know the maximum number of lines
that the source query can return, but it will still be big- the last report
I got was accidently truncated at 34K rows, so the total number will still
be very large)

I'm cycling through a sheet and pulling numbers off a particular column that
I need to check against the array; if that number is found, I need to know
where so I can pull a related number from the array. Example:

TotalDataArray(5,18,1) = cat
TotalDataArray(5,18,2) = dog
TotalDataArray(5,18,3) = bird
TotalDataArray(5,18,4) = worm

in a target cell on my worksheet I find "dog", which (when I figure out how
to find the match) will return (5,18,2). I need to know the "2" so I can
pull back out:
(5,15,2) = vertibrate, and (5,16,2) = mammal

My curent level of sophistication would be to cycle through all (up to)
65000 elements to look for a match, and do this for each of the hundreds of
cells in my file. the problem is I have to do this with different parts of
my array, for a total of about 15 times...so I'm worried this will end up
taking forever to run.

Is there an easier way to detect the location/match in one dimension of a
3-D array, other than cycling through each element?

Many thanks,
Keith


--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.