The article provides some easy methods on how to find the first **occurrence **of a value in a **column **in Excel. Sometimes we need to identify duplicate items or data in our Excel sheet. To find them, we need to find the first **occurrence **of a value in a **column**. Here we are using a dataset which contains **IDs **and **Names **of some guys.

**Table of Contents**hide

## Download Practice Workbook

## 5 Ways to Find First Occurrence of a Value in a Column in Excel

**1. Using Excel COUNTIF Function to Find First Occurrence of a Value in a Column**

Suppose we want to identify the **first** occurrences of the **names **in the dataset. If any name **occurs twice or more** in this dataset, we will mark them as **0s**, otherwise, it will be marked as **1**. We can do this using **the COUNTIF function**. Let’s see the process below.

**Steps:**

- Make a new
**column**to identify the**occurrences**and type the following formula in cell**D5**.

`=(COUNTIF($C$5:$C5,$C5)=1)+0`

Here, **the COUNTIF function** keeps returning **TRUE **until it finds the same **name **in the **column C**. We added a **0 **(**zero**) to get the numerical value.

- Hit
**ENTER**and you will see the output in cell**D5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells and this operation will mark subsequent**occurrences**of the**names**as**0**.

Thus you can easily identify the first **occurrence** of a value in a **column**.

**Read More:** **How to Find Last Occurrence of a Value in a Column in Excel (5 Methods)**

**2. Applying COUNTIFS Function to Find First Occurrence of a Value in a Column**

We can also find the first **occurrence** by this using **the COUNTIFS function**. Suppose we want to identify the **first occurrences** of the **names **in the dataset. If any **name occurs twice or more** in this dataset, we will mark them as **0s**, otherwise, we mark them as **1**. Let’s discuss the process below.

**Steps:**

- Make a new
**column**to identify the**occurrences**and type the following formula in cell**D5**.

`=N(COUNTIFS(C$5:C5,C5)=1)`

Here, **the COUNTIFS function** keeps returning **TRUE **until it finds the same **name **in the **column C**. **The N function** converts **TRUE **or **FALSE **to **1 **or **0 **respectively.

- Hit
**ENTER**and you will see the output in cell**D5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells and this operation will mark subsequent**occurrences**of the**names**as**0**.

Thus you can easily identify the first **occurrence** of a value in a **column**.

**Read More:** **How to Find Value in Column in Excel (4 Methods)**

**3. Find First Occurrence of a Value in a Column by Utilizing Excel ISNUMBER and MATCH Functions **

Applying **the ISNUMBER function** along with **the MATCH function** can be useful to find the first **occurrence** of a value in a **column**. Suppose we want to identify the **first** occurrences of the **names **in the dataset. If any name **occurs twice or more** in this dataset, we will mark them as **0s**, otherwise, we mark them as **1**. Let’s see the procedure below.

**Steps:**

- Make a new
**column**to identify the**occurrences**and type the following formula in cell**D5**.

`=1-ISNUMBER(MATCH(C5,C$4:C4,0))`

Here,** the MATCH function **searches for the value in **C5**, **looks up** through the **range C4:C4 **and returns the position where it finds an exact match. **The ISNUMBER function** returns **TRUE **if it finds a numeric value in it, otherwise it returns **FALSE **even if it has an error in it.

- Press the
**ENTER**button and you will see the output in cell**D5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells and this operation will mark subsequent**occurrences**of the**names**as**0**.

Thus you can easily identify the first **occurrence** of a value in a **column**.

**Read More: How to Find Top 5 Values and Names in Excel (8 Useful Ways)**

**4. Finding First Occurrence of a Value by Using Combined Functions **

We can also find the **first** occurrence of a value or data in a **column **by combining **IF**, **INDEX**, **FREQUENCY**, **MATCH **and **ROW** functions. Suppose we want to identify the **first** occurrences of the **IDs **in the dataset. If any **ID** occurs twice or more in this dataset, we will mark them as **0s**, otherwise, we mark them as **1**. The formula will be a little bit messy. Let’s go through the description below.

**Steps:**

- Make a new
**column**to identify the**occurrences**and type the following formula in cell**D5**.

`=IF(INDEX(FREQUENCY(IF($B$5:$B$13&"#"&$C$5:$C$13<>"",MATCH("~"&$B$5:$B$13&"#"&$C$5:$C$13,$B$5:$B$13&"#"&$C$5:$C$13,0)),ROW($B$5:$B$13)-ROW($B$5)+1),ROWS($B$5:B5))>0,1,0)`

