VLookup not working in VBA

D

DonEB

Board Regular
Joined
Apr 26, 2016
Messages
133
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I'm attempting to do a VLookup in a macro while using the Match function to dynamically determine which column to look in.

I did some testing by sending values to a Msgbox to validate I am passing the correct values.
Ex: MsgBox "Match Value is : " & Match1 <== the value was 13
MsgBox "Lookup Value is : " & Sheet2.Range("A2").Value <== the value was 8

In both both of the above examples, the value that I expected to be passed were. However, the value I am expected to be "Found" using the VLoopup was not correct.

Match1 = WorksheetFunction.Match(Sheet2.Range("B2").Value, Range("N1:U1"), 0) + 3
FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), Match1, False)

Note: Expected results is ==> 0.44444

I'm actually coming back with the number "0".

Section of Table Used to Lookup:
NOTES: (1) the real table does extend beyond to column U and down to row 10
(2) the ONLY difference is that I changed the names. All numerical values are the same.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dent[/TD]
[TD]Bulls[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]Doe[/TD]
[TD].75[/TD]
[TD].33333[/TD]
[TD].33333[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]Zully[/TD]
[TD].66[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]Hill[/TD]
[TD].75[/TD]
[TD].33333[/TD]
[TD].33333[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7[/TD]
[TD]Smith[/TD]
[TD].66[/TD]
[TD].25[/TD]
[TD].25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8[/TD]
[TD]Dent[/TD]
[TD].01[/TD]
[TD][/TD]
[TD].44444[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]9[/TD]
[TD]Vails[/TD]
[TD].8[/TD]
[TD].25[/TD]
[TD].25[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone see what I'm doing wrong? Your help would be appreciated.

Thank you.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Unfortunately, your example is not complete.
I can't see the values ​​in cells A2, B2 or the values ​​in cells N1:U1

It is not necessary to increase the number of columns.
The following should return the column number starting at 1 column K.
Match1 = WorksheetFunction.Match(Sheet2.Range("B2").Value, Range("K1:U1"), 0)

So if the searched value is in column O, the result will be 5.
Match1 = 5

Then

FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), Match1, False)
FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), 5, False)
FoundValue = .44444

I guess in A2 you have the value "Dent"

If not, you can put all the data they play.
 
Upvote 0
DanteAmor said:
Unfortunately, your example is not complete.
I can't see the values ​​in cells A2, B2 or the values ​​in cells N1:U1

It is not necessary to increase the number of columns.
The following should return the column number starting at 1 column K.
Match1 = WorksheetFunction.Match(Sheet2.Range("B2").Value, Range("K1:U1"), 0)

So if the searched value is in column O, the result will be 5.
Match1 = 5

Then

FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), Match1, False)
FoundValue = Application.WorksheetFunction.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), 5, False)
FoundValue = .44444

I guess in A2 you have the value "Dent"

If not, you can put all the data they play.
Click to expand...

Thank you for your response. I made sure that my code matched what you showed above and, sadly, it did not work. The value that came back was the number "0"

I am providing a "copy" of the code I am using below:
Dim Perc1 As Long
Dim Match1 As Long

Match1 = Application.WorksheetFunction.Match(Sheet2.Range("B2").Value, Sheet2.Range("K1:U1"), 0)
Perc1 = Application.VLookup(Sheet2.Range("A2").Value, Sheet2.Range("K3:U10"), Match1, False)

MsgBox ("The Match lookup value is: " & Sheet2.Range("B2")) '==> value found was 13
MsgBox ("The Vlookup value is: " & Sheet2.Range("A2")) '==> value found was 8
MsgBox ("The Match value is: " & Match1) '==> value found was 5
MsgBox ("The lookup value is: " & Perc1) '==> value found was 0

I only have two pages in this worksheet. They are called Sheet1 and Sheet2. Sheet2 is the one that contains the data which I am trying to perform the lookup on.

