VBA/Excel: why does the match() method not work fo

Question

Playing around with the match() method I found out that match() only works for search ranges referencing single rows and columns (e.g. B1:B5 or A2:F2) (one dimensional ranges) not for a range like (A1:E5) (two dimensional range).

Why is that? As far as I understand both "types" of ranges are essentially arrays/array-references (which is the only thing required by Microsoft's reference). Is it my understanding or does the match() method work funky?

I tried to research this issue myself, but there is nothing pointing to that issue in the reference (as SJR points out correctly).

Here's the code where I get an error as a result. Using e.g. B1:B5 works fine. The value I'm looking for is in cell B2.

'Set Search Range
    Dim rngSearchRange As Range
    Set rngSearchRange = ThisWorkbook.Worksheets(1).Range("A1:E5")

'Set Search Term  
    Dim searchTerm As String
    searchTerm = "test"

'Set Compare Type
    Dim compareType As Integer
    compareType = 0

'Execute Search
    Dim found As Variant
    found = Application.Match(searchTerm, rngSearchRange, compareType)

Note: This is a followup question on this question. Using find() is not an option at least not in a general case since find() depends on the presentation of the value.

Answer 1

The way MATCH is designed is to return the number of the match in one specified direction. By default, it counts the array in one direction, and returns which count the match occurs. If there were more dimensions to the array, it couldn't return a single number as a match, since it would need to specify which row and column in the array the match was found, rather than just which cell matched. Example data:

a   b   c   d   e
f   g   h   i   j

If you want to find value i, it would return 4 when searching the row, or 2 when searching the column. When searching the range, it would need to return both 4 and 2 simultaneously, which is not what it is designed to do.

In this case, it would be better to use the Find formula for this, as this would return a range object (direct reference to a cell) from which either the cell content or its address can be derived.

Edit: One could argue that it could return either 8 or 9, if it continued counting your range either per column or row. However this would complicate the formula exponentially and make much more room for unexpected results. I would assume the design team decided to make it as easy to use as possible so most base excel users can use the formula without any advanced logic knowledge.

Answer 2

Match looks through a one-dimensional array and returns the matching value's position (not a range, address, etc.). Match only returns a number indicating the position. For what you're trying to do Find should do the trick.