I have 2 collection as below
Table1
Col1 | Col2 |
A,B,C | 1 |
B,C,D | 1 |
A,C,D | 3 |
A,B | 2 |
A,C | 6 |
A,D | 1 |
B,C | 9 |
B,D | 0 |
A,B,D | 7 |
Table2
Col3 |
A |
B |
D |
I am trying to match the item of Table2.Col3 with Table1.Col1 and get Table1.Col2 in return ( expecting 7 in return for A,B,D combination from last row of Table1)
I did try the below below formula but it is not working
Filter(Table1, Col1 in Table2.Col3)
As an alternative, I did reform Table1 as below, kept Table2 as before
Col1 | Col2 | Col3 | Col4 | Col5 |
A,B,C | A | B | C | 1 |
B,C,D | B | C | D | 1 |
A,C,D | A | C | D | 3 |
A,B | A | B | 2 | |
A,C | A | C | 6 | |
A,D | A | D | 1 | |
B,C | B | C | 9 | |
B,D | D | D | 0 | |
A,B,D | A | B | D | 7 |
used the below formula
Filter(Table1, (Col2 = Blank() || Col2 in Table2), (Col3 = Blank() || Col3 in Table2), (Col4 = Blank() || Col4 in Table2)).Col5
What this is doing is, it is returning all the rows where part of the items are there ( getting back data from Table1.Col5 for A,B/A,D/B,D/A,B,D in Table1.Col1)
Any help?