The Excel function MATCH() will find the position of the first occurrence of a specified number or string in a range of data, but what if you want to find the first row in a table that contains two or more different matching numbers? There may be some way to do this with a fancy array function, but I couldn’t think of one, so I wrote the UDF MMATCH(MatchValues, DataRange).

MatchValues is a single column or single row range containing the values that you are looking for. Datarange is the range that will be searched, which may be one or more rows. MMATCH returns the number of the first row that contains all of the values in MatchValues, in any order, or zero if there are no matching rows.

Download Mmatch.zip

The screenshot below shows the function in use. The edit line shows the contents of cell K2, which returns the first row containing all of 83, 17, 92, and 84, which is row 7. In Column I the function has been entered to look at a single row, returning 1 if it contains all the match values, or 0 if not.

MMatch Function

The code is shown below, and is also viewable on the download file:

Function MMatch(MatchValues As Variant, Datarange As Variant)

Dim NumRows As Long, NumCols As Long, NumMatch As Long, i As Long, j As Long, k As Long

Dim Matches As Long

MatchValues = MatchValues.Value

Datarange = Datarange.Value

NumRows = UBound(Datarange)

NumCols = UBound(Datarange, 2)

If UBound(MatchValues, 2) > 1 Then

MatchValues = WorksheetFunction.Transpose(MatchValues)

End If

NumMatch = UBound(MatchValues)

For i = 1 To NumRows

Matches = 0

For j = 1 To NumMatch

For k = 1 To NumCols

If MatchValues(j, 1) = Datarange(i, k) Then

Matches = Matches + 1

Exit For

End If

Next k

If Matches < j Then Exit For

If Matches = NumMatch Then

MMatch = i

Exit Function

End If

Next j

Next i

MMatch = 0

End Function

### Like this:

Like Loading...

*Related*

Pingback: Weekly Excel Links - After a Long Time Edition | excel links | Pointy Haired Dilbert - Chandoo.org

Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

This is very useful. Am I right in thinking that this will take either rows or columns as the input for MatchValues but that it will only match on the rows within DataRange?

LikeLike

Rob, yes, the data being searched must be in rows (as for the Excel built in Match function), but the Match values can be in a single row or column.

LikeLike

Is it possible to get extra function, get the count of the match, even if there is not a full match .

LikeLike