Match function? VBA not working properly

Question

I have a following code and i have "name" in cells B11 , B19 and B25 and i was expecting a result of 19 or 25 with the code but it returns Y=1. Could anyone tell me what is the problem?.

Sub mat()

Y = Application.Match("name", ActiveSheet.Range("B19:B30"), 0)
MsgBox Y

End Sub

Solution 1

According to the documentation for MATCH:

MATCH returns the position of the matched value within lookup_array, not the value itself.

and with 0as the optional third argument (match_type):

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

So the returned 1refers to the position on B19in the array Range("B19:B30") and the code sample is indeed behaving as expected.

Solution 2

Application.Match("name", ActiveSheet.Range("B19:B30"), 0)

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range

So parsing the parameter ActiveSheet.Range("B19:B30") means that B19 equals relative position =1.