No warranties, but this function appears to do what you are asking (based on
the tests I threw at it.)

Function MatchChar(myString As String, myRng As Range) As Variant
Dim L As Integer, L2 As Integer
Dim i As Integer
Dim cell As Range
Dim TestChar As String, CellVal As String, CellChar As String
Dim NotFound As Boolean
Dim MatchCnt As Integer, PrevMatchCnt As Integer
Dim MatchRowIndex As Long
Dim RowIndex As Integer

PrevMatchCnt = 0
RowIndex = 0
L = Len(myString)
If myString = "" Then
MatchChar = CVErr(2042)
Else
For Each cell In myRng
MatchCnt = 0
RowIndex = RowIndex + 1
NotFound = False
CellVal = cell.Value
L2 = Len(CellVal)
i = 1
Do Until i L Or i L2 Or NotFound
TestChar = Mid(myString, i, 1)
CellChar = Mid(CellVal, i, 1)
If TestChar = CellChar Then
MatchCnt = MatchCnt + 1
Else
NotFound = True
End If
i = i + 1
Loop
If MatchCnt PrevMatchCnt Then
MatchRowIndex = RowIndex
PrevMatchCnt = MatchCnt
End If
Next cell
If PrevMatchCnt 0 Then
MatchChar = MatchRowIndex
Else
MatchChar = CVErr(2042)
End If
End If
End Function

Here is some data I threw at it. Data range is A2:B6. Formula in C2
(copied down through C11) is: =MatchChar(D2,$A$2:$A$6)
Using the function with the Index function for rows 12 - 22 (starting from
where the word Index ----------- is) formula is:
=INDEX($A$2:$B$6,MatchChar(D12,$A$2:$A$6),2)

AAABBBCCC B2 2 AAACCCCC
AAACCCCCC B3 5 mou
AAACCCDDD B4 #N/A
BBBABCX B5 1 AAABB
mouse B6 2 AAACCCCC
4 B
1 AAAB
1 A
3 AAACCCD
#N/A aaa
Index --------------- B2 AAA
B3 AAACCCCC
B6 mou
#N/A
B2 AAABB
B3 AAACCCCC
B5 B
B2 AAAB
B2 A
B4 AAACCCD
#N/A aaa


"Ronster" wrote:

Are any of you aware of a function that does a character by character
match of a string to an array no matter what the length of the string?
If I don't get an exact match using VLOOKUP what I really want is the
item that matches the most number of characters in the string.