VLookup not working in VBA

[DonEB]

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.

 

[DanteAmor]

Did you read post #7 ? with a single line you can find the value.

Do you want all the A-B, A-C, A-D, B-C, B-D and C-D combinations, or only the A-B and C-D combinations?

You want the result of all the combinations in an arrangement or better yet, in Excel cells. Or you just want a result.
Why not better raise everything you have, what you want to do and what you want as a result.
The above is because you started with a simple search that was solved because you were not correctly declaring a variable, but now we are going in a loop of combinations, but you still do not ask what your final objective is.

 

[offthelip]

Your problem is you defined myarray as type long, msgbox only types out strings, so the assignment to myarray is perfectly ok ( I think) I think the problem is just with your method of looking at it.
try this:

dim tt as string


tt = cstr(myarray(X,y)
MsgBox ("This is my array value: " & tt)

or faster try looking at myarray on the locals window.

 

[offthelip]

I finally got back to my computer and solved your problem you need to change the declaration of myarray
to:

Dim MyArray(1 To 5, 1 To 5) As Variant