Hello,

I have a multidimensional array that is sorted on the first column and was
trying to use the VLOOKUP to search for particular entries. VLOOKUP doesn't
seem to reconize arrays. Has anybody been able to incorporate a faster
search other than scanning the array by looping through iterative times?

Any help is appreciated.

Dean.

Vlookup supports a two dimensional array

Just a quick demo:

Sub TestVlookup()
Dim v() As Variant
ReDim v(1 To 20, 1 To 5)
For i = 1 To 20
For j = 2 To 4
v(i, j) = Int(Rnd() * 1000 + 1)
Next
v(i, 1) = i
v(i, 5) = Chr(64 + i)
Next
res = Application.VLookup(12, v, 5, False)
MsgBox res
End Sub



--
Regards,
Tom Ogilvy

"Dean Hinson" wrote in message
...
Hello,

I have a multidimensional array that is sorted on the first column and was
trying to use the VLOOKUP to search for particular entries. VLOOKUP
doesn't
seem to reconize arrays. Has anybody been able to incorporate a faster
search other than scanning the array by looping through iterative times?

Any help is appreciated.

Dean.

Dean Hinson wrote:
Hello,

I have a multidimensional array that is sorted on the first column and was
trying to use the VLOOKUP to search for particular entries. VLOOKUP doesn't
seem to reconize arrays. Has anybody been able to incorporate a faster
search other than scanning the array by looping through iterative times?

Any help is appreciated.

Dean.
When you say "search for particular entries", do you mean search for
particular entries corresponding to a specified value in the first
"column" of the array? If so, Tom Ogilvy has posted a sample with
correct syntax. Otherwise you need to be a little more specific about
what you're trying to search for.

Alan Beban

Thank you Tom. I think that will do the trick. I saw some documentation on
the REDIM but didn't understand it's full potential.

"Tom Ogilvy" wrote:

Vlookup supports a two dimensional array

Just a quick demo:

Sub TestVlookup()
Dim v() As Variant
ReDim v(1 To 20, 1 To 5)
For i = 1 To 20
For j = 2 To 4
v(i, j) = Int(Rnd() * 1000 + 1)
Next
v(i, 1) = i
v(i, 5) = Chr(64 + i)
Next
res = Application.VLookup(12, v, 5, False)
MsgBox res
End Sub



--
Regards,
Tom Ogilvy

"Dean Hinson" wrote in message
...
Hello,

I have a multidimensional array that is sorted on the first column and was
trying to use the VLOOKUP to search for particular entries. VLOOKUP
doesn't
seem to reconize arrays. Has anybody been able to incorporate a faster
search other than scanning the array by looping through iterative times?

Any help is appreciated.

Dean.

Tom, I tried but get a type mismatch error. Here's what I am doing....

Dim arrUserIds As Variant
Dim arrUserNames As Variant
Dim arrDepartments As Variant
..
Load Sub-Routine...
Dim Preserve arrUserIds(1 To 5000, 2)
Dim Preserve arrUserNames(1 To 5000, 2)
Dim Preserve arrDepartments(1 To 5000, 2)
Code to load array...
..
Display Routine....
ReDim Preserve arrUserIds(1 To 5000, 2)
ReDim Preserve arrUserNames(1 To 5000, 2)
ReDim Preserve arrDepartments(1 To 5000, 2)

xTest = Application.VLookup(cbUserIds.Text, arrUserIds, 2, False)

cbUserIds.Text is "tie4ch", the arrUserIds is loaded with userids and names.

What am I doing wrong?

"Tom Ogilvy" wrote:

Vlookup supports a two dimensional array

Just a quick demo:

Sub TestVlookup()
Dim v() As Variant
ReDim v(1 To 20, 1 To 5)
For i = 1 To 20
For j = 2 To 4
v(i, j) = Int(Rnd() * 1000 + 1)
Next
v(i, 1) = i
v(i, 5) = Chr(64 + i)
Next
res = Application.VLookup(12, v, 5, False)
MsgBox res
End Sub



--
Regards,
Tom Ogilvy

"Dean Hinson" wrote in message
...
Hello,

I have a multidimensional array that is sorted on the first column and was
trying to use the VLOOKUP to search for particular entries. VLOOKUP
doesn't
seem to reconize arrays. Has anybody been able to incorporate a faster
search other than scanning the array by looping through iterative times?

Any help is appreciated.

Dean.

Tom,
I figured out the type mismatch but now I am getting an error 2042 from the
VLOOKUP. What is that?

"Dean Hinson" wrote:

Hello,

I have a multidimensional array that is sorted on the first column and was
trying to use the VLOOKUP to search for particular entries. VLOOKUP doesn't
seem to reconize arrays. Has anybody been able to incorporate a faster
search other than scanning the array by looping through iterative times?

Any help is appreciated.

Dean.

An error 2042 from VLookup indicates that the value was not
found.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Dean Hinson" wrote in
message
...
Tom,
I figured out the type mismatch but now I am getting an error
2042 from the
VLOOKUP. What is that?

"Dean Hinson" wrote:

Hello,

I have a multidimensional array that is sorted on the first
column and was
trying to use the VLOOKUP to search for particular entries.
VLOOKUP doesn't
seem to reconize arrays. Has anybody been able to incorporate
a faster
search other than scanning the array by looping through
iterative times?

Any help is appreciated.

Dean.

That means the value wasn't found. error 2042 is the same as #N/A
cvErr(xlErrNA)

from the immediate window:
? cvErr(xlErrNA)
Error 2042

In your sample code, you redim preserve your arrays twice, but you don't
change anything the second time, so not sure why you are doing it again
there (not that it should make a difference).

for instance, in my example I would normally do
Dim res as Variant
..
..
..
res = Application.VLookup(12, v, 5, False)
if iserror(res) then
msgbox "12 was not found"
Else
MsgBox "Value returned is " & res
End If

The only reason I used Redim in my example is that I declared my array as
dynamic

dim v() as Variant

I could have skipped the Redim if I did

Dim v(1 To 20, 1 To 5) as Variant

so there is nothing particularly pertinent with regard to using Redim in the
example. Most of the example was spend building up a dummy array so I could
demo that Vlookup worked.

Anyway, a common problem is that a textbox produces a string value. Even if
I entered 123 in the textbox, it would return "123" (a string with 3
characters that happend to be numerals). However, if my data contains 123
stored as a number, then "123" < 123 and vlookup would fail. This doesn't
appear to be your problem because you say you are searching for "tie4ch",
which is clearly text. Since you are looking for an exact match, any
possibility the search term might have a space or two appended on the end
"tie4ch " instead of "tie4ch"?

--
Regards,
Tom Ogilvy



"Dean Hinson" wrote in message
...
Tom,
I figured out the type mismatch but now I am getting an error 2042 from
the
VLOOKUP. What is that?

"Dean Hinson" wrote:

Hello,

I have a multidimensional array that is sorted on the first column and
was
trying to use the VLOOKUP to search for particular entries. VLOOKUP
doesn't
seem to reconize arrays. Has anybody been able to incorporate a faster
search other than scanning the array by looping through iterative times?

Any help is appreciated.

Dean.