### 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 2017Dim 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 IfFor 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 ValSelect 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