Perform Reverse Simple Match

Bill Bisco

Active Member
Joined
Aug 8, 2007
Messages
446
Hi all,

Every Keyword has a route associated with it. I want to find out which description contains the keyword and then post the associated route for that keyword .

For example, Abrasive is associated with Route 22. I want to match the Description "Large Abrasive Tool" to the keyword Abrasive. and now in the same row as Large Abrasive Tool I want to post the associated route of the keyword contained within "Large Abrasive Tool" (which is Abrasive) which should be Route 22.

ABCD
1DescriptionKeywordRouteRoute I want to Return
2Large Abrasive ToolTensile8722
3Small Cutting ScissorsAbrasive2245
4Difficult Tensile InstrumentCutting4587
5Random ToolHoning21
6Small Abrasive WheelDrilling1122

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2


<tbody>
</tbody>
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Description</td><td style="font-weight: bold;text-align: center;;">Keyword</td><td style="font-weight: bold;text-align: center;;">Route</td><td style="font-weight: bold;text-align: center;;">Route I want to Return</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;;">Large Abrasive Tool</td><td style="text-align: center;;">Tensile</td><td style="text-align: center;;">87</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;;">Small Cutting Scissors</td><td style="text-align: center;;">Abrasive</td><td style="text-align: center;;">22</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;;">Difficult Tensile Instrument</td><td style="text-align: center;;">Cutting</td><td style="text-align: center;;">45</td><td style="text-align: right;;">87</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;;">Random Tool</td><td style="text-align: center;;">Honing</td><td style="text-align: center;;">21</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;;">Small Abrasive Wheel</td><td style="text-align: center;;">Drilling</td><td style="text-align: center;;">11</td><td style="text-align: right;;">22</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A2</font>),$C$2:$C$6</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A3</font>),$C$2:$C$6</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A4</font>),$C$2:$C$6</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A5</font>),$C$2:$C$6</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D6</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">10^100,SEARCH(<font color="Green">$B$2:$B$6,A6</font>),$C$2:$C$6</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Bill Bisco

Active Member
Joined
Aug 8, 2007
Messages
446
Hi I noticed that these formulas start failing if I put in a large number of rows. For example if I do B2:B1000 and C2:C1000 suddenly my answers go to a value of 0 can you tell me why and how to avoid this?
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857

ADVERTISEMENT

Did you update the formula accordingly?

=IFERROR(LOOKUP(10^100,SEARCH($B$2:$B$6,A2),$C$2:$C$6),"")

the 6 needs to become the new last row
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
If you don't need the live update nature of formulas, then you can eliminate all those formulas and use this macro to return the results you want to Column D...
Code:
[table="width: 500"]
[tr]
	[td]Sub Routes()
  Dim R As Long, LastRow As Long, Keywords As Variant, FilteredRng As Range
  LastRow = Range("A1").CurrentRegion.Rows.Count
  Keywords = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)
  Application.ScreenUpdating = False
  For R = 2 To UBound(Keywords)
    If Application.CountIf(Range("A:A"), "*" & Keywords(R, 1) & "*") Then
      Range("A1").AutoFilter 1, "=*" & Keywords(R, 1) & "*", VisibleDropDown:=False
      Set FilteredRng = Intersect(Columns("D"), Range("2:" & LastRow).SpecialCells(xlVisible))
      If FilteredRng.Rows.Count Then
        FilteredRng.Value = Keywords(R, 2)
      End If
    End If
  Next
  ActiveSheet.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]
 

Bill Bisco

Active Member
Joined
Aug 8, 2007
Messages
446

ADVERTISEMENT

Did you update the formula accordingly?

=IFERROR(LOOKUP(10^100,SEARCH($B$2:$B$6,A2),$C$2:$C$6),"")

the 6 needs to become the new last row

Hi VBA Geek. Yes, I modified both of those. Try your own code again, and replace the 6 with a 1000. Suddenly Excel craps out and shows a 0!
 

Bill Bisco

Active Member
Joined
Aug 8, 2007
Messages
446
If you don't need the live update nature of formulas, then you can eliminate all those formulas and use this macro to return the results you want to Column D...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub Routes()
  Dim R As Long, LastRow As Long, Keywords As Variant, FilteredRng As Range
  LastRow = Range("A1").CurrentRegion.Rows.Count
  Keywords = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)
  Application.ScreenUpdating = False
  For R = 2 To UBound(Keywords)
    If Application.CountIf(Range("A:A"), "*" & Keywords(R, 1) & "*") Then
      Range("A1").AutoFilter 1, "=*" & Keywords(R, 1) & "*", VisibleDropDown:=False
      Set FilteredRng = Intersect(Columns("D"), Range("2:" & LastRow).SpecialCells(xlVisible))
      If FilteredRng.Rows.Count Then
        FilteredRng.Value = Keywords(R, 2)
      End If
    End If
  Next
  ActiveSheet.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Rick, there's no doubt that you're an Excel Genius. This might work, but I'd much prefer for it to be formula based. I've been banging my head against a wall because this should be intuitively easy yet it is not for me!
 

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Hi VBA Geek. Yes, I modified both of those. Try your own code again, and replace the 6 with a 1000. Suddenly Excel craps out and shows a 0!

'It should not do that. Can you post your whole 1000 rows or better yet upload a sample to dropbox
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,275
Office Version
  1. 365
Platform
  1. Windows
It will be because of blank cells i believe. Does your range include blank cells?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,841
Messages
5,598,390
Members
414,234
Latest member
grlevesq

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
Top