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

J

jarod_007

New Member
Joined
Jun 8, 2021
Messages
16
Office Version
  1. 365
Platform
  1. Windows
DataExpected ResultList 1Value
This man has an appleFruitAppleFruit
There is an Orange in the TreeFruitBananaFruit
You can peel a bananaFruitCarrotVegetable
The carrots have green leavesVegetableCowAnimal
There's a cow on the farmAnimalOrange 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...
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
MrExcelPlayground19.xlsx
ABCDE
1DataExpected ResultList 1Value
2This man has an appleFruitAppleFruit
3There is an Orange in the TreeFruitBananaFruit
4You can peel a banana and an orangeFruit, FruitCarrotVegetable
5The carrots have green leavesVegetableCowAnimal
6The cow eats the appleFruit, AnimalOrangeFruit
7There's a cow on the farmAnimal
Sheet15
Cell Formulas
RangeFormula
B2:B7B2=TEXTJOIN(", ",TRUE,FILTER($E$2:$E$6,ISNUMBER(SEARCH($D$2:$D$6,A2))))
 
Upvote 1
jarod_007 said:
Thank you! That's amazing!!
Click to expand...
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
ABCDE
1DataExpected ResultList 1Value
2This man has an appleFruitAppleFruit
3There is an Orange in the TreeFruitBananaFruit
4You can peel a banana and an orangeFruit, FruitCarrotVegetable
5The carrots have green leavesVegetableCowAnimal
6The cow eats the appleFruit, AnimalOrangeFruit
7There's a cow on the farmAnimalPineappleTropical Fruit
8Please don't scowlAnimal, Facial expressionScowlFacial expression
9I like pineapple juiceFruit, Tropical FruitBerryFruit
10I had berries for breakfast#CALC!
Sheet1
Cell Formulas
RangeFormula
B2:B10B2=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?
 
Upvote 1
Peter_SSs said:
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?
Click to expand...

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
 
Upvote 0
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.
jarod_007 said:
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
Click to expand...
 
Upvote 1
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:ResultListName
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National & localHomeProjectGroup1-12345Fruit
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National onlyHomeProjectGroup2-12346Clothing
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\oldHomeProjectGroup1-12347Medicine
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\old\DESIGN\LinksHomeProjectGroup1-12348Meat
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\SocialHomeAdmin1-12349Fish
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346ClothingMaintenance4-12350Liquids
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346\PackClothingAdmin4-12451Home
\\?\U:\media_server4\housebrandname\BRANDING\LogosNo Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\NewNo Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\CMYKNo Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\RGBNo Project
\\?\U:\media_server4\housebrandname\BRANDING\PictosNo Project
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\LogosFish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\NewFish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\CMYKFish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\RGBFish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\PictosFish
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Adobe AnimateClothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\CopyClothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Copy\ContentClothing
\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. FoldersMedicine
\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. Folders\WIPMedicine
\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\Medicine
\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\WIPMedicine
 
Upvote 0
Thanks for samples. How does this go then?

jarod_007.xlsm
ABCDE
1Directories:ResultListName
2\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National & localHomeProjectGroup1-12345Fruit
3\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National onlyHomeProjectGroup2-12346Clothing
4\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\oldHomeProjectGroup1-12347Medicine
5\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\old\DESIGN\LinksHomeProjectGroup1-12348Meat
6\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\SocialHomeAdmin1-12349Fish
7\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346ClothingMaintenance4-12350Liquids
8\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346\PackClothingAdmin4-12451Home
9\\?\U:\media_server4\housebrandname\BRANDING\LogosNo project
10\\?\U:\media_server4\housebrandname\BRANDING\Logos\NewNo project
11\\?\U:\media_server4\housebrandname\BRANDING\Logos\CMYKNo project
12\\?\U:\media_server4\housebrandname\BRANDING\Logos\RGBNo project
13\\?\U:\media_server4\housebrandname\BRANDING\PictosNo project
14\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\LogosFish
15\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\NewFish
16\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\CMYKFish
17\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\RGBFish
18\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\PictosFish
19\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Adobe AnimateClothing
20\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\CopyClothing
21\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Copy\ContentClothing
22\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. FoldersMedicine
23\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. Folders\WIPMedicine
24\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\Medicine
25\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\WIPMedicine
Sheet2
Cell Formulas
RangeFormula
B2:B25B2=FILTER($E$2:$E$8,ISNUMBER(SEARCH($D$2:$D$8,A2)),"No project")



