Search:

# Problems with XL's 'MATCH' function

Asked By: Fern    Date: Aug 18    Category: MS Office    Views: 1597

At work I have to 'match' tens of thousands of daily prices.

The most likely 'match' is on the current row with the next most
likely on the row before or the row after and so on.

Sadly, XL's MATCH function doesn't work like this. It is purely
linear, so far as I am aware and I need a sort of nested solution.

I have tried to generate a UDF (shown below). While it sort of works,
astonishingly slowly, it is also extremely inelegant!

Any advice? I can live with the inelegance but not the inefficiency.

Public Function mat()
Dim I As Variant
Dim Count As Integer
Dim rLamda, rICON As Range
Const K = 24
Dim RowOffset(K) As Integer
Dim Match As Boolean

RowOffset(0) = 0
RowOffset(1) = -1
RowOffset(2) = 1
RowOffset(3) = -2
RowOffset(4) = 2
RowOffset(5) = -3
RowOffset(6) = 3
RowOffset(7) = -4
RowOffset(8) = 4
RowOffset(9) = -5
RowOffset(10) = 5
RowOffset(11) = -6
RowOffset(12) = 6
RowOffset(13) = -7
RowOffset(14) = 7
RowOffset(15) = -8
RowOffset(16) = -9
RowOffset(17) = -10
RowOffset(18) = -11
RowOffset(19) = -12
RowOffset(20) = -13
RowOffset(21) = -14
RowOffset(22) = -15
RowOffset(23) = -16
RowOffset(24) = -17

Set rLamda = ActiveCell.Offset(0, -3)
For Each I In RowOffset
Set rICON = ActiveCell.Offset(I, -1)
If rLamda.Value = rICON.Value Then
mat = I
Match = True
Exit For
Else
mat = 99
Match = False
End If
Next I
End Function

Share:

Tagged: