Search code examples
google-sheetsgoogle-sheets-formulaspreadsheet

How do I import multiple rows into a different tab in Google Sheets, based on whether they contain a keyword?

I have a database of blogs, all based around different business units (including information like headline, topic, progress, spokesperson, etc).

I'd like to create separate tabs into which I can automatically pull the rows for each business unit (i.e. containing the keyword for "Banking", or "Energy & Utilities", or "Retail" in a certain column).

Unfortunately, despite looking at some tutorials and examples, I haven't been able to find any VLOOKUP, IMPORTRANGE, or INDEX/MATCH (etc.) functions that can help.

Example: I've created an example spreadsheet here: https://docs.google.com/spreadsheets/d/19lHTxLaAgtHz5wg0sLLVw1YSpEXvQycL2PUd6Grs4X0/edit?usp=sharing

I'd like to be able to search column C for every row which contains the keyword "Banking" and automatically import all of these rows into a separate tab, named 'Banking'. That formula could then be repurposed for different tabs for each Business Unit.

Is there a formula or solution that would let me do this?

Thank you!

I attempted VLOOKUP, XLOOKUP, INDEX/MATCH, QUERY(IMPORTRANGE) in a separate sheet, and other related functions, but was unable to repurpose these functions to work for my database. Most efforts resulted in error codes or an inability to parse (although I'm more than willing to admit I probably made a mistake).

Solution

  • You can try either of these in your Banking tab:

    =QUERY('Master sheet'!A:E,"WHERE C='Banking'",1)

    OR

    ={'Master sheet'!A1:E1;FILTER('Master sheet'!A:E,'Master sheet'!C:C="Banking")}

    OR

    =LAMBDA(z,{chooserows(z,1);filter(z,choosecols(z,3)="Banking")})('Master sheet'!A:E)