Finding Partial String while using List as a Reference

[Avid1530]

Hello Wizards of Excel,

I'm in a bit of a bind I'm trying to find a partial match within a list of keywords. I'm looking for the return result to be the word found in the list. I tried vloookup and index match, but the problem is that it only finds the exact match of the word and doesn't take into account any symbols or characters that maybe in front or in between the particular keyword I'm searching for. I even tried adding an "*" as a wildcard with the vlookup and index-match formuals, but it still didn't get the information I'm looking for. I provide a sample below of what I'm looking for. Any help shedding light on this would be appreciated.

-- corrupted image removed --

 

[tlowry]

Here is some code you might look at:

Private Sub TextBox1_Change()

Dim rng, cCell
Me.ListBox1.Clear
With Sheets("FileNames")
​

For Each cCell In .Range("A1:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
​

If cCell.Text Like "*" & Me.TextBox1 & "*" Then
​

Me.ListBox1.AddItem cCell.Text​

End If
​

Next
​

End With
​

End Sub

 

[Momentman]

Hello Wizards of Excel,

I'm in a bit of a bind I'm trying to find a partial match within a list of keywords. I'm looking for the return result to be the word found in the list. I tried vloookup and index match, but the problem is that it only finds the exact match of the word and doesn't take into account any symbols or characters that maybe in front or in between the particular keyword I'm searching for. I even tried adding an "*" as a wildcard with the vlookup and index-match formuals, but it still didn't get the information I'm looking for. I provide a sample below of what I'm looking for. Any help shedding light on this would be appreciated.

Cant see the sample though

 

[István Hirsch]

Where is the sample?

 

[Avid1530]

Sorry about that I took a screenshot and it didn't load, Here is it below:

[TABLE="width: 500"]
[TR]
[TD="width: 215"]List
[/TD]
[TD="width: 95"]Return Values[/TD]
[TD="width: 64"][/TD]
[TD="width: 71"]Reference[/TD]
[/TR]
[TR]
[TD]Cool blue shirts
[/TD]
[TD][/TD]
[TD][/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]get yellow shirts today for $10.99[/TD]
[TD][/TD]
[TD][/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]Pink shirt sale[/TD]
[TD][/TD]
[TD][/TD]
[TD]pink[/TD]
[/TR]
[TR]
[TD]"yellow pants for sale"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]+blue +shirt +discount +sale[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][pink pants discount][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]shirts that are blue on sale[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]"blue shade of pants"
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]green jeans[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]get your pink shirt today![/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

 

[István Hirsch]

Please insert the desired result (manually) into the table above.

 

[Avid1530]

Sorry about that.....Here is the table.
If the items in the List column match anything in the Reference column I would like it to return those values

<tbody>
[TD="class: xl65"]List[/TD]
[TD="class: xl65, width: 95"]Return Values[/TD]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl65, width: 71"]Reference[/TD]

[TD="class: xl66"]Cool blue shirts[/TD]
[TD="class: xl66"]blue[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]blue[/TD]

[TD="class: xl66"]get yellow shirts today for $10.99[/TD]
[TD="class: xl66"]yellow[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]yellow[/TD]

[TD="class: xl66"]Pink shirt sale[/TD]
[TD="class: xl66"]pink[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"]pink[/TD]

[TD="class: xl66"]"yellow pants for sale"[/TD]
[TD="class: xl66"]yellow[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]+blue +shirt +discount +sale[/TD]
[TD="class: xl66"]blue[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"][pink pants discount][/TD]
[TD="class: xl66"]pink[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]shirts that are blue on sale[/TD]
[TD="class: xl66"]blue[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]"blue shade of pants"[/TD]
[TD="class: xl66"]blue[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]green jeans[/TD]
[TD="class: xl66"]N/A[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl66"]get your pink shirt today![/TD]
[TD="class: xl66"]pink[/TD]
[TD="class: xl66"][/TD]

</tbody>

 

[Momentman]

Like this

Book1
	A	B	C	D
1	List	Return Values		Reference
2	Cool blue shirts	blue		blue
3	get yellow shirts today for $10.99	yellow		yellow
4	Pink shirt sale	pink		pink
5	"yellow pants for sale"	yellow
6	+blue +shirt +discount +sale	blue
7	[pink pants discount]	pink
8	shirts that are blue on sale	blue
9	"blue shade of pants"	blue
10	green jeans	N/A
11	get your pink shirt today!	pink
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=IFERROR(INDEX($D$2:$D$4,MATCH(TRUE,IFERROR(SEARCH($D$2:$D$4,$A2),0)>0,0)),"N/A")

 

[István Hirsch]

Or try this:

=LOOKUP(1,-SEARCH(D$2:D$4,A2),$D$2:$D$4)

If the reference list contains complex colours, for example dark-green and green, put the reference list in the descending order of colour names before use.

 

[Avid1530]

Thanks, both solutions presented worked perfectly! Thanks for your help with this!