Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problems with XL's 'MATCH' function

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

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: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on Problems with XL's 'MATCH' function Or get search suggestion and latest updates.




Tagged: