A more versatile Match function for Excel

The Problem

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.

The Syntax is as below:
MATCH(lookup_value, lookup_array, [match_type])

The match_type is an optional argument that can take one of three values;

  • 0 Returns an exact match
  • -1 returns the lowest value that is greater than the lookup_value if an exact match cannot be found.
    For this to function, the lookup_array must be sorted in descending order.
  • 1 returns the highest value that is less than than the lookup_value if an exact match cannot be found.
    For this to function, the lookup_array must be sorted in ascending order.

The problem is that it’s often not desirable to have the lookup_array sorted in ascending or descending order. Sometimes you may want to apply a match_type of both 1 and -1 to the same lookup_array, which isn’t possible because it cannot be both sorted in ascending and descending order simultaneously.

The Solution

I’ve written a short User Defined Function called MATCHU which uses exactly the same syntax as the native MATCH function, only it doesn’t have the same limitations; it can use a match_type of 0, -1 or 1 on an unsorted lookup_array and return the index as desired.

Download

You can download the module containing this routine here.

The code for this is given below.

Option Explicit

Function MatchU(lookup_value As Range, lookup_array As Range, Optional match_type As Integer = 0)
‘ Performs the same function as the built-in Match function, with the same inputs,
‘ but modified such that the lookup_arrayerence range needn’t be sorted for the ‘Less Than’ or ‘Greater Than’ functionality to work.

‘ lookup_value is the target value to look up. This may be a string or numeric.
‘ If the target is a string then the ‘Less than’ or ‘Greater than’ values will not function – only an exact match can be returned.

‘ lookup_array is the lookup_arrayerence range that the Target value will be compared against.

‘ Outpout is an optional arugment that may be used to indicate that – in the event that an exact match is not found – the nearest value less than the target
‘ value will be returned (enter 1 for this functionality), or the nearest value greater than the target is returned (enter -1 for this functionality).
‘ These are the same functionality and arguments used by the native Match function.
‘ By default the match_type argument has value 0, indicating that an exact match is to be returned.

‘ Written by Dan Ashby
‘ Last updated 11 March 2017

Dim Val As Range
Dim Low As Variant
Dim High As Variant
Dim Exact As Variant
Dim i As Integer
Dim iLow As Integer
Dim iHigh As Integer
Dim iExact As Integer

‘ Check validity of the match_type value entered
If match_type <> 0 And match_type <> 1 And match_type <> -1 Then
MatchU = “Invalid match_type argument entered. Enter 0 for exact match, 1 to return the index of the value nearest to but less than the target, or -1 for the index of the value nearest to but not greater than the target.”
Exit Function
ElseIf (match_type = 1 Or match_type = -1) And Not IsNumeric(lookup_value) Then
MatchU = “Invalid match_type argument entered. The Target value is not numeric, so only an exact match can be found; Less Than/Greater Than functionality does not work. Enter match_type value of 0 to search for exact match.”
Exit Function
End If

For Each Val In lookup_array ‘ Loop through the values in the lookup_arrayerence range
i = i + 1
If Not IsNumeric(lookup_value) Then ‘ If the Target isn’t numeric, then Greater Than/Less Than functionality fails and is bypassed – just look for an exact match
If Val.Value = lookup_value.Value Then
MatchU = i ‘ Exact match has been found – return the match index number and exit the function
Exit Function
End If
ElseIf IsNumeric(Val) Then ‘ lookup_value has previously been established to be numeric. Thelookup_arrayore only proceed with comparison of Val and lookup_value if the Val is also numeric (i.e. bypass non numeric values in the lookup_arrayerence range)
Select Case Val.Value
Case Is = lookup_value.Value ‘ Exact match has been found – return the match index number and exit the function
MatchU = i
Exit Function
Case Is < lookup_value.Value
If IsEmpty(Low) Then ‘ If this is the first value encountered that is lwoer than the target, then set High as this value
Low = Val.Value
iLow = i
ElseIf lookup_value.Value – Val.Value < lookup_value.Value – Low Then ‘ Else compare to see if the current value is a nearer match to the target than the previously found Low value
Low = Val.Value
iLow = i
End If
Case Is > lookup_value.Value
If IsEmpty(High) Then ‘ If this is the first value encountered that is higher than the target, then set High as this value
High = Val.Value
iHigh = i
ElseIf Val.Value – lookup_value.Value < High – lookup_value.Value Then ‘ Else compare to see if the current value is a nearer match to the target than the previously found High value
High = Val.Value
iHigh = i
End If
End Select
End If
Next Val

Select Case match_type
Case Is = 1 ‘ Report the index of the value closest to but not greater than the target
If IsEmpty(Low) Then
MatchU = “No values less than the target were found”
Else
MatchU = iLow
End If
Case Is = -1 ‘ Report the index of the value closest to but not less than the target
If IsEmpty(High) Then
MatchU = “No values greater than the target were found”
Else
MatchU = iHigh
End If
Case Else ‘ Report index of the exact match, if found
If IsEmpty(Exact) Then
MatchU = “Exact match not found”
Else
MatchU = iExact
End If
End Select
End Function

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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