Dynamic If Contains specific String then return said Original String

[jross20]

Hey guys, it has been a while since I've needed any help but I've ran into a wall today. Basically, I need to be able to figure out which customer is which from a long "site ID".

So say that the first one is "Cust1A/SITE-5" , I have a table full of each customers short name and then matching project name on another tab. So say this table says Customer-A's project is "Customer 1-A Main Project".

I need excel to return to me which customer is which from the first cell based on whether it contains the short name for the customer. The short names will NOT always be the same length or at the same position.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer Short Name[/TD]
[TD]Project Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Site ID[/TD]
[TD]Project Name[/TD]
[/TR]
[TR]
[TD]Cust1A[/TD]
[TD]Customer 1-A Main Project[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cust1A/SITE-5[/TD]
[TD]Needs to return matching customer project name[/TD]
[/TR]
[TR]
[TD]Cust2B[/TD]
[TD]Customer 2-B Main Project[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cust2B/SITE-56[/TD]
[TD]Needs to return matching customer project name[/TD]
[/TR]
</tbody>[/TABLE]

I've spent an hour trying to what feels really simple but I've no idea how to make this work. The thing is it cannot have the names locked into the formulas, I need to to be dynamic as the list of customers and projects grow.

 

[jtakw]

Hi,

If I understand correctly, this should help:

Excel 2010
	A	B	C	D
1	Customer Short Name	Project Name	Site ID	Project Name
2	Cust1A	Customer 1-A Main Project	Cust1A/SITE-5	Customer 1-A Main Project
3	Cust2B	Customer 2-B Main Project	Cust2B/SITE-56	Customer 2-B Main Project
Sheet4

Cell Formulas
Range	Formula
D2	=INDEX(B$2:B$3,MATCH("*"&A2&"*",C$2:C$3,0))

D2 formula copied down.
Since your data is spread over a few different tabs, add tab names as appropriate.

 

[Roderick_E]

Hi there. A couple of things:

1. Can you use macros/subroutines?
2. How will you match these 3 lists? Will the user match only 1 customer short name at a time or do they need to match a list of short names?

The formula version is (assuming these are columns A-C):

=index(B:B,match(custshortname, A:A,0))

 

[jross20]

Hi there. A couple of things:

1. Can you use macros/subroutines?
2. How will you match these 3 lists? Will the user match only 1 customer short name at a time or do they need to match a list of short names?

The formula version is (assuming these are columns A-C):

=index(B:B,match(custshortname, A:A,0))

Each line is unique for each customers site. So like Customer-A/1 and then Customer-A/2 and so on. There can be multiple customers, so Row A can can grow.
There will never be a duplicate site in this list

There is an input sheet where the user will dump raw data into, the rest of the sheet will auto fill based on that. The first reply ALMOST works but gets some of them wrong.

After thinking about it I realized an easier way to ask the question, basically "Hey excel do each of these cells contain any of the strings from this column? If so tell me WHICH string it finds". That would be enough for me to match and index my way to victory.

(would like to avoid VBA if possible, some work computers are almost too slow to even run excel in the first place)

 

[jross20]

Hi,

If I understand correctly, this should help:

Excel 2010

	A	B	C	D
Customer Short Name	Project Name	Site ID	Project Name
Cust1A	Customer 1-A Main Project	Cust1A/SITE-5	Customer 1-A Main Project
Cust2B	Customer 2-B Main Project	Cust2B/SITE-56	Customer 2-B Main Project

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=INDEX(B$2:B$3,MATCH("*"&A2&"*",C$2:C$3,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



D2 formula copied down.
Since your data is spread over a few different tabs, add tab names as appropriate.

This almost works but gets a few wrong. I am still experimenting.