Solved: Looking for a more 'flexible' Match()

[Paul_Hossler]

I'm trying to come up with a more flexible Match () function which isn't so dependent on compatible data types

For example, if the data being searched contains a numeric 123, but the data to be Match-ed is string "123", it returns N/A

The best I've been able to come up with MyMatch() below (proof of concept only = not very polished or robust).

It just feels 'crude' and having to do 4 Matchs each time could start adding up to a performance hit. Once I get this sorted out, VLookup is next

Is there any better way?


Option Explicit
Sub test1()
Dim v As Variant

Range("A1").Value = "aaa"
Range("A2").Value = "bbb"
Range("A3").Value = #1/1/2012#
Range("A4").Value = 123
Range("A5").Value = "ddd"
Range("A6").Value = "'456"


v = "bbb"
MsgBox MyMatch(v, Range("A:A"))

v = 123
MsgBox MyMatch(v, Range("A:A"))

v = "123"
MsgBox MyMatch(v, Range("A:A"))

v = 456
MsgBox MyMatch(v, Range("A:A"))

v = "456"
MsgBox MyMatch(v, Range("A:A"))

v = #1/1/2012#
MsgBox MyMatch(v, Range("A:A"))

End Sub

Function MyMatch(v As Variant, r As Range) As Long
Dim N As Long


N = 0

On Error Resume Next
'case 1 -- 123 finds 123, and "abc" finds "abc"
N = Application.WorksheetFunction.Match(v, r.Columns(1), 0)
If N > 0 Then GoTo NiceExit
'case 2 -- "123" finds 123
If IsNumeric(v) Then N = Application.WorksheetFunction.Match(--v, r.Columns(1), 0)
If N > 0 Then GoTo NiceExit

'case 3 -- 123 finds "123"
If IsNumeric(v) Then N = Application.WorksheetFunction.Match(CStr(v), r.Columns(1), 0)
If N > 0 Then GoTo NiceExit

'case 4 -- 1/1/2012 finds 1/1/2012
If IsDate(v) Then N = Application.WorksheetFunction.Match(CLng(v), r.Columns(1), 0)

NiceExit:
On Error GoTo 0
MyMatch = N
End Function


Paul

[Bob Phillips]

Function MyMatch(v As Variant, r As Range) As Long
Dim N As Long

On Error Resume Next
N = Application.Match(CLng(v), r.Columns(1), 0)
If N = 0 Then
If IsNumeric(v) Then N = Application.WorksheetFunction.Match(--v, r.Columns(1), 0)
N = Application.Match(CStr(v), r.Columns(1), 0)
End If
MyMatch = N
End Function

[shrivallabha]

If you are looking for plain native formula which should work as well then maybe:
assuming your Lookup_Array in Col A and the Lookup_Values in Col B then following will imitate MATCH:
=LOOKUP(9.99E+307,SEARCH(B1,$A$1:$A$6,1),ROW($A$1:$A$6))
And following will imitate VLOOKUP:
=LOOKUP(9.99E+307,SEARCH(B1,$A$1:$A$6,1),$A$1:$A$6)

[Paul_Hossler]

Thanks to both

shrivallabha -- I'm going to have to think about that one to figure it out

Paul

[shrivallabha]

This formula is "pinched" from MrExcel. It was used for "partial" match but it can do "complete string" match as well.

Refer this post (http://www.mrexcel.com/forum/showpost.php?p=2920504&postcount=6). Credit to: Aladin Akyurek.

[snb]

You could convert the array/range into strings:
sub snb()
sn = Split("aaa|bbb|01/01/2012|123|ddd|456", "|")

MsgBox Application.Match("01/01/2012", sn, 0)
MsgBox Application.Match("456", sn, 0)
MsgBox Application.Match("123", sn, 0)
MsgBox Application.Match("bbb", sn, 0)
MsgBox Application.Match("aaa", sn, 0)
MsgBox Application.Match("123", sn, 0)
end sub

[snb]

You could convert the array/range to strings:
sub snb()
sn = Split("aaa|bbb|01/01/2012|123|ddd|456", "|")

MsgBox Application.Match("01/01/2012", sn, 0)
MsgBox Application.Match("456", sn, 0)
MsgBox Application.Match("123", sn, 0)
MsgBox Application.Match("bbb", sn, 0)
MsgBox Application.Match("aaa", sn, 0)
MsgBox Application.Match("123", sn, 0)
end sub