Vlookup / Search of List to Partial Match and return a different value

[jarod_007]

Data	Expected Result		List 1	Value
This man has an apple	Fruit		Apple	Fruit
There is an Orange in the Tree	Fruit		Banana	Fruit
You can peel a banana	Fruit		Carrot	Vegetable
The carrots have green leaves	Vegetable		Cow	Animal
There's a cow on the farm	Animal		Orange	Fruit

In the above sample set, I'm looking to be able to Search through the Data with all values in List 1 and if a value in List 1 is spotted, then return the corresponding value as per the expected result.

I initially tried using a IF/CountIF function to determine if the value in List A exists in the Data, but then I'm stuck and not sure how I would translate that to then return a value.

Any help would be greatly appreciated. I hope this isn't a simple vlookup that I've totally missed the use of...

 

[JamesCanale]

MrExcelPlayground19.xlsx
	A	B	C	D	E
1	Data	Expected Result		List 1	Value
2	This man has an apple	Fruit		Apple	Fruit
3	There is an Orange in the Tree	Fruit		Banana	Fruit
4	You can peel a banana and an orange	Fruit, Fruit		Carrot	Vegetable
5	The carrots have green leaves	Vegetable		Cow	Animal
6	The cow eats the apple	Fruit, Animal		Orange	Fruit
7	There's a cow on the farm	Animal
Sheet15

Cell Formulas
Range	Formula
B2:B7	B2	=TEXTJOIN(", ",TRUE,FILTER($E$2:$E$6,ISNUMBER(SEARCH($D$2:$D$6,A2))))

 

[jarod_007]

Thank you! That's amazing!!

 

[Peter_SSs]

Thank you! That's amazing!!

Just checking.
I'm not suggesting anything wrong with the suggestion but this sort of question typically throws up some anomalies that I thought worth raising in case it might affect your results.

I assume that the sample data provided is not your real data but I have added some items to the list and some more example data in column A. Points to note

• B8 includes "Animal" though to my mind there is no animal in A8
• B9 includes "Fruit" though to my mind there is no fruit in A9
• In List1 you have "Carrot" and listed the result of "Vegetable" when the plural "carrots" appeared in the data. I have "Berry" in List1 but naturally the formula does not pick up that plural in row 10 below.

If desired, the formula could be adjusted to remove "Animal" and "Fruit" from B8:B9 but that adjustment would also remove "Vegetable" from B5 since "carrot" is not found as a whole word in A5.

If you might have any data where no results are found as in my row 10, this slight adjustment to the formula would return a blank instead of the error.
=TEXTJOIN(", ",TRUE,FILTER($E$2:$E$9,ISNUMBER(SEARCH($D$2:$D$9,A2)),""))

jarod_007.xlsm
	A	B	C	D	E
1	Data	Expected Result		List 1	Value
2	This man has an apple	Fruit		Apple	Fruit
3	There is an Orange in the Tree	Fruit		Banana	Fruit
4	You can peel a banana and an orange	Fruit, Fruit		Carrot	Vegetable
5	The carrots have green leaves	Vegetable		Cow	Animal
6	The cow eats the apple	Fruit, Animal		Orange	Fruit
7	There's a cow on the farm	Animal		Pineapple	Tropical Fruit
8	Please don't scowl	Animal, Facial expression		Scowl	Facial expression
9	I like pineapple juice	Fruit, Tropical Fruit		Berry	Fruit
10	I had berries for breakfast	#CALC!
Sheet1

Cell Formulas
Range	Formula
B2:B10	B2	=TEXTJOIN(", ",TRUE,FILTER($E$2:$E$9,ISNUMBER(SEARCH($D$2:$D$9,A2))))

You may well still be entirely happy with the suggestion made previously, but if it turns out you want to pursue this further, a couple of other questions

1. I note an apostrophe in the last row of your sample data. Does your real data have other punctuation/characters? eg Characters like these ? . ! " ( ) -
   
   
2. Would a vba suggestion be acceptable?
   
   
3. With an example like A4 of post 2, would you want "Fruit" returned twice in B4 like that or just once?

 

[jarod_007]

Just checking.
I'm not suggesting anything wrong with the suggestion but this sort of question typically throws up some anomalies that I thought worth raising in case it might affect your results.

I assume that the sample data provided is not your real data but I have added some items to the list and some more example data in column A. Points to note

• B8 includes "Animal" though to my mind there is no animal in A8
• B9 includes "Fruit" though to my mind there is no fruit in A9
• In List1 you have "Carrot" and listed the result of "Vegetable" when the plural "carrots" appeared in the data. I have "Berry" in List1 but naturally the formula does not pick up that plural in row 10 below.

