How to lookup multiple values based on multiple criteria in one cell?

[Anonymous]

Hello,

Can someone help me guide how to look-up values for a cell which contains multiple values? 

Below is the problem description with a small sample data:

1. Input Table 1:

Below is the first input table with one column that contains the values that I want to lookup. Each cell contains multiple values as shown.

2. Input table 2 (Lookup Table)

The lookup table below contains 2 columns, the first column matches the content of table 1 above. Column 2 is the value that I want to lookup.

3. Desired Output

Below is the desired output where values of "LookupTable Column2" from the 2nd table get matched up to the "INPUTCOLUMN" from table 1. I want the output in this format since i want finally extract the output in an excel just the way displayed in the snapshot below.

Is it possible to achieve this in powerbi?

Thanks in advance

[ryan_mayu]

@Anonymous 

i don't know why the line change code does not work in powerbi. So I did this in excel

pls see the attachment below

[Anonymous]

@ryan_mayu Is the attachment correct?

This is what i see and this seems incorrect.
Also, if you were able to get the desired output like the picture I posted in my initial post, how did you do it?

Thanks!

[ryan_mayu]

pls see the attachment. 

The wrap does not work after you load the data. However, you can see it works in the power query.

[Anonymous]

ok.. i am a little confused, so a solution does not exist?

If it exists can you post the powerBI file?

Also, I forgot to mention one more thing is that in your last snapshot (output) i want the "Valid, invalid, N/A" in a separate column as shown in my Desired output snapshot in initial post.