With hopes you may still be able to discover what might be wrong, here are the answers to your questions:
The value in A2 = 8
The value in B2 = 13
The values in N1:U1 are shown in the table below:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]13[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]11[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dent[/TD]
[TD]Hicks[/TD]
[TD]Zully[/TD]
[TD]Smith[/TD]
[TD]Doe[/TD]
[TD]Hill[/TD]
[TD]Jones[/TD]
[TD]Vails[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]Doe[/TD]
[TD].75[/TD]
[TD].3333[/TD]
[TD].3333[/TD]
[TD].1111[/TD]
[TD].1111[/TD]
[TD][/TD]
[TD]0[/TD]
[TD].3333[/TD]
[TD].2222[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]Zully[/TD]
[TD].6667[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD].25[/TD]
[TD].25[/TD]
[TD].25[/TD]
[TD].25[/TD]
[TD].25[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]Hill[/TD]
[TD].75[/TD]
[TD].3333[/TD]
[TD].3333[/TD]
[TD].3333[/TD]
[TD].6667[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7[/TD]
[TD]Smith[/TD]
[TD].6667[/TD]
[TD].25[/TD]
[TD].25[/TD]
[TD].25[/TD]
[TD][/TD]
[TD].25[/TD]
[TD].5[/TD]
[TD].25[/TD]
[TD].25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8[/TD]
[TD]Dent[/TD]
[TD].0001[/TD]
[TD][/TD]
[TD].4444[/TD]
[TD]0[/TD]
[TD].1111[/TD]
[TD].3333[/TD]
[TD].1111[/TD]
[TD].3333[/TD]
[TD].2222[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]9[/TD]
[TD]Vails[/TD]
[TD].8[/TD]
[TD].25[/TD]
[TD].25[/TD]
[TD].125[/TD]
[TD].125[/TD]
[TD].25[/TD]
[TD]0[/TD]
[TD].5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]11[/TD]
[TD]Jones[/TD]
[TD].7692[/TD]
[TD].3[/TD]
[TD].3[/TD]
[TD].1[/TD]
[TD].1[/TD]
[TD].3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD].4[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]13[/TD]
[TD]Hicks[/TD]
[TD].0001[/TD]
[TD].4444[/TD]
[TD][/TD]
[TD]0[/TD]
[TD].1111[/TD]
[TD].3333[/TD]
[TD].1111[/TD]
[TD].3333[/TD]
[TD].2222[/TD]
[/TR]
</tbody>[/TABLE]

I've done plenty of Lookups in Excel before but this is not making any sense to me. I am baffled to say the least.

If there is anything else you or anyone else can suggest... I would appreciate it.

Thanks,
Don
 
Upvote 0
I would never use vlookup in vbA, it is easier and more flexible to do it using varianta rrays and a few loops:
try this:
Code: 
' load all the data into variant array
inarr = Range("K1:U10")
vb2 = Sheet2.Range("B2")
va2 = Sheet2.Range("A2")
 For i = 1 To 11
  If inarr(1, i) = vb2 Then
  Exit For ' we have found the column
  End If
 Next i
 For j = 1 To 10
  If inarr(j, 1) = va2 Then
  Exit For ' we have found the row
  End If
 Next j
MsgBox inarr(j, i)
 
Upvote 0
offthelip said:
I would never use vlookup in vbA, it is easier and more flexible to do it using varianta rrays and a few loops:
try this:
Code: 
' load all the data into variant array
inarr = Range("K1:U10")
vb2 = Sheet2.Range("B2")
va2 = Sheet2.Range("A2")
 For i = 1 To 11
  If inarr(1, i) = vb2 Then
  Exit For ' we have found the column
  End If
 Next i
 For j = 1 To 10
  If inarr(j, 1) = va2 Then
  Exit For ' we have found the row
  End If
 Next j
MsgBox inarr(j, i)
Click to expand...
Your approach works GREAT... thanks. I do have one additional question/problem that is directly related. I would like to place the results of the "lookup" into another array but I'm not having any luck doing so. Is that possible? I've provided my code below. I replaced my original VLoopup with the code you provided. That part works great... I love learning new things. Thanks!

Dim MyArray(5, 5) As Long 'defines array as being 5 rows, 5 columns
Dim X As Long
Dim Y As Long
For X = 1 To 5 Step 1 ' Step thru each row
For Y = 1 To 5 Step 1 ' For each roww, step thru each column

