Forum Discussion

Copper Contributor

Jan 05, 2024

Solved

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 criter...

excel

Formulas and Functions

HansVogelaar
Jan 05, 2024
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))

HansVogelaar

MVP

Jan 05, 2024

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))

greinholds

Copper Contributor

Jan 05, 2024

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!