Here, **the IF function** returns **1** (**TRUE**) when it meets the criteria, else it returns **0** (**FALSE**). **The FREQUENCY function** determines the number of times a value **occurs** within a given range of values.

**Formula Breakdown**

**ROWS($B$5:B5) —->**Returns**Output**:**1**

**ROW($B$5:$B$13) —->**Becomes**Output: {5;6;7;8;9;10;11;12;13}**

**ROW($B$5) —->**Turns into**Output: {5}**

**MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0) —->**Becomes**Output**:**{1;2;3;4;5;1;7;2;9}**

**IF($B$5:$B$13&”#”&$C$5:$C$13<>””,MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0)) —->**Turns into**IF($B$5:$B$13&”#”&$C$5:$C$13<>””,{1;2;3;4;5;1;7;2;9}) —->**leaves**Output**:**{1;2;3;4;5;1;7;2;9}**

**FREQUENCY(IF($B$5:$B$13&”#”&$C$5:$C$13<>””,MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0)),ROW($B$5:$B$13)-ROW($B$5)+1) —->**Becomes**FREQUENCY(IF{1;2;3;4;5;1;7;2;9}),{5;6;7;8;9;10;11;12;13}-{5}+1) —->**Turns into**Output**:**{2;2;1;1;1;0;1;0;1;0}**

**INDEX(FREQUENCY(IF($B$5:$B$13&”#”&$C$5:$C$13<>””,MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0)),ROW($B$5:$B$13)-ROW($B$5)+1) —->**Returns**INDEX({2;2;1;1;1;0;1;0;1;0})****Output: {2}**

**IF(INDEX(FREQUENCY(IF($B$5:$B$13&”#”&$C$5:$C$13<>””,MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0)),ROW($B$5:$B$13)-ROW($B$5)+1),ROWS($B$5:B5))>0,1,0) —->**Simplifies to**IF({2}>0,1,0)****Output: 1**

Finally, we get the output as **1 **because the **ID **in cell **B5 occurs** for the first time.

- Hit
**ENTER**and you will see the output in cell**D5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells and this operation will mark subsequent**occurrences**of the**names**as**0**.

Thus you can easily identify the first **occurrence** of a value in a **column**.

**Read More:** **How to Find Value in Column Using VBA in Excel (4 Ways)**

**5. Using Filter Command to Sort First Occurrences of Values in a Column**

Suppose you want to see the repeat times of the **names **in **column D** and hence you want to see the position of the** first** occurrences of these **names**. We can do this by applying **the Filter command**. Please go through the description below.

**Steps:**

- Make a new
**column**to identify the**occurrences**and type the following formula in cell**D5**.

`=COUNTIF($C$5:C5,C5)`

Here, **the COUNTIF function** returns the number of times a **name** occurs in **column C**.

- Now, hit
**ENTER**and you will see the output in cell**D5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells and this operation will mark subsequent**occurrences**of the**names**as**0**.

Thus you can see the number of times a name **occurs** in **column D**.

- To
**Filter**the first**occurrences**, select the**range B4:D13**and go to**Home**>>**Sort & Filter**>>**Filter**

- Click on the marked
**arrow**in the**Match header**. Mark**1**and then click**OK**.

- After that, you will see all the duplicate
**IDs**removed by the**filtering**. Only the**first**occurrences of the**IDs**will appear.

Thus you can find only the first **occurrence **and **Filter **them in a column.

**Read More: How to Get Cell Value by Row and Column in Excel VBA**

## Practice Section

In the following figure, I give you the dataset that we used in this article so that you can practice these examples on your own.

## Conclusion

In conclusion, the key focus of this article is to provide some easy approach to find the first **occurrences **of some given values in a **column **in Excel. We used pretty basic functions for this purpose. If you have any better methods or ideas or feedback, please leave them into the comment box. This will help me enrich my upcoming articles.

Just wanted to thank you for a simple solution to a problem I was having. Was able to add one of your suggestions to an already complex formula and fix my issue the first time. Thank you for your effort to create articles of this type.

Hey Pam, it’s really nice to see you get benefit from my article. It’s also an honor because I work with sincerity for these articles so the readers can have their solutions in the easiest way possible. I appreciate your compliment and also hope that my other articles can be useful for you as well. You are welcome!!!