Return a value found in another table in any column

[Clilly]

Dear community,

I'm having a hard time finding the right formula to do the following:

I have a Table 1 that contains references, one reference per row. I have a Table 2 that contains references spread accross multiple columns, so up to 12 references per rows.

I need to find a way to tell if the reference in Table 1, is found in Table 2 (any column).

I have tried look ups but I can only manage to look up one column, not the whole table 2. 

I hope you can help me find a solution,

Thanks

Table 1 - this is the table I get from our database:

ID	Reference	Result
1	556-0000025443L
2	556-0000029463L
3	556-0000025835L

Table 2 - this is the table I get from our database

Reference 1	Reference 2	Reference 3
556-0000025443L	556-0000026001L	556-0000026671
556-0000026730L	556-0000026732
556-0000025833L	556-0000025835L

Result for Table 1- this is the table I would like to get

ID	Reference	Result
1	556-0000025443L	Found
2	556-0000029463L
3	556-0000025835L	Found

[Ahmedx · Accepted solution]

pls try this

[Ahmedx · Accepted solution]

if your table2 is very large, then it would be better for you to write it like this

[
a =List.Buffer( List.Combine( Table.ToColumns(Table2))),
b = if List.Contains(a,[#"Reference "]) then "Found" else ""][b]

[shafiz_p · Accepted solution]

Hi @Clilly  Connect both the table to power query. Keep table as it is. Create a new custom column in Table1 and write code as in the image below:

Checking each value of Table1 Reference with the Tab21 each 3 reference column, if matched, then it would be found else blank.

Hope this helps!!

[Ahmedx · Accepted solution]

pls try this

[Ahmedx · Accepted solution]

if your table2 is very large, then it would be better for you to write it like this

[
a =List.Buffer( List.Combine( Table.ToColumns(Table2))),
b = if List.Contains(a,[#"Reference "]) then "Found" else ""][b]