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

=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 Excel

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.

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

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

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.

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