loopkup part of a name or number

[mariekek5]

Hi,

I have a sheet containing a column with complete product names or numbers,
like for instance: 'Dogs plastic small 5 packages' or 'BKL 55267'.

In another sheet I want to look for something else, belonging to that name
or number. However, the other sheet does not contain the names or numbers as
such, but only part of the name or number, respectively 'plastic' ór '55267'.
Is it possible to lookup these cells, with only part of the name or number?

I know how I can look for the complete name, by ony having a part with:
"*plastic*", or "*55267*". But I dont know how I can work the other way
around. Is this possible?

Thanks in advance.

Marieke

 

[Jacob Skaria]

Change the reference as required

=VLOOKUP("*"&A1&"*",B110,2,FALSE)

If this post helps click Yes

 

[Jacob Skaria]

Oops..I missed your point

The below formula look for a first word match in the array.
A1 = 'Dogs plastic small 5 packages'

The below will lookup the first word that is 'dogs' in the array B1:B10 and
returns the row number. May be you can check whether this returns a row. If
not try and match the second word. and once you get the row use INDEX()
function to retreive the corresponding cell.

=MATCH(LEFT(A1,FIND(" ",A1)-1),B1:B10,0)

=index(b1:b10,MATCH(LEFT(A1,FIND(" ",A1)-1),B1:B10,0))

If this post helps click Yes