Search code examples
google-sheetsgoogle-sheets-formula

How to get the ROW NUMBER base on Query

How in Google Sheet, I can get the row number based on QUERY.

Col A, I have:

=QUERY(Datas!A:A; "select A WHERE A LIKE 'Test'"))

How in Col B, I can have the row number where this result comes from ?

So for example, if the query result a result from the line #9, it should gave me 9.

Thanks.

Solution

  • Add an Additional Column with Filter() and Row()

    You just need to create a 2 column formula with your current query formula placed on the first column. On the second formula, you should use Row() to get the row number and Filter() to filter out the raw data based on your required search word (in this case, "Test").

    Your formula should look like this:

    ={QUERY(Datas!A:A; "select A WHERE A LIKE 'Test'")\FILTER(ROW(Datas!A:A); Datas!A:A="Test")}

    Output:

    From this sample data (from 'Datas' sheet):

    enter image description here

    The formula will produce the following output:

    enter image description here

    For Regular Expressions:

    To add the LIKE concept to the formula, you may use REGEXMATCH(). This will filter your data based on your regular expression.

    Your new function should look like this:

    ={ARRAYFORMULA(SUBSTITUTE(REGEXEXTRACT(QUERY(Datas!A:A; "select A WHERE A LIKE '%FR SPORTS | TV%'"); "tvg-name=(.*)tvg-logo");"""";""))\FILTER(ROW(Datas!A:A); REGEXMATCH(Datas!A:A; "FR SPORTS | TV"))}

    New Output:

    enter image description here