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

bukimi

Board Regular
Joined
Apr 12, 2017
Messages
90
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

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

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

HTH
 

bukimi

Board Regular
Joined
Apr 12, 2017
Messages
90
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello again,

If the names you need to return are located in Sheet2 in Column A ...you could have :
Code:
=Index([FONT=Verdana]Sheet2!A2:A100[/FONT][FONT=Verdana],MATCH("*"&Sheet1!A2&"*",Sheet2!D2:D100,0))[/FONT]

HTH
 

bukimi

Board Regular
Joined
Apr 12, 2017
Messages
90
Office Version
  1. 2016
Platform
  1. Windows
It works! I needed to add $ to formula range. After that it does exactly what I need. Thank you very much!
Code:
=Index([FONT=Verdana]Sheet2!$A$2:$A$100[/FONT][FONT=Verdana],MATCH("*"&Sheet1!A2&"*",Sheet2!D2:D100,0))[/FONT]
 
Last edited:

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Glad you could fix your problem ...:wink:

Thanks ... for your Thanks ...:)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,944
Messages
5,621,753
Members
415,854
Latest member
Tutu123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top