Table of Contents
In Google Sheets Query, you can use the ROW() function to get the row number of the current cell. This is a built-in function that requires no arguments and returns the row number of the cell where it is used. The output is an integer, starting from 1 for the first row and increasing for each row below it. This is useful for displaying row numbers or for referencing a specific row in a formula.
You can use the following syntax in a Google Sheets query to add a column that contains row numbers:
=ARRAYFORMULA(QUERY({A2:B11, ROW(A2:B11)}, "SELECT Col1, Col3 WHERE Col1 = 'Mavs'",0))
This particular query will select the values from column Col1 (column A) and Col3 (column C) where the value in Col1 is equal to “Mavs” – this query also assumes that Col3 is blank so that Col3 will simply be used to find the row numbers.
Note: We must wrap the QUERY in an ARRAYFORMULA so that we can get the row numbers for each row and not just the first row.
The following example shows how to use this syntax in practice.
Example: Get Row Number in Google Sheets Queries
Suppose we have the following dataset that contains information about various basketball players:
We can use the following formula to select all of the values in column A where the team is equal to “Mavs” and select the row number for each row as well:
=ARRAYFORMULA(QUERY({A2:B11, ROW(A2:B11)}, "SELECT Col1, Col3 WHERE Col1 = 'Mavs'",0))
The following screenshot shows how to use this query in practice:
The query returns the values from column A where the team is equal to “Mavs” along with the row number for each of these rows.
From the output we can see that the players on the “Mavs” team are found in rows 2, 3, and 6 in the original dataset.
The following tutorials explain how to perform other common operations in Google Sheets: