Matching cells' values (cell contains a string from another cell)

[bukimi]

Hello!

I have a problem, which I'm thinking about for some time already and didn't find any solution yet (and have to do it manually).

I have two columns in two seperate sheets:
FIRST contains shortened names of some products (for example: "Lamp")
SECOND contains full names of these products and in almost all cases they include shortened name somewhere (for example "Professional Lamp XL")
Of course there is only 1 "Lamp" in the list, so second column can look up the value of it in column A without finding duplicates.

Items in both columns are in different order, but I need to reference one from another to make one table that will be two columns:
|Short Name | Full name |

VLOOKUP doesn't help. I can't think of any formula (with If, Search, etc.) to solve it.
Any ideas? It can be VBA code, I often use that.

 

[James006]

Hello,

In order to get the row number you can test the match() function:

=MATCH("*"&Sheet1!A2&"*",Sheet2!D2:D100,0)

HTH

 

[bukimi]

Ok, that somehow works, now I have a column full of numbers of where I can find that product in my second Sheet.
How do I change these into names (string values)?

By the way, thank you.

 

[James006]

Hello again,

If the names you need to return are located in Sheet2 in Column A ...you could have :

=Index([FONT=Verdana]Sheet2!A2:A100[/FONT][FONT=Verdana],MATCH("*"&Sheet1!A2&"*",Sheet2!D2:D100,0))[/FONT]

HTH

 

[bukimi]

It works! I needed to add $ to formula range. After that it does exactly what I need. Thank you very much!

=Index([FONT=Verdana]Sheet2!$A$2:$A$100[/FONT][FONT=Verdana],MATCH("*"&Sheet1!A2&"*",Sheet2!D2:D100,0))[/FONT]

 

[James006]

Glad you could fix your problem ...:wink:

Thanks ... for your Thanks ...