Find a Value In A Table of Cell Each With More Than 2 Values Separated By Commas

I have two tables:

First table M24:R37 has two columns, one column has single values per cell (K24 has A; K25 has B, etc.) second column has multiple values per cell (M24 has Z1, Z2, A1; M25 has B1, B1A, B1B; etc.)

Second table has one column of single values per cell (A1, B1, etc.) starting in cell A5. Second column is blank and would like to be populated with a corresponding value from column 1 in table one (M).

What I would like to do is to create a Vlookup type command that will look for a value in second table Column A from the first table M24:R37 and return value from first column (M) in that table. The issue I am having is with the values in column 2 of table one that have values separated by commas. I do not think excel is recognizing that it is a list of individual values.


A1
B1
B1A
B1B
B1C
etc.


A - Z1,Z2,A1,B5
B - B1,B1A,B1B,B1C
C - D1,D1A,D1B,D1C
D - C1,C1A,C1B
E - D5,D5A
etc. - etc.

I am a little confused about the columns to reference.. but generally you can perhaps use a wildcard.

e.g.

=INDEX($K$24:$K$37,MATCH("*"&A2&"*",$M$24:$M$37,0))

this looks up the value that is in A2 and finds it within the comma separated values in column M and returns what is in column K at the same row....

you may have to rearrange ranges...

Great!

If you are satisfied with the solution provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save