Fuzzy Matching - new version plus explanation

A

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,546
It has been a while since I originally posted my Fuzzy matching UDF’s on the board, and several variants have appeared subsequently.

I thought it time to ‘put the record straight’ & post a definitive version which contains slightly more efficient code, and better matching algorithms, so here it is.

Firstly, I must state that the Fuzzy matching algorithms are very CPU hungry, and should be used sparingly. If for instance you require to lookup a match for a string which starts with, contains or ends with a specified value, this can be performed far more efficiently using the MATCH function:
Fuzzy Examples.xls
ABCDE
1Starts WithEndsContains
2BilljelenBill
3Mr Bill Jelen433
4Bill Jelen
5Joe Bloggs
6Fred Smith
MATCH Example


... Continued ...
 
Sort by date Sort by votes
Hi,
Welcome to the board!

FuzzyVLookup is not suitable for your requirements, how about JaxiDernLookup:
Code: 
Option Explicit
Function JaxiDernLookup(ByVal LookupValue As String, _
                        ByVal LookupTable As Range, _
                        ByVal Index As Integer, _
                        Optional Rank As Long = 1, _
                        Optional Delimiter As String = ";") As Variant
Dim bMatch As Boolean
Dim iPtr As Integer
Dim lRank As Long
Dim rCur As Range, rInput As Range
Dim saCurrentEntry() As String
Dim wsLookup As Worksheet

lRank = Rank
Set wsLookup = Sheets(LookupTable.Parent.Name)
Set rInput = Intersect(wsLookup.UsedRange, LookupTable.Resize(, 1))
For Each rCur In rInput
    saCurrentEntry = Split(Delimiter & Replace(CStr(rCur.Value), " ", ""), Delimiter)
    For iPtr = 1 To UBound(saCurrentEntry)
        If LookupValue = saCurrentEntry(iPtr) Then
            lRank = lRank - 1
            If lRank< 1 Then Exit For
        End If
    Next iPtr
    If lRank< 1 Then
        If Index = 0 Then
            JaxiDernLookup = rCur.Row
        Else
            JaxiDernLookup = rCur.Offset(, Index - 1).Value
        End If
        Exit Function
    End If
Next rCur
JaxiDernLookup = CVErr(xlErrNA)
End Function

Example of use:
Excel Workbook
ABCDEFGHIJ
1List AList A DataList BRank 1Rank 2Rank 3Rank 4Rank 5Rank 6
21234567; 4332142; 3423575; 4323454$B$21234567$B$2$B$4$B$5$B$6$B$7$B$8
32310012; 2213003; 2313082; 2820809; 2830903; 9106090; 9508105; 9121329$B$32213003$B$3#N/A#N/A#N/A#N/A#N/A
41234567; 4332142; 3423575; 4323454$B$42820809$B$3#N/A#N/A#N/A#N/A#N/A
51234567; 4332142; 3423575; 4323454$B$53423575$B$2$B$4$B$5$B$6#N/A#N/A
61234567; 4332142; 3423575; 4323454$B$6
724680;1234567;357911$B$7
81234567$B$8
Sheet1
Excel 2003
Cell Formulas
RangeFormula
E2=JaxiDernLookup($D2,$A:$A,2,RIGHT(E$1,1),";")
F2=JaxiDernLookup($D2,$A:$A,2,RIGHT(F$1,1),";")
G2=JaxiDernLookup($D2,$A:$A,2,RIGHT(G$1,1),";")
H2=JaxiDernLookup($D2,$A:$A,2,RIGHT(H$1,1),";")
I2=JaxiDernLookup($D2,$A:$A,2,RIGHT(I$1,1),";")
J2=JaxiDernLookup($D2,$A:$A,2,RIGHT(J$1,1),";")
B2=ADDRESS(ROW(),COLUMN())
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi al_b_cnu,

Thanks for your reply. Using your spreadsheet as example, cell A3 will be my lookupvalue, and list B as my lookuptable. Will need my answer to be in a single cell.

