I've got 2 sheets setup with exactly the same format (need to split up data because sheets are getting too long) and I'm currently able to filter through 1 column, see if there's any content, and if there is, to display a corresponding cell from that row into a new column. Problem is, I want to be able to search through multiple sheets within the same document, and pull all the relevant results. I don't know how to have it output the results into a single column.
=IFERROR(FILTER(Data1!C4:C,(Data1!E4:E<>"")*(Data2!E4:E<>"")),"")
Here you can see I'm filtering and pulling data from 2 columns, one from Data1 starting in column E4 and down, and one also from Data2 column E4 and down, and if it matches my criteria, it displays the data I have in column C. Currently this is working as is, but I don't know how to add in Data2 (Data2!C4:C) into the mix. It's only bringing in from Data1 which is all I've got defined in that formula. I get errors anytime I try injecting Data2. I just don't know enough about proper syntax with this… any help would be great!
Best Answer
Use { array expressions } to stack the data, like this:
=filter( { Data1!C4:C; Data2!C4:C }, { len(Data1!E4:E); len(Data2!E4:E) } )