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.

No.

Looping through an array is very fast.

--
Regards,
Tom Ogilvy

"KR" <nospam@nospam.com> wrote in message
news:uSkatzjLFHA.2824@TK2MSFTNGP10.phx.gbl...
> 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.
>
>