Search and result

Can someone please help me with my search request? I am looking for a way to find information in a dataset.

I tried to find the solution with the formula ?=WEGLATEN(REDUCE(0;A2#;LAMBDA(y;z;VERT.STAPELEN(y;ALS(z="";"";TEKST.COMBINEREN(", ";;UNIEK(NAAR.KOLOM(BYCOL(TEKST.SPLITSEN(z;", ");LAMBDA(x;FILTER(Blad2!B2:B151;Blad2!A2:A151=x)));3)))))));1)?

Unfortunately, it?s not working.

Here you find the Excel file:

Excel search.xlsx

------------

Find the value from column A of Sheet 1 in column A of Sheet 2, taking into account values separated by a comma. If the value is found, display the corresponding value from column B of Sheet 2.

Power Query

Please Login or Register  to view this content.

Your query stops working if you add a row with more than 6 numbers in a cell.

The third step is hard coded

Please Login or Register  to view this content.

With your formula it should be

Formula:

Please Login or Register  to view this content.

In Dutch (hopefully)

Formula:

Please Login or Register  to view this content.

Thank you HansDouwe for your help. You gave me the correct answer very quickly. I can now continue building my Excel. Have a nice day!

Glad to help & thanks for the feedback.

To also show a dynamic Power Query solution

PHP Code: 

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    result = Table.AddColumn(Source, "Custom", each let r = Table.FromList(Text.Split(Text.From([MM]),", ")) in Text.Combine(Table.Join(r, "Column1",Table1, "test")[Mitigating Measures],"#(lf)"))
in
    result