If desired, the formula could be adjusted to remove "Animal" and "Fruit" from B8:B9 but that adjustment would also remove "Vegetable" from B5 since "carrot" is not found as a whole word in A5.

If you might have any data where no results are found as in my row 10, this slight adjustment to the formula would return a blank instead of the error.
=TEXTJOIN(", ",TRUE,FILTER($E$2:$E$9,ISNUMBER(SEARCH($D$2:$D$9,A2)),""))

You may well still be entirely happy with the suggestion made previously, but if it turns out you want to pursue this further, a couple of other questions

1. I note an apostrophe in the last row of your sample data. Does your real data have other punctuation/characters? eg Characters like these ? . ! " ( ) -
   
   
2. Would a vba suggestion be acceptable?
   
   
3. With an example like A4 of post 2, would you want "Fruit" returned twice in B4 like that or just once?

Thank you! That's really helpful too!! I had originally used a IF/CountIF/Search with "*"&Cell ID&"*" parameters to find if something exists and return a true/false value which I then leveraged with an If statement to wrap around the original solution suggestion above so that I wouldn't get the #calc. I know that the search itself is indeed flawed such that Bird vs Birds could yield different results. It definitely crossed my mind but in the real data set, the List is quite a unique set of names with a digit code at the end. Problem is the same because I can have say Fruit-1234 and Fruit-12345 exist but the case of that is actually rare for the amount of data I have.

To answer your questions:

1 - in the real data actually, the data column is a directory path. That is say for example \\?\Z:\sharedrive_1\folder\folder\...\project code\crazy name folder with even emojis listed that's not the proper project name\ where the end goal is to be able to extract the project code which may exist in any nested folder length and it's not fixed in position, not by \ separator or by characters or number of \ paths and to then reference a sheet of project codes that then give the project's actual name. And your assumption earlier is correct. There were a lot of folders that did not have project codes which instead of returning blank, I got it to return "no project related".

2 - VBA wouldn't be acceptable at the moment as we're using a shared spreadsheet through teams and certain functions are locked down.

3 - it wouldn't happen. The directories only ever contain one project code. There will never be an instance of two results in the data where two different results will show, but if the project code was Project-1234 then yes, the list could have Project-1234 and Project-12345 but a small error margin I can probably handle for now

 

[Peter_SSs]

Thanks for the further clarifications. It is still a bit vague, so could you dummy up 4 or 5 rows with a bit more realistic data (ie dummy folder paths), a new Lookup table and the expected results?
Make your project codes more realistic in structure and be sure to include a bit of variety especially in relation to the comments below.

I can have say Fruit-1234 and Fruit-12345

project code which may exist in any nested folder length and it's not fixed in position, not by \ separator or by characters or number of \ paths

 

[jarod_007]

Hi, Sorry, been travelling for work and didn't have much access after I got on the train yesterday.

Actually, the likelihood of say ProjectGroup1-1234 and ProjectGroup1-12345 happening is extremely rare unless we manage to clear 10000 projects that year as the numbers are sequential. For example:

ProjectGroup1-12345
ProjectGroup2-12346
ProjectGroup1-12347
ProjectGroup1-12348
Admin1-12349
Maintenance4-12350
Admin4-12451

as a sample set of the list.

Here's some sample data:

Directories:	Result		List	Name
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National & local	Home		ProjectGroup1-12345	Fruit
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National only	Home		ProjectGroup2-12346	Clothing
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\old	Home		ProjectGroup1-12347	Medicine
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\old\DESIGN\Links	Home		ProjectGroup1-12348	Meat
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\Social	Home		Admin1-12349	Fish
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346	Clothing		Maintenance4-12350	Liquids
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346\Pack	Clothing		Admin4-12451	Home
\\?\U:\media_server4\housebrandname\BRANDING\Logos	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\New	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\CMYK	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\RGB	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Pictos	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos	Fish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\New	Fish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\CMYK	Fish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\RGB	Fish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Pictos	Fish
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Adobe Animate	Clothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Copy	Clothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Copy\Content	Clothing
\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. Folders	Medicine
\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. Folders\WIP	Medicine
\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\	Medicine
\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\WIP	Medicine

 

[Peter_SSs]

Thanks for samples. How does this go then?

jarod_007.xlsm
	A	B	C	D	E
