Forum Discussion
Filter function with multiple tables/arrays
I have a sheet with data in it (customers contacts in this example, where columns contain name, surname, company and email) and with the following formula I can use a cell (F1) to enter search criteria and it will return all results with partial matches in any of the columns:
=FILTER(Contacts,(ISNUMBER(SEARCH(F1,Contacts[Email Address])))+(ISNUMBER(SEARCH(F1,Contacts[First Name])))+(ISNUMBER(SEARCH(F1,Contacts[Last Name])))+(ISNUMBER(SEARCH(F1,Contacts[Company]))),"No Record Found")
This works fine, however, I am struggling to figure out how to do something similar if I have multiple sheets/tables, e.g. I'd have sheets:
1) Search
2) Customer batch1
3) Customer batch2
4) Customer batch3
And I'd like to use the 1st sheet - Search - to be able to enter some search criteria in one cell and the results to return hits similar to my original formula, but from all the sheets with raw data.
I've tried googling without much luck.
Thanks.
If you have Microsoft 365, you can use VSTACK to combine the results of three FILTER formulas
=VSTACK(FILTER(data on Customer batch 1), FILTER(data on Customer batch 2), FILTER(data on Customer batch 3))
8 Replies
I'm late to the show but here's my take on this task:
=LET( stack, VSTACK(Contacts, Contacts2, Contacts3), header, Contacts[#Headers], multiplier, IFERROR(SEARCH(input, stack), 0), vector, MMULT(multiplier, SEQUENCE(4, , 1, 0)), results, FILTER(stack, vector <> 0, "None Found"), IFERROR(VSTACK(header, results), "") )greinholds Your question raises lots of questions of its own. For one thing, the FILTER function is capable of retrieving all columns in a table (or selection of them) without needing to specify each, especially if they're contiguous columns in the source table. I wonder what purpose the ISNUMBER function is serving.
Would it be possible for you to post a sample copy of the workbook in question--all real customer names and any other confidential info being replaced with fictitious data--so we can take a look at it? Post on OneDrive or GoogleDrive, with a link pasted here that grants access to it.
Hi,
I can select all columns or 1 column with filter. How do you return 2 columns?
Thanks!
Sorry for raising questions with my question, it was never my intention. I'm just learning. 😄 Maybe my explanations are not the best either as English is not my first language.
Formula I did was based on this video: https://youtu.be/KG7Ih_Yf-fg?si=0_hGry7OrZTETto_
I specified each column since I understood FILTER function would retrieve all columns (as you said) but would do so based on results based on single column, so specifying multiple allows to search for matches in any of them.The IFNUMNBER helps doing partial matches, perhaps this can be done easier too, but the approach I used is described at 04:30 mark in the video linked.
I will see if I can prep some sample file, but it might be too time consuming, I'm afraid.
As per my reply to HansVogelaar above, I am getting there, but now ran into some other error.Thanks.
If you have Microsoft 365, you can use VSTACK to combine the results of three FILTER formulas
=VSTACK(FILTER(data on Customer batch 1), FILTER(data on Customer batch 2), FILTER(data on Customer batch 3))
What can i use if i have more that 3 sets of data to filter?
You can add more FILTER functions to the VSTACK formula.
Thanks, this has got me a lot closer! I've got the formula working with VSTACK, but I've now noticed that for some reason some search parameters return #CALC! error, despite showing correct results with slightly different criteria.
E.G.
Person with a surname Goodwin will appear in results if I search for "win", but I will get an error if I search for "goodwin" or "dwin" or "good".
I noticed similar behaviour with a lot of results, also emails that contain numbers etc. But I cannot put my finger on one single way to reproduce this to narrow down to some root cause.
Thanks.
EDIT:I believe this was happening because I had put wrong [if_empty] in FILTER functions under VSTACK!
