Searching one Google Sheet to add a row of data to another

Question

I searched and was able to find some similar questions like this, but I wasn't able to find exactly what I'm looking for and I wasn't able to use the formulas in the other questions to make this work. So sorry in advance if this has been asked before!!

Basically, I have multiple Google Sheet files (about 12 in total). Here I'll call them "Master", "Client1", Client2" and so on. For simplicity, I've just named the sheets within the files to be "Sheet1" :)

The column headers in each spreadsheet are "Brand" "Client Name", "Client Location", "Client Email", , etc... In the "Client1", Client2", etc... workbooks the first column will always be the same as the filename.

The "Master" workbook contains information on all of the clients and what "Brand" they work for (or in some cases, which Brands they work for, which makes this tricky).

Here is a picture of the "Master" workbook as an example

As pictured, some have multiple Brands, all are different clients, locations, emails.

This is what the Client1 workbook looks like currently

but I need a formula to insert into the Client1 workbook and search the first column of Master workbook and then display data if column A in Master contains "Client1"

So basically here is what Client1 would need to look like when all is said and done

I've tried things like =IFERROR, =vlookup, =RegExMatch and can't get any of them to work. I know it has to be something simple that I'm doing wrong, but I'm at my wit's end.

Any and all help would be AMAZING!!!!!

Answer 1

if this is your master:

then:

={TRANSPOSE(SPLIT(REPT("client1 ", COUNTA(
  QUERY(QUERY(master!A2:D, "select *", 0), 
  "select Col2,Col3,Col4
   where Col1 contains 'client1'", 0))/3)," ")), 
  QUERY(QUERY(master!A2:D, "select *", 0), 
  "select Col2,Col3,Col4 
   where Col1 contains 'client1'", 0)}