Forum Discussion

greinholds's avatar
greinholds
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's avatar
    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's avatar
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))

  • M_Carr1996's avatar
    M_Carr1996
    Copper Contributor
    Mar 31, 2025

    What can i use if i have more that 3 sets of data to filter?

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Apr 02, 2025

      You can add more FILTER functions to the VSTACK formula.

  • greinholds's avatar
    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!

     

Resources