FILTER() With Multiple Tables

Introduction

Last week, I answered a question on quora, How do I find matches in multiple Excel tables using the FILTER() function? I answered the question in several ways, including Data Validation, using Power Query and using the new FILTER() function.

I did what the OP asked but I was left with the problem that the question related to multiple tables needing to be searched/filtered but I had been unable to get FILTER() to work with all tables at the same time. Well, that's not strictly true as I used Power Query to Append the tables and then used FILTER() to find the answers the OP was looking for.

By chance, out of the blue, I came across a page (link at the end of this page) by Debra Dalgleish, Excel MVP and all round good egg, that led me to the solution: how to use FILTER() across multiple tables.

The Problem

What follows is my own example. There is nothing special about it but just to say, I simplified the example given that the OP's tables are 500 lines long ...

Imagine I use several words and phrases in an article or a page or an Excel file, along with many other words and phrases. These words are special in that they relate to proteins and not to anything more general. I would like, at the end of a task, to find when and where I used or found the proteins. I created a list of what are probably random protein names:

• Cadherin
• Ependymin
• Integrin
• NCAM
• Selectin

And I then created four small tables in which you might or might not find these proteins referred to:

On a table by table basis, I used the FILTER() function as you can see below:

Since it might be difficult to read from that image, here is the first of those formulas, in whch you will find the name of the table to be one_6:

=FILTER(one_6,(one_6[Name]=R5)+(one_6[Name]=R6)+(one_6[Name]=R7)+(one_6[Name]=R8)+(one_6[Name]=R9),"No match")

R5:R9 contains the names of the proteins.

FILTER() gives this output, again, to help you with what might be difficult to read in the screenshot and the screenshot is illustrating the results of having used the Data Validation filter:

Multiple Tables

The FILTER() function works really well with that single table but the OP wanted a list that shows all results across all tables. To cut a long story short, here is a two part solution:

• FILTER(), SUBSTITUTE() and TEXTJOIN() working together
• LET() function

The first formula looks horrifically long but in truth it is really just my individual FILTER() functions nested inside TEXTJOIN() and that is nested inside SUBSTITUTE().

=SUBSTITUTE(TEXTJOIN(", ",TRUE,FILTER(one_6,(one_6[Name]=R5)+(one_6[Name]=R6)+(one_6[Name]=R7)+(one_6[Name]=R8)+(one_6[Name]=R9),"No match"))&CHAR(10)&TEXTJOIN(", ",TRUE,FILTER(two_7,(two_7[Name]=R5)+(two_7[Name]=R6)+(two_7[Name]=R7)+(two_7[Name]=R8)+(two_7[Name]=R9),"No match"))&CHAR(10)&TEXTJOIN(", ",TRUE,FILTER(three8,(three8[Name]=R5)+(three8[Name]=R6)+(three8[Name]=R7)+(three8[Name]=R8)+(three8[Name]=R9),"No match"))&CHAR(10)&TEXTJOIN(", ",TRUE,FILTER(four9,(four9[Name]=R5)+(four9[Name]=R6)+(four9[Name]=R7)+(four9[Name]=R8)+(four9[Name]=R9),"No match")), CHAR(10) & ", ",CHAR(10))

The output of that formula is:

The only other thing to say is that I used Wrap Text for this single cell that contains this output.

The LET() Function doesn't add a great deal to this first solution except that it is an excuse to use it and to package the whole process in one place. My LET() formula is:

=LET(a,TEXTJOIN(", ",TRUE,FILTER(one_6,(one_6[Name]=R5)+(one_6[Name]=R6)+(one_6[Name]=R7)+(one_6[Name]=R8)+(one_6[Name]=R9),"No match"))&CHAR(10),b,TEXTJOIN(", ",TRUE,FILTER(two_7,(two_7[Name]=R5)+(two_7[Name]=R6)+(two_7[Name]=R7)+(two_7[Name]=R8)+(two_7[Name]=R9),"No match"))&CHAR(10),c,TEXTJOIN(", ",TRUE,FILTER(three8,(three8[Name]=R5)+(three8[Name]=R6)+(three8[Name]=R7)+(three8[Name]=R8)+(three8[Name]=R9),"No match"))&CHAR(10),d,TEXTJOIN(", ",TRUE,FILTER(four9,(four9[Name]=R5)+(four9[Name]=R6)+(four9[Name]=R7)+(four9[Name]=R8)+(four9[Name]=R9),"No match")),SUBSTITUTE(a&b&c&d, CHAR(10) & ", ",CHAR(10)))

Again, it looks worse than it is and in my Excel file, you can see that I have wrapped the text again to make the formula itself as well as the output much easier to read.

The output also looks the same as my first multi table formula, above.

You can download my file from the link below and the Debra Dalgleish page link is also below.

Conclusions

I have been using the FILTER() function for a while now and it really is flexible and not that difficult to use. Learning how to use it across multiple tables is a useful bonus.

In the Excel file, you will be able to see all of the above but not the data validation and not the Power Query versions of my solution ... do write and ask me for that file if you are interested.

Excel File 

Debra Dalglish page

Duncan Williamson

29th January 2021