Need help with complex lookup table.

A

alpswd

New Member
Joined
Nov 10, 2010
Messages
8
I have tried extensively for 3 hrs using vlookup and search commands but have had no luck.

The purpose of this table is to take a car name that is poorly formatted (ie "new 2010 bmw car 330xi town" and separate it nicely into parts:
Make = bmw
series = 3
model = 330
trim = xi

Screenshot: http://minus.com/mDBvNup33#1f

The screen shot above shows exactly what I am trying to do: Find the correct solution for a given query by indivdually seaching for words in that query and matching them up in the lookup table.

Two important notes:
1. If there are multiple matches, the seach that matches the greater number of words takes presense (so for a query of "2010 BMW 325 i X Staten", the correct solution would be Line 9, not Line 8).
2. Everything is case in-sensitive.

Any help would be greatly appreciated.

Excel File: http://min.us/mbn018v2Th
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Hi,

Maybe a UDF:
Code: 
Option Explicit

Function MultiMatch(ByVal LookupString As String, ByRef LookupTable As Range) As Long
Dim bMatch As Boolean
Dim lPtr As Long
Dim lLookupColumns As Long
Dim lBestMatchRow As Long
Dim lBestMatchCount As Long
Dim lMatchCount As Long
Dim objLookupString As Object
Dim rLookupTable As Range
Dim rCur As Range
Dim sCurLookupTableElement As String
Dim saLookupString() As String
Dim vCur As Variant

MultiMatch = -1

Set rLookupTable = Intersect(LookupTable.Resize(, 1), Sheets(LookupTable.Parent.Name).UsedRange)
If Not rLookupTable Is Nothing Then
    LookupString = WorksheetFunction.Trim(LCase$(LookupString))
    
    Set objLookupString = Nothing
    Set objLookupString = CreateObject("Scripting.Dictionary")
    If LookupString <> "" Then
        saLookupString = Split(LookupString, " ")
        For lPtr = 0 To UBound(saLookupString)
            On Error Resume Next
            objLookupString.Add key:=saLookupString(lPtr), Item:="X"
            On Error GoTo 0
        Next lPtr
        
        lBestMatchCount = -1
        lLookupColumns = LookupTable.Columns.Count
        If lLookupColumns = 1 Then ReDim vCur(1 To 1, 1 To 1)
        For Each rCur In rLookupTable
            If lLookupColumns = 1 Then
                vCur(1, 1) = rCur.Value
            Else
                vCur = rCur.Resize(, lLookupColumns).Value
            End If
            
            lMatchCount = 0
            For lPtr = 1 To lLookupColumns
                sCurLookupTableElement = WorksheetFunction.Trim(LCase$(CStr(vCur(1, lPtr))))
                If sCurLookupTableElement <> "" Then
                    If objLookupString.exists(sCurLookupTableElement) Then
                        lMatchCount = lMatchCount + 1
                    Else
                        lMatchCount = -1
                        Exit For
                    End If
                End If
            Next lPtr
            If lMatchCount > lBestMatchCount Then
                lBestMatchCount = lMatchCount
                lBestMatchRow = rCur.Row - LookupTable.Row + 1
            End If
        Next rCur
    End If
End If

If lBestMatchCount > 0 Then MultiMatch = lBestMatchRow

On Error Resume Next
objLookupString.RemoveAll
Set objLookupString = Nothing
End Function

Example of usage:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Input Query</td><td style="font-weight: bold;text-align: center;;">Row</td><td style="font-weight: bold;;">Make</td><td style="font-weight: bold;;">Series</td><td style="font-weight: bold;;">Model</td><td style="font-weight: bold;;">Trim</td><td style="font-weight: bold;;">Extra</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">2011 BMW 3 Series 330xi</td><td style="text-align: center;;">2</td><td style=";">BMW</td><td style=";">3</td><td style=";">330</td><td style=";">xi</td><td style=";">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">08 BMW 325 i</td><td style="text-align: center;;">9</td><td style=";">BMW</td><td style=";">3</td><td style=";">325</td><td style=";">i</td><td style=";">0</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">NEW bmw 3 series xi 325</td><td style="text-align: center;;">8</td><td style=";">BMW</td><td style=";">3</td><td style=";">325</td><td style=";">xi</td><td style=";">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">bmw 325 I x 12 awd</td><td style="text-align: center;;">10</td><td style=";">BMW</td><td style=";">3</td><td style=";">325</td><td style=";">i</td><td style=";">X Drive</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=multimatch(<font color="Blue">A2,'Lookup Table'!A:D</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B2,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B2,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B2,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B2,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B2,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=multimatch(<font color="Blue">A3,'Lookup Table'!A:D</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B3,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B3,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B3,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B3,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B3,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">=multimatch(<font color="Blue">A4,'Lookup Table'!A:D</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B4,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B4,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E4</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B4,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B4,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G4</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B4,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B5</th><td style="text-align:left">=multimatch(<font color="Blue">A5,'Lookup Table'!A:D</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B5,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B5,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E5</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B5,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F5</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B5,COLUMN(<font color="Red"></font>)+2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G5</th><td style="text-align:left">=INDEX(<font color="Blue">'Lookup Table'!$A:$I,$B5,COLUMN(<font color="Red"></font>)+2</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Lookup Table</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;border-right: 1px solid black;;"></td><td style="font-weight: bold;border-left: 1px solid black;;">Make</td><td style="font-weight: bold;;">Series</td><td style="font-weight: bold;;">Model</td><td style="font-weight: bold;;">Trim</td><td style="font-weight: bold;;">Extra</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">BMW</td><td style=";">330xi</td><td style=";"></td><td style="border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">BMW</td><td style="text-align: right;;">3</td><td style="text-align: right;;">330</td><td style=";">xi</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">BMW</td><td style=";">330</td><td style=";">xi</td><td style="border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">BMW</td><td style="text-align: right;;">3</td><td style="text-align: right;;">330</td><td style=";">xi</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">BMW</td><td style=";">330</td><td style=";"></td><td style="border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">BMW</td><td style="text-align: right;;">3</td><td style="text-align: right;;">330</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">BMW</td><td style=";">325</td><td style=";"></td><td style="border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">BMW</td><td style="text-align: right;;">3</td><td style="text-align: right;;">325</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">BMW</td><td style=";">325xi</td><td style=";"></td><td style="border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">BMW</td><td style="text-align: right;;">3</td><td style="text-align: right;;">325</td><td style=";">xi</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">BMW</td><td style=";">325i</td><td style=";"></td><td style="border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">BMW</td><td style="text-align: right;;">3</td><td style="text-align: right;;">325</td><td style=";">i</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">BMW</td><td style=";">325</td><td style=";">xi</td><td style="border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">BMW</td><td style="text-align: right;;">3</td><td style="text-align: right;;">325</td><td style=";">xi</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">BMW</td><td style=";">325</td><td style=";">i</td><td style="border-right: 1px solid black;;"></td><td style="border-left: 1px solid black;;">BMW</td><td style="text-align: right;;">3</td><td style="text-align: right;;">325</td><td style=";">i</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">BMW</td><td style=";">325</td><td style=";">i</td><td style="border-right: 1px solid black;;">X</td><td style="border-left: 1px solid black;;">BMW</td><td style="text-align: right;;">3</td><td style="text-align: right;;">325</td><td style=";">i</td><td style=";">X Drive</td></tr></tbody></table><p style="width:7.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Lookup Table</p><br /><br />
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Better approach to text-based lookup tables? Currently too slow.
Replies
2
Views
722
offthelip
O
G
  • Question Question
Need Help: Dynamic Matching that then backs into the header source
Replies
4
Views
569
goose7856
G
J
  • Question Question
Power Query/Pivot - Complex Lookup Table
Replies
2
Views
1K
jives00
J

Forum statistics

Threads
1,224,584
Messages
6,179,687
Members
452,938
Latest member
babeneker

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