In this case, the result for my lookupvalue List A, maybe having all these results in column E:

Cell E2 will be: "1234567"
Cell E3 will be: "2213003 ; 2820809"

Do you think its possible, appreciate your help
 
Upvote 0
Sorry, I dont understand your logic here, can you post a sample input & expected results with explanation?
 
Upvote 0
Apologies for my confusing post. :stickouttounge:

Let me try again. I have two separate lists, List A and B.

I will like to check List A against List B and return serial numbers where there is a match. In my table below, Cell A2 will be my lookupvalue and D:D will be the lookuptable. Cell B2 shows 1 match which is just a comparsion of each item in A2 against D:D. Cell B3 returns 2 matches, etc.

<TABLE class=MsoNormalTable style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; mso-cellspacing: 0cm; mso-padding-alt: 0cm 0cm 0cm 0cm; mso-border-insideh: 1.0pt solid windowtext; mso-border-insidev: 1.0pt solid windowtext" cellSpacing=0 cellPadding=0 border=1><THEAD><TR style="mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 209.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=279>
A<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 163.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=217>
B<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
C<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
D<o:p></o:p>

​
</TD></TR></THEAD><TBODY><TR style="mso-yfti-irow: 1"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
1<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 209.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=279>
List A<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 163.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=217>
Results<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
List B<o:p></o:p>​

​
</TD></TR><TR style="mso-yfti-irow: 2"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
2<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 209.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=279>
1234567; 4332142; 3423575; 4323454<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 163.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=217>
1234567<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
1234567<o:p></o:p>​

​
</TD></TR><TR style="mso-yfti-irow: 3"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
3<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 209.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=279>
2310012; 2213003; 2313082; 2820809; 2830903; 9106090; 9508105; 9121329<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 163.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=217>
2213003; 2820809 <o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
2213003<o:p></o:p>​

​
</TD></TR><TR style="mso-yfti-irow: 4"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
4<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 209.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=279>
1234567; 4332142; 3423575; 4323454<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 163.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=217>
1234567<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
2820809<o:p></o:p>​

​
</TD></TR><TR style="mso-yfti-irow: 5"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
5<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 209.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=279>
2468000;1234567;3579011<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 163.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=217>
1234567<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
3423575<o:p></o:p>​

​
</TD></TR><TR style="mso-yfti-irow: 6"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
6<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 209.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=279>
3423575; 2213003; 7654321; 6654521; 2820809<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 163.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=217>
3423575; 2213003; 2820809<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>​

​
</TD></TR><TR style="mso-yfti-irow: 7"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
7<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 209.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=279>
<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 163.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=217>
<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>​

​
</TD></TR><TR style="mso-yfti-irow: 8; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
8<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 209.25pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=279>
<o:p></o:p>​

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; WIDTH: 163.05pt; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=217>
<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>

​
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 0cm; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: windowtext 1pt solid; PADDING-TOP: 0cm; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">
<o:p></o:p>​


</TD></TR></TBODY></TABLE>​
 
Upvote 0
Hi,

OK, is this what you're after :
Excel Workbook
ABCDE
1List AExpected ResultsActual ResultsList B
21234567; 4332142; 3423575; 432345412345671234567;34235751234567
32310012; 2213003; 2313082; 2820809; 2830903; 9106090; 9508105; 91213292213003; 28208092213003;28208092213003
41234567; 4332142; 3423575; 432345412345671234567;34235752820809
52468000;1234567;3579011123456712345673423575
63423575; 2213003; 7654321; 6654521; 28208093423575; 2213003; 28208092213003;2820809;3423575
Sheet1
Excel 2003
Cell Formulas
RangeFormula
C2=JaxiDermLookup(A2,E:E)
C3=JaxiDermLookup(A3,E:E)
C4=JaxiDermLookup(A4,E:E)
C5=JaxiDermLookup(A5,E:E)
C6=JaxiDermLookup(A6,E:E)


Code: 
Option Explicit

