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

Patrick2788's avatar
Patrick2788
Silver Contributor
Jan 05, 2024

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

Resources