1	Directories:	Result		List	Name
2	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National & local	Home		ProjectGroup1-12345	Fruit
3	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National only	Home		ProjectGroup2-12346	Clothing
4	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\old	Home		ProjectGroup1-12347	Medicine
5	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\old\DESIGN\Links	Home		ProjectGroup1-12348	Meat
6	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\Social	Home		Admin1-12349	Fish
7	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346	Clothing		Maintenance4-12350	Liquids
8	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346\Pack	Clothing		Admin4-12451	Home
9	\\?\U:\media_server4\housebrandname\BRANDING\Logos	No project
10	\\?\U:\media_server4\housebrandname\BRANDING\Logos\New	No project
11	\\?\U:\media_server4\housebrandname\BRANDING\Logos\CMYK	No project
12	\\?\U:\media_server4\housebrandname\BRANDING\Logos\RGB	No project
13	\\?\U:\media_server4\housebrandname\BRANDING\Pictos	No project
14	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos	Fish
15	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\New	Fish
16	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\CMYK	Fish
17	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\RGB	Fish
18	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Pictos	Fish
19	\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Adobe Animate	Clothing
20	\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Copy	Clothing
21	\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Copy\Content	Clothing
22	\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. Folders	Medicine
23	\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. Folders\WIP	Medicine
24	\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\	Medicine
25	\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\WIP	Medicine
Sheet2

Cell Formulas
Range	Formula
B2:B25	B2	=FILTER($E$2:$E$8,ISNUMBER(SEARCH($D$2:$D$8,A2)),"No project")

Actually, the likelihood of say ProjectGroup1-1234 and ProjectGroup1-12345 happening is extremely rare

If you could have something like that, you could try this instead.

jarod_007.xlsm
	A	B	C	D	E
1	Directories:	Result		List	Name
2	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National & local	Home		ProjectGroup1-12345	Fruit
3	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National only	Home		ProjectGroup2-12346	Clothing
4	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\old	Home		ProjectGroup1-1234	Medicine
5	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\old\DESIGN\Links	Home		ProjectGroup1-12348	Meat
6	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\Social	Home		Admin1-12349	Fish
7	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346	Clothing		Maintenance4-12350	Liquids
8	\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346\Pack	Clothing		Admin4-12451	Home
9	\\?\U:\media_server4\housebrandname\BRANDING\Logos	No project		ProjectGroup1-12347	Other
10	\\?\U:\media_server4\housebrandname\BRANDING\Logos\New	No project
11	\\?\U:\media_server4\housebrandname\BRANDING\Logos\CMYK	No project
12	\\?\U:\media_server4\housebrandname\BRANDING\Logos\RGB	No project
13	\\?\U:\media_server4\housebrandname\BRANDING\Pictos	No project
14	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos	Fish
15	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\New	Fish
16	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\CMYK	Fish
17	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\RGB	Fish
18	\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Pictos	Fish
19	\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Adobe Animate	Clothing
20	\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Copy	Clothing
21	\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Copy\Content	Clothing
22	\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. Folders	Other
23	\\?\U:\media_server4\housebrandname\ProjectGroup1-1234\Indesign\1. Folders\WIP	Medicine
24	\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\	Other
25	\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\WIP	Other
26		No project
Sheet3

Cell Formulas
Range	Formula
B2:B26	B2	=FILTER($E$2:$E$9,ISNUMBER(SEARCH("\"&$D$2:$D$9&"\","\"&A2&"\")),"No project")

 

[jarod_007]

I noticed that the search is based on the list being wrapped in \ which is my bad. I was editing on the fly and didn't give a wider example. Sorry about that! But actually, the first formula works fine!!

Amended sample:

Directories:	Result		List	Name
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National & local	Home		ProjectGroup1-12345	Fruit
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National only	Home		ProjectGroup2-12346	Clothing
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451 old&tobedeleted\docs\old	Home		ProjectGroup1-12347	Medicine
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451 old&tobedeleted\docs\old\DESIGN\Links	Home		ProjectGroup1-12348	Meat
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451 - karen to edit \Social	Home		Admin1-12349	Fish
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346	Clothing		Maintenance4-12350	Liquids
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346\Pack	Clothing		Admin4-12451	Home
\\?\U:\media_server4\housebrandname\BRANDING\Logos	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\New	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\CMYK	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\RGB	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Pictos	No Project
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos	Fish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\New	Fish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\CMYK	Fish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\RGB	Fish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349 - still in draft! Do not touch!\Pictos	Fish
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346 2023 edition\Adobe Animate	Clothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346 2023 edition\Copy	Clothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346 2023 edition\Copy\Content	Clothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup1-12347\Indesign\1. Folders	Medicine
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup1-12347\Indesign\1. Folders\WIP	Medicine
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup1-12347\Indesign\2. Pos\	Medicine
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup1-12347\Indesign\2. Pos\WIP	Medicine

 

[Peter_SSs]

But actually, the first formula works fine!!

OK, cheers, Good news.