Google Sheets: Find First Occurrence of Value in Column


You can use the following formula to find the first occurrence of a value in a column in Google Sheets:

=COUNTIF($A$2:$A2,$A2)=1

This particular formula returns a value of TRUE for the first occurrence of each unique value in column A and a value of FALSE for all other values.

If you would like to return a 1 or 0 instead, you can use the following formula:

=(COUNTIF($A$2:$A2,$A2)=1)+0

The following example shows how to use these formulas in practice.

Example: Find First Occurrence of a Value in Column in Google Sheets

Suppose we have the following dataset that contains information about points scored by basketball players on various teams:

We can type the following formula into cell C2 to return either TRUE or FALSE to indicate whether or not the team name in cell A2 is the first occurrence of that team name in column A:

=COUNTIF($A$2:$A2,$A2)=1

We can then click and drag this formula down to each remaining cell in column C:

The formula returns either TRUE or FALSE to indicate whether or not the corresponding team name in column A is the first occurrence of that team name.

For example:

  • The value of “Rockets” in row 2 receives a value of TRUE since it is the first occurrence of that team name in column A.
  • The value of “Spurs” in row 3 receives a value of TRUE since it is the first occurrence of that team name in column A.
  • The value of “Spurs” in row 4 receives a value of FALSE since it is not the first occurrence of that team name in column A.

And so on.

If you would instead like to return a value of 1 or 0, you can type the following formula into cell C2:

=(COUNTIF($A$2:$A2,$A2)=1)+0

You can then click and drag this formula down to each remaining cell in column C:

Excel find first occurrence of value in column

Now the formula returns either 1 or 0 to indicate whether or not the corresponding team name in column A is the first occurrence of that team name.

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Create Pivot Table with Count of Occurrences in Google Sheets
How to Count Specific Characters in Google Sheets
How to Count Specific Words in Google Sheets

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *