0

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!

Improve this question
2
  • Hi and welcome to WebApps. Would you please clarify a few things? 1) need to split up data because sheets are getting too long How many actual rows and columns of data are in each sheet? 2) Have you tried creating a second formula for "Data2", and "stacking" the results of both formulas in an array (using curly brackets)?
    – Tedinoz
    Apr 25, 2022 at 0:35
  • Splitting data into multiple sheets is usually not the best strategy. You may want to consolidate the data back into one sheet and use tools like filter views to manage the data. See the Filter views example spreadsheet.
    – doubleunary
    Apr 25, 2022 at 7:34

1 Answer 1

Reset to default
0

Use { array expressions } to stack the data, like this:

=filter( { Data1!C4:C; Data2!C4:C }, { len(Data1!E4:E); len(Data2!E4:E) } )

Improve this answer
1
  • Yes! @doubleunary that worked perfectly! Thank you!
    – Andrew
    Apr 29, 2022 at 5:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.