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.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Field A
[/TD]
[TD]Field B
[/TD]
[TD]Field C
[/TD]
[TD]Field D
[/TD]
[/TR]
[TR]
[TD]Paper Gasket
[/TD]
[TD]1
[/TD]
[TD]Yellow Paper Gasket
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Spacer
[/TD]
[TD]2
[/TD]
[TD]round spacer
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Paper Liner
[/TD]
[TD]3
[/TD]
[TD]Paper Liner Small
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Spark Plug
[/TD]
[TD]4
[/TD]
[TD]small pipe
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Spring
[/TD]
[TD]5
[/TD]
[TD]liner
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Pipe
[/TD]
[TD]6
[/TD]
[TD]Spring 2.5
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Field A
[/TD]
[TD]Field B
[/TD]
[TD]Field C
[/TD]
[TD]Field D
[/TD]
[/TR]
[TR]
[TD]Paper Gasket
[/TD]
[TD]1
[/TD]
[TD]Yellow Paper Gasket
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Spacer
[/TD]
[TD]2
[/TD]
[TD]round spacer
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Paper Liner
[/TD]
[TD]3
[/TD]
[TD]Paper Liner Small
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Spark Plug
[/TD]
[TD]4
[/TD]
[TD]small pipe
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Spring
[/TD]
[TD]5
[/TD]
[TD]liner
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Pipe
[/TD]
[TD]6
[/TD]
[TD]Spring 2.5
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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))