Trouble with VLOOKUP Matching text string anywhere in field

[aafowler]

I am trying to use VLOOKUP to match the text in Field C with the text in Field A and then populate Field D with the values from Field B. The formula I am using is =VLOOKUP("*"&C2&"*",$A$2:$B$7,2,FALSE). The first table below is the results I would like. The second table shows the results that I am getting. Any advice would be great. Thanks.

Field A	Field B	Field C	Field D
Paper Gasket	1	Yellow Paper Gasket	1
Spacer	2	round spacer	2
Paper Liner	3	Paper Liner Small	3
Spark Plug	4	small pipe	6
Spring	5	liner	3
Pipe	6	Spring 2.5	5

<tbody>
</tbody>

Field A	Field B	Field C	Field D
Paper Gasket	1	Yellow Paper Gasket	#N/A
Spacer	2	round spacer	#N/A
Paper Liner	3	Paper Liner Small	#N/A
Spark Plug	4	small pipe	#N/A
Spring	5	liner	1
Pipe	6	Spring 2.5	2

<tbody>
</tbody>

 

[Chrisdontm]

There may be a better way of doing this, but this appears to work;

Try this in cell: D2 then copy it on down...

=IFERROR(VLOOKUP("*"&C2&"*",$A$2:$B$7,2,FALSE),LARGE(ISNUMBER(SEARCH(("*"&INDEX($A$2:$A$7,)&"*"),$C2))*$B$2:$B$7,1))

It is an array formula, so you need to enter it with: Ctrl + Sht. + Enter

 

[bosco_yip]

Or…….

=IFERROR(LOOKUP(9^9,SEARCH(C2,A$2:A$7),B$2:B$7),LOOKUP(9^9,SEARCH(A$2:C$7,C2),B$2:B$7))

Regards
Bosco

 

[Aladin Akyurek]

In D2 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$7&" "," "&C2&" "),$B$2:$B$7),VLOOKUP("*"&C2&"*",$A$2:$B$7,2,0))