inarr = Range("K1:U10")
vb2 = Sheet2.Range("B2")
va2 = Sheet2.Range("A2")
For i = 1 To 11
If inarr(1, i) = vb2 Then
Exit For ' we have found the column
End If
Next i
For j = 1 To 10
If inarr(j, 1) = va2 Then
Exit For ' we have found the row
End If
Next j

If Y < 5 Then
MyArray(X, Y) = Cells(X + 1, Y).Value 'placing values in a table located in A2:D6 into an array to be used in the future
Else
MyArray(X, Y) = inarr(j, i) 'placing value found using "lookup" in the 5th position of the same array to be used in the future

MsgBox ("This is my array value: " & inarr(j, i)) ' this put the value of .44444 in the message box --- two thumbs up!!
MsgBox ("This is my array value: " & MyArray(X, Y)) ' this resulted in the value of 0 in the message box --- don't understand why??
End If
Next Y
Next X
 
Upvote 0
Can you describe the whole problem because it is not clear from your code what you are trying to do. Your code would appear to load the same value (inarr(j,i) into all positions in the Myarray up until column 5 where it load the values from column 5 of the worksheet. you aren't doing anything with Myarray after it is loaded, so what result do you expect.
 
Upvote 0
DonEB said:
Thank you for your response. I made sure that my code matched what you showed above and, sadly, it did not work. The value that came back was the number "0"

I am providing a "copy" of the code I am using below:
Dim Perc1 As Long 'Long data type to contain integer numbers
Dim Match1 As Long
Click to expand...

The problem is the declaration of the Perc1 variable, since the result can be a number or a text or a decimal. It is solved if you declare the variable as variant.
Unlike the Match variable, the result is a column number, so it can be declared as Long.



Code: 
Sub test()
  Dim Match1 As Long,[COLOR=#ff0000] Perc1 As Variant[/COLOR]
  Dim sh2 As Worksheet
  Set sh2 = Sheets("Sheet2")
  Match1 = Application.WorksheetFunction.Match(sh2.[B2], sh2.Range("K1:U1"), 0)
  Perc1 = Application.VLookup(sh2.[A2], sh2.Range("K3:U10"), Match1, 0)
  MsgBox "The lookup value is: " & Perc1
End Sub

-----------------------------
Note: Something that made me noise, is that you mention that you have 2 sheets, but in the code or in the examples you never use sheet1, so I didn't understand why you mentioned sheet1.

So, if everything happens on the same sheet2, you can use the Index function like this:

Code: 
Sub test2()  
MsgBox WorksheetFunction.Index([K3:U10], WorksheetFunction.Match([A2], [K3:K10], 0), WorksheetFunction.Match([B2], [K1:U1], 0))
End Sub

Or this:

Code: 
Sub test3()
  MsgBox Evaluate("Index(K3:U10,Match(A2,K3:K10,0),Match(B2,K1:U1,0))")
End Sub

------------------------------

If you don't want to use formulas, I recommend the Find method:

Code: 
Sub test4()
  Dim f1 As Range, f2 As Range
  Set f1 = Sheets("Sheet2").Range("K1:U1").Find(Range("B2"), , xlValues, xlWhole)
  Set f2 = Sheets("Sheet2").Range("K3:K10").Find(Range("A2"), , xlValues, xlWhole)
  MsgBox Cells(f2.Row, f1.Column)
End Sub
 
Last edited:
Upvote 0
offthelip said:
Can you describe the whole problem because it is not clear from your code what you are trying to do. Your code would appear to load the same value (inarr(j,i) into all positions in the Myarray up until column 5 where it load the values from column 5 of the worksheet. you aren't doing anything with Myarray after it is loaded, so what result do you expect.
Click to expand...

Here is what I am trying to accomplish:
  • Table A (A2:D6) contains potential combination of matchups for a Doubles Tennis match
  • Table B (K1:U10), shown in a previous example, contains current % of times players played with/against each other
  • As I loop thru the various combinations found in Table A and use the values in Table B to determine which of the potential combinations has played the least with each other. It is this combination that will be used to set up a tennis match.

Previously, I just disclosed the value of A2 = 8 and B2 = 13. Below is the example of what Table A look like, there are no values in row 1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Player1[/TD]
[TD]Player2[/TD]
[TD]Player3[/TD]
[TD]Player4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8[/TD]
[TD]13[/TD]
[TD]4[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]13[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]8[/TD]
[TD]11[/TD]
[TD]4[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]13[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

My intent was to do the following:
  1. Loop thru Table A and create an array that consisted of all the potential combination of players
  2. Add to the array created above the value found by the lookup in Table B. However, I will ultimately be performing that same lookup for each of the players in each of the combinations. Ex: In my previous example, we were looking up A2 by using the column found by the player in B2. I would then continue the loop by looking at the column found by the player in C2 and then in D2.
  3. Ultimately, I will want to then find the lowest combined value for each row in Table A.

You are 100% correct in your observation that my code currently only loads the same value (inarr(j,i) into all positions in the Myarray up until column 5. My hope was to simply learn how to find the value and then get it appropriately placed into an array. Once that is done... then I was going to try and expand that to reflect my intentions I expressed above.

Does this make since now? I know what I want.. not sure if it is coming across correctly.

I do appreciate you assistance.

Thank you,
Don
 
Upvote 0
I think something like this is what you want:
Code: 
Sub test2()




Dim MyArray(5, 5) As Long 'defines array as being 5 rows, 5 columns
Dim X As Long
Dim Y As Long
inarr = Range("K1:U10") ' move this outside the loop so that it only executes once
'load all the players data
players = Range("A2:D6")
For X = 1 To 5 Step 1 ' Step thru each row
For Y = 2 To 5 Step 1 ' For each roww, step thru each column


vb2 = players(X, Y)
va2 = players(X, 1)
For i = 1 To 11
If inarr(1, i) = vb2 Then
Exit For ' we have found the column
End If
Next i
For j = 1 To 10
If inarr(j, 1) = va2 Then
Exit For ' we have found the row
End If
Next j


MyArray(X, Y) = inarr(j, i) 'placing value found using "lookup" in the 5th position of the same array to be used in the future
MsgBox ("This is my array value: " & MyArray(X, Y))
Next Y
Next X
End Sub
 
Upvote 0
offthelip said:
I think something like this is what you want:
Code: 
Sub test2()




Dim MyArray(5, 5) As Long 'defines array as being 5 rows, 5 columns
Dim X As Long
Dim Y As Long
inarr = Range("K1:U10") ' move this outside the loop so that it only executes once
'load all the players data
players = Range("A2:D6")
For X = 1 To 5 Step 1 ' Step thru each row
For Y = 2 To 5 Step 1 ' For each roww, step thru each column


vb2 = players(X, Y)
va2 = players(X, 1)
For i = 1 To 11
If inarr(1, i) = vb2 Then
Exit For ' we have found the column
End If
Next i
For j = 1 To 10
If inarr(j, 1) = va2 Then
Exit For ' we have found the row
End If
Next j


MyArray(X, Y) = inarr(j, i) 'placing value found using "lookup" in the 5th position of the same array to be used in the future
MsgBox ("This is my array value: " & MyArray(X, Y))
Next Y
Next X
End Sub
Click to expand...

I believe everything is working well except the assignment of the value to the array.

To determine where they problem is, I did the following MsgBox right below the "Next j" code.
MsgBox ("This is my array value: " & inarr(j, i))
This message box produced all the values I would expect to be found and placed into the array.

The following message box produced only "0" each time the message box appeared.
MsgBox ("This is my array value: " & MyArray(X, Y))

So... the problem seems to be the assignment of the value to the array. If this issue can be resolved then I can definitely see how the code you are providing is much more efficient than the "Vlookup" and "Match" I was attempting to use.

Do you have any additional thoughts on this?

Again, thanks for your continued effort. The code you have provided thus far has taught me a lot... and it is appreciated.

Thanks,
Don
 
Upvote 0
You must log in or register to reply here.

Similar threads

megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
271
etaf
etaf
Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
378
Alex Blakenburg
A
P
  • Solved
Excel VBA, Vlookup to another sheet in same work book
Replies
6
Views
1K
StephenCrump
S
J
  • Solved
VLookup Struggles
Replies
7
Views
444
Peter_SSs
Peter_SSs
O
  • Question Question
Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.
Replies
6
Views
2K
nemmi69
nemmi69

Forum statistics

Threads
1,223,703
Messages
6,173,972
Members
452,540
Latest member
haasro02

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top