Search code examples
excelgoogle-sheetsgoogle-sheets-query

List all the rows that match a string in a column

My ultimate goal:

enter image description here

I want to look in a specific column to match a specific string and list all the rows that match the string in the column.

Note: I am doing a personal MS Excel on my computer but I'm also doing it in Google Sheets - to share with partners. I am not sure if the formulas are the same for both but as long as either works I'll be happy. The two spreadsheets have different listings but I'm still looking to apply it to both somehow. So I suppose this question is for Google Sheets but if anyone knows how to do it for Excel, or both, please let me know!

Solution

  • In Google Spreadsheets please enter in G11: 

    =query(A:D,"select A,B,C where D=1")

    Change the last 1 to 2 for L11 ... and so forth.

    To exclude the labels: 

    =query(A:D,"select A,B,C where D=1 label A '', B '', C '' ")

    Or, much neater!, courtesy @Mogsdad:

    You can exclude the headers with the headers parameter;
    =query(A:D,"select A,B,C where D=1", FALSE)