Function JaxiDermLookup(ByVal LookupValue As String, _
                        ByVal LookupTable As Range, _
                        Optional Delimiter As String = ";") As Variant
Dim iPtr As Integer
Dim rCur As Range
Dim saLookupValues() As String, sCur As String, sResult As String
Dim wsLookupTable As Worksheet

Set wsLookupTable = Sheets(LookupTable.Parent.Name)

saLookupValues = Split(Delimiter & LCase$(Replace(LookupValue, " ", "")), Delimiter)

sResult = ""
For Each rCur In Intersect(wsLookupTable.UsedRange, LookupTable.Resize(, 1))
    sCur = LCase$(Replace(CStr(rCur.Value), " ", ""))
    For iPtr = 1 To UBound(saLookupValues)
        If sCur = saLookupValues(iPtr) Then
            sResult = sResult & Delimiter & sCur
        End If
    Next iPtr
Next rCur
If sResult<> "" Then sResult = Mid$(sResult, 2)

JaxiDermLookup = sResult
End Function
 
Upvote 0
Wow, you are a lifesaver! Thats exactly what i was looking for :biggrin:.

Will the formula work if i will like to include a Col_index_num to return a different column from List B? Thanks
 
Upvote 0
Try this version (note the new 'Index' parameter):
Code: 
Option Explicit

Function JaxiDermLookup(ByVal LookupValue As String, _
                        ByVal LookupTable As Range, _
                        Optional Index As Integer = 1, _
                        Optional Delimiter As String = ";") As Variant
Dim iPtr As Integer
Dim rCur As Range
Dim saLookupValues() As String, sCur As String, sResult As String
Dim wsLookupTable As Worksheet

Set wsLookupTable = Sheets(LookupTable.Parent.Name)

saLookupValues = Split(Delimiter & LCase$(Replace(LookupValue, " ", "")), Delimiter)

sResult = ""
For Each rCur In Intersect(wsLookupTable.UsedRange, LookupTable.Resize(, 1))
    sCur = LCase$(Replace(CStr(rCur.Value), " ", ""))
    For iPtr = 1 To UBound(saLookupValues)
        If sCur = saLookupValues(iPtr) Then
            sResult = sResult & Delimiter & CStr(rCur.Offset(, Index - 1).Value)
        End If
    Next iPtr
Next rCur
If sResult<> "" Then sResult = Mid$(sResult, 2)

JaxiDermLookup = sResult
End Function

For example:
Excel Workbook
ABCDE
1List AResultsList B
21234567; 4332142; 3423575; 4323454$F$2;$F$51234567$F$2
32310012; 2213003; 2313082; 2820809; 2830903; 9106090; 9508105; 9121329$F$3;$F$42213003$F$3
41234567; 4332142; 3423575; 4323454$F$2;$F$52820809$F$4
52468000;1234567;3579011$F$23423575$F$5
63423575; 2213003; 7654321; 6654521; 2820809$F$3;$F$4;$F$5
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B2=JaxiDermLookup(A2,D:D,2)
 
Upvote 0
Hi, I am trying to match a short description to a different set of data/descriptions. The descriptions I am trying to compare to are not exactly the same. Some may be longer or shorter and/or have extra/less words. Once I match the values, i need to get a value that has a "table location" in a different column. Is there a way to pull up the "table location" value as well as the result from the algorithm?
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Fuzzy Match code
Replies
1
Views
2K
gelu
G
J
  • Question Question
Formula(s) to match cell value
Replies
3
Views
2K
Aladin Akyurek
Aladin Akyurek
E
  • Question Question
Fuzzy Excel problem
Replies
1
Views
5K
al_b_cnu
A
A
  • Question Question
How to Consolidate large Qty of unique numbers w/ matching criteria into fewer numbers
Replies
3
Views
1K
amerikanzero
A
A
  • Question Question
Form Automation: Populating a ComboBox during a dynamic userform build
2
Replies
12
Views
2K
Atroxell
A

Forum statistics

Threads
1,224,847
Messages
6,181,311
Members
453,031
Latest member
Chris_1

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top