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
Newton Excel Bach, not (just) an Excel Blog 