Vlookup - Multiple lookup in single cell

[mattyro1]

Good day all,

I was hoping to get some guidance on a technique to do a vlookup with multiple responses in a single cell.

To be specific:

I have the below in one cell, COLUMN Y, I do a Vlook up that references a table that attaches a simplified name for anything that has a "C" code, such as C34.90, C79.31 etc. My original formula will only find the 1st instance of a C code, but in my example below, there are multiple "C" codes in a cell. I am looking to get all instances of the C code.

Any help would be greatly appreciated.

Thank you!

[R91.1] [C34.90] [R51] [Z92.3] [E86.0] C79.31] [E78.5] [I10] [Z00.00] [T66.XXXA] [R61] [D49.6] [R32, R15.9] [G93.6]

 

[Peter_SSs]

My original formula will only find the 1st instance of a C code,

What is your original formula?

Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also be good to see a small set of dummy data, including the lookup table and the expected results with XL2BB so that we can see the expected layout and have some realistic data to test with.

 

[mattyro1]

Enclosed is my current formula: =VLOOKUP(MID(Y6,SEARCH("[C*",Y6)+1,3),'Diagnosis Table'!$A$1:$B$89,2,FALSE). It does a search within text for anything with a "C" code and returns something more simplified such as C79.31 equals "Secondary malignant neoplasm". Hope this helps a little more to determine the best route to go. As you can see in the list below, there are multiple "C" codes and I need them (Only the "C" codes) to be listed into a single cell.

Lung nodule [R91.1]
Lung cancer (CMS-HCC) [C34.90]
Headache [R51]
Status post radiation therapy [Z92.3]
Dehydration [E86.0]
Metastasis to brain (CMS-HCC) [C79.31]
Hyperlipidemia [E78.5]
Hypertension [I10]
Preventative health care [Z00.00]
Radiation adverse effect [T66.XXXA]
Night sweat [R61]
Brain tumor (CMS-HCC) [D49.6]
Bowel and bladder incontinence [R32, R15.9]
Vasogenic edema (CMS-HCC) [G93.6]

 

[Peter_SSs]

Thanks for updating your profile.

Lung nodule [R91.1]
Lung cancer (CMS-HCC) [C34.90]
Headache [R51]
Status post radiation therapy [Z92.3]
Dehydration [E86.0]
Metastasis to brain (CMS-HCC) [C79.31]
Hyperlipidemia [E78.5]
Hypertension [I10]
Preventative health care [Z00.00]
Radiation adverse effect [T66.XXXA]
Night sweat [R61]
Brain tumor (CMS-HCC) [D49.6]
Bowel and bladder incontinence [R32, R15.9]
Vasogenic edema (CMS-HCC) [G93.6]

What are we looking at here? Is this a single column? Multiple columns?
I can't see how a VLOOKUP could be performed on this data.


This is still the case:

It would also be good to see a small set of dummy data, including the lookup table and the expected results with XL2BB so that we can see the expected layout and have some realistic data to test with.

 

[mattyro1]

HI Peter, my lookup is searching just for the "C" code in a string of medical terminology. It is then cross-referenced with a diagnosis table for a simplified term.
Example: Column "Y" has a result of - Lung cancer (CMS-HCC) [C34.90] , cross-referenced to the diagnosis table, it becomes Malignant neoplasm of bronchus and lung.
The issue that I've encountered is that works great for the 1st lookup instance, but if there are multiple "C" codes listed in Column Y, I lose that visibility. I'd like to be able to capture all instances in a list of "C" codes.

 

[Peter_SSs]

You are very familiar with your data and layout but I am not so your written description is not clarifying enough for me to be able to set up some sample data to test with.
Is there reason that you cannot show a small set of dummy data from each relevant sheet so I can see exactly what is where and how the cross-referencing works?

XL2BB would be best as all forum users can access that but otherwise a sample file uploaded to DropBox,, Google Drive etc with a shared link posted here would at least allow some users to acces that information.