VLOOKUP Closest Match Not Close Enough

  • Thread starter Thread starter Ronster
  • Start date Start date
R

Ronster

I'm comparing one string array with another using VLOOKUP. About half
the list matches exactly if I use FALSE in the Range_Lookup. On the
items that don't match I used TRUE in the Range_Lookup but found it
doesn't get the closest match. See example:

Test Table Array
AAABBBCCC
AAACCCCCC
AAACCCDDD

Lookup Value = AAACCCCC

VLOOKUP returns 1st item in Table (AAABBBCCC) ????????????

Of course item 2 (AAACCCCCC) is the better match but VLOOKUP doesn't
see it that way.

Does anyone know of a way around this problem? Maybe a character by
character match function that returns the item that matchs the most
number of correct characters?

Thanks in advance.
 
When you use true, the list that your are looking up into must be sorted,
otherwise it may return the wrong value.
 
AAACCCCCC is greater than AAACCCC so Excel returns the one before.
If there is not too much variation in the length of the string you could
introduce some extra columns in your table, derived from the original one,
wit fewer characters.
In your formula you could choose the column to look in, depending on the
length of your search argument.
To find out the length of your search argument: =LEN(A2)
To get a smaller column: =LEFT(A1,LEN(A1)-1)
 
The list is sorted, but the lookup value has one less "C", therefore, having
fewer characters than AAABBBCCC makes it return the first value in the lookup
list, no?
 
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.
 
Thanks Niek but I should have mentioned before my seach strings vary
from 4 to 52 chars and that's just in list I'm presently using. Other
list I need to search may be more or less so creating a column for each
search length gets a little large. Also in some search strings only a
few characters actually match anything in the table array so the seach
string could be 45 characters but only the first 3 characters match to
something in the table array. I think a function would work better.
 
Sorry about that I thought they were the same length... You will need to
parse the strings into smaller strings (as per Niek's suggestion) and play
around to get what you want. This is one of those cases where what appears to
be the simplest thing is the most difficult to accomplish.
 
The logic on this is very ugly because it is fuzzy. To come up with one
function that fits all of the possible circumstances will be very difficult.
For example what to do if we are trying to match a 7 character string to
items in the list and:

One possible match has all 7 characters but not in a row.
Another possible matches 6 charaters starting in the second position
Another possible matches the first 5 characters exactly
Another matches the last 5 characters exactly

Which ONE should the function return??? There needs to be very definite
rules in place that work for ALL cases.
 
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
 
Thanks Kleev. Here's something I threw together and it seems to work
pretty well.

Function ExactCharMatch(MySearchStr As String, Rng As Range) As String
' Function does a character by character search to determine which
' range item matches the most search characters from left to right.
' With range sorted in ascending order only one pass is needed to
determine best match.
' Use as-is or change as needed.

Dim MyRange As Range, MySearchStrLen As Integer, I As Integer, CharPos
As Integer
Dim LastGoodMatch As String

MySearchStrLen = Len(MySearchStr)

CharPos = 1

For Each MyRange In Rng
Do While Left(MySearchStr, CharPos) = Left(MyRange, CharPos)
'MsgBox Left(MySearchStr, CharPos) + " = " + Left(MyRange,
CharPos)
If CharPos >= MySearchStrLen Then
Exit For
End If
CharPos = CharPos + 1
LastGoodMatch = MyRange
Loop
If Left(MySearchStr, CharPos - 1) <> Left(MyRange, CharPos - 1)
Then
Exit For
End If
Next

ExactCharMatch = LastGoodMatch

End Function
 
Hi Ronster,
How do I get to use the function you given below? As by description, it is almost exactly what I'm looking for.
Although, I'm not going to use it for character search, but for company name comparisons (same company may be named "ABC company" or "ABC Co." but vlookup approximate fails to recognize these changes and gives wrong matches).
I am zero at VBA programming hence I'm unable to understand how I can run this function, please explain.

Thank you.

Ronster said:
Thanks Kleev. Here's something I threw together and it seems to work
pretty well.

Function ExactCharMatch(MySearchStr As String, Rng As Range) As String
' Function does a character by character search to determine which
' range item matches the most search characters from left to right.
' With range sorted in ascending order only one pass is needed to
determine best match.
' Use as-is or change as needed.

Dim MyRange As Range, MySearchStrLen As Integer, I As Integer, CharPos
As Integer
Dim LastGoodMatch As String

MySearchStrLen = Len(MySearchStr)

CharPos = 1

For Each MyRange In Rng
Do While Left(MySearchStr, CharPos) = Left(MyRange, CharPos)
'MsgBox Left(MySearchStr, CharPos) + " = " + Left(MyRange,
CharPos)
If CharPos >= MySearchStrLen Then
Exit For
End If
CharPos = CharPos + 1
LastGoodMatch = MyRange
Loop
If Left(MySearchStr, CharPos - 1) <> Left(MyRange, CharPos - 1)
Then
Exit For
End If
Next

ExactCharMatch = LastGoodMatch

End Function
Click to expand...
 
As soon as I asked the question, I found out the answer.
Instead of pasting it into particular sheet, I had to insert a module and paste it there.
It's working so far, putting it to big test now :-)
 
Back
Top