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.
<tbody>
</tbody>
Can anyone see what I'm doing wrong? Your help would be appreciated.
Thank you.
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.
K | L | M | N | O | |
1 | 8 | 13 | |||
2 | Dent | Bulls | |||
3 | 3 | Doe | .75 | .33333 | .33333 |
4 | 4 | Zully | .66 | 0 | 0 |
5 | 5 | Hill | .75 | .33333 | .33333 |
6 | 7 | Smith | .66 | .25 | .25 |
7 | 8 | Dent | .01 | .44444 | |
8 | 9 | Vails | .8 | .25 | .25 |
<tbody>
</tbody>
Can anyone see what I'm doing wrong? Your help would be appreciated.
Thank you.