jarod_007 said:
Actually, the likelihood of say ProjectGroup1-1234 and ProjectGroup1-12345 happening is extremely rare
Click to expand...
If you could have something like that, you could try this instead.

jarod_007.xlsm
ABCDE
1Directories:ResultListName
2\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National & localHomeProjectGroup1-12345Fruit
3\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National onlyHomeProjectGroup2-12346Clothing
4\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\oldHomeProjectGroup1-1234Medicine
5\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\old\DESIGN\LinksHomeProjectGroup1-12348Meat
6\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\SocialHomeAdmin1-12349Fish
7\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346ClothingMaintenance4-12350Liquids
8\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346\PackClothingAdmin4-12451Home
9\\?\U:\media_server4\housebrandname\BRANDING\LogosNo projectProjectGroup1-12347Other
10\\?\U:\media_server4\housebrandname\BRANDING\Logos\NewNo project
11\\?\U:\media_server4\housebrandname\BRANDING\Logos\CMYKNo project
12\\?\U:\media_server4\housebrandname\BRANDING\Logos\RGBNo project
13\\?\U:\media_server4\housebrandname\BRANDING\PictosNo project
14\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\LogosFish
15\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\NewFish
16\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\CMYKFish
17\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\RGBFish
18\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\PictosFish
19\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Adobe AnimateClothing
20\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\CopyClothing
21\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346\Copy\ContentClothing
22\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\1. FoldersOther
23\\?\U:\media_server4\housebrandname\ProjectGroup1-1234\Indesign\1. Folders\WIPMedicine
24\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\Other
25\\?\U:\media_server4\housebrandname\ProjectGroup1-12347\Indesign\2. Pos\WIPOther
26No project
Sheet3
Cell Formulas
RangeFormula
B2:B26B2=FILTER($E$2:$E$9,ISNUMBER(SEARCH("\"&$D$2:$D$9&"\","\"&A2&"\")),"No project")
 
Upvote 1
Solution
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:ResultListName
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National & localHomeProjectGroup1-12345Fruit
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451\docs\docs_2023_DEF Folder\Links\National onlyHomeProjectGroup2-12346Clothing
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451 old&tobedeleted\docs\oldHomeProjectGroup1-12347Medicine
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451 old&tobedeleted\docs\old\DESIGN\LinksHomeProjectGroup1-12348Meat
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\admin4-12451 - karen to edit \SocialHomeAdmin1-12349Fish
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346ClothingMaintenance4-12350Liquids
\\?\U:\media_server4\housebrandname\BRANDING\styleguide\ProjectGroup2-12346\PackClothingAdmin4-12451Home
\\?\U:\media_server4\housebrandname\BRANDING\LogosNo Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\NewNo Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\CMYKNo Project
\\?\U:\media_server4\housebrandname\BRANDING\Logos\RGBNo Project
\\?\U:\media_server4\housebrandname\BRANDING\PictosNo Project
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\LogosFish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\NewFish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\CMYKFish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349\Logos\RGBFish
\\?\U:\media_server4\housebrandname\BRANDING\Admin1-12349 - still in draft! Do not touch!\PictosFish
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346 2023 edition\Adobe AnimateClothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346 2023 edition\CopyClothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup2-12346 2023 edition\Copy\ContentClothing
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup1-12347\Indesign\1. FoldersMedicine
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup1-12347\Indesign\1. Folders\WIPMedicine
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup1-12347\Indesign\2. Pos\Medicine
\\?\U:\media_server4\housebrandname\Campaign\ProjectGroup1-12347\Indesign\2. Pos\WIPMedicine
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Match with multiple columns
2
Replies
14
Views
754
Peter_SSs
Peter_SSs
N
  • Question Question
Search for multiple values within a single cell
Replies
6
Views
681
Peter_SSs
Peter_SSs
E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
3K
Kevin_Li
Kevin_Li
E
  • Solved
Index/Match - Return value if partial text from cell contained anywhere in separate column
Replies
5
Views
4K
excelnewb221
E
R
  • Question Question
Using a named range in formula, reading it as cell value
Replies
3
Views
584
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

Share this page

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
Back
Top