Multiple Match Function

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.


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

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

This entry was posted in Excel, UDFs, VBA. Bookmark the permalink.

5 Responses to Multiple Match Function

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

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

  3. Rob says:

    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?


    • dougaj4 says:

      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.


  4. david says:

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s