VLOOKUP with only certain words

[terp410]

hi - I have two spreadsheets... and trying to use a VLOOKUP formula (Or something similar if there is better) to basically do the following:

- Spreadsheet A has a product name - say "Color Hair Care"
- Spreadsheet B has a more detailed product name and a UPC code (separate columns) - "Color Hair Care 10 ounces Twin Pack)
- I want to say "Color Hair Care" from Spreadsheet A on spreadsheet B and then pull in Column C value (UPC). However, the "Color Hair Care" may be in a longer form on Spreadsheet B - like "Color Hair Care 10 ounces twin pack".

So basically how do I use a VLOOKUP model by saying "contains these words", versus an exact match.

Does that make sense?

Thanks!

 

[Jonmo1]

You can use a wildcard in VLOOKUP

=VLOOKUP(A2&"*",'spreadsheet B'!A:C,3,0)

 

[terp410]

Thx not sure what I am doing wrong - when you say wildcard... is that the "*"? Or should i be putting words in the quotes?
I uploaded a mocked example.
Tab 1 - I am trying to find the UPC (in yellow column C where I will input) by calling Column B to the VLOOKUP on tab 2. However the "product Name" is a little different on Tab 2, but most will contain phrases of it. Tab 2 product name is in Column A. UPC column 3.
Thoughts?

 

[Jonmo1]

You would use it exactly as I posted it with the "*"

Example

Excel Workbook
	A	B
2	Color Hair Care	d
Spreadsheet A

Excel Workbook
	A	B	C
2	blah		a
3	blah		b
4	blah		c
5	Color Hair Care 10 ounces Twin Pack		d
6	blah		e
7	blah		f
Spreadsheet B