Forum Discussion
Search of multiple values connecting multiple tables
ema_rz Now, I didn't recreate your tables, 6 levels deep, but did a mock-up at a smaller scale in Power Query. Although you mentioned not to be "practical" with PQ, you might be able to follow the steps in the attached (not so elegant) example. With a number of consecutive merge and expand steps, starting with the last table, you will end up with the sequence of items from the first column in the first table that correspond to the item selected from the second column of the last table.
Here's an example of how it should work.
Here is a horrible formula (name in B19):
=IFERROR(TEXTJOIN(";";TRUE;FILTER(Table2[Column1];ISNUMBER(MATCH(Table2[Column2];FILTER(Table3[Column2];ISNUMBER(MATCH(Table3[Column1];FILTER(Table4[Column1];ISNUMBER(MATCH(Table4[Column2];FILTER(Table5[Column2];ISNUMBER(MATCH(Table5[Column1];FILTER(Table6[Column1];ISNUMBER(MATCH(Table6[Column2];FILTER(Table7[Column1];ISNUMBER(MATCH(Table7[Column2];B19;0)));0)));0)));0)));0)));0))));"")
You'll have to replace the names of the tables and columns.
HansVogelaar Unfortunately it doesn't work properly.
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? And explain where the formula fails? Thanks in advance.
