INDEX / MATCH / with Multiple Results

Hey Gurus! You guys are the best!

Im using Excel 2016

Supposedly attached is an example workbook though I had issues attaching the site kept making me log in

I need help with a formula that searches the CELL for any MATCHES from a list.

The formula would then give me each MATCH from the CELL found in the list.

So, if there is only one result I get that result. If there are two results, I would get a result in one CELL and a result in the next CELL in the same row.

Here is my current formula but it only gives me one result AND it cannot search the cell properly to find correct MATCHES when there are mixed values and sometimes the wrong values are concatenated with the needed values. (it's clear in the example workbook)

=IF(OR(INDEX(COUNTIF(D9,"*"&A$2:A$16&"*"),)), INDEX(A$2:A$16, MATCH(D9, A$2:A$16, 0), 1), "none")

Ideally, the formula should be able to identify the correct list items within the CELL and populate those results, by CELL, in the same ROW, regardless of other characters in the searched CELL.

Thank you guys for any help you can provide!

Dan

Originally Posted by InkyDrinky

Hey Gurus! You guys are the best!

Im using Excel 2016

Supposedly attached is an example workbook though I had issues attaching the site kept making me log in

I need help with a formula that searches the CELL for any MATCHES from a list.

The formula would then give me each MATCH from the CELL found in the list.

So, if there is only one result I get that result. If there are two results, I would get a result in one CELL and a result in the next CELL in the same row.

Here is my current formula but it only gives me one result AND it cannot search the cell properly to find correct MATCHES when there are mixed values and sometimes the wrong values are concatenated with the needed values. (it's clear in the example workbook)

=IF(OR(INDEX(COUNTIF(D9,"*"&A$2:A$16&"*"),)), INDEX(A$2:A$16, MATCH(D9, A$2:A$16, 0), 1), "none")

Ideally, the formula should be able to identify the correct list items within the CELL and populate those results, by CELL, in the same ROW, regardless of other characters in the searched CELL.

Thank you guys for any help you can provide!

Dan

The site will not allow me to attach the workbook... I have tried soo many times not sure what to do??

I am still trying to attach and use commercial services but it say Im not allowed and I have points...

Cannot upload workbook or post to commercial if someone can help me please

Dan

Thanks Sam! Unfortunately after many many tries it did not work. I had to do it on a completely different machine as this laptop the site kept asking me to log in over and over and never recognized me long enough to do it correctly...

Using a different laptop I have reported the old post and moved this to the commercial posts with payment though $$!

Dan