In Excel, we can find the first occurrence of a value in a column. **COUNTIF** is one of the heavily used statistical functions used to count the number of active cells that meet a criterion, such as the number of periods a specific name appears within a student’s list. So, we will demonstrate 5 diverse ways to the first occurrence of a value in a column in Excel.

**📁 Download Excel File**

Download the Excel file used for the demonstration from the link below.

## Learn to Find First Occurrence of a Value in a Column in Excel with These 5 Handy Methods

Firstly, let us get acquainted with our dataset used throughout the article as an example. We have the name of the students. Now, we want to know the first occurrence of each name. Hence, we will use this dataset to demonstrate 5 simple methods to remove letters in a cell in Excel.

**Approach 1**

**1. Using COUNTIF Function**

In this method, we used a simple formula containing **the COUNTIF function** to find the first occurrence of a value in a column in Excel. The **COUNTIF **function usually counts the occurrence of a value in a range. So, let us see the steps to apply this function.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, insert the formula in cell
**C6**.

**=(COUNTIF($B$6:$B6,$B6)=1)+0**

**COUNTIF** returns the count of cell numbers that has data of **B6 **in the range **$B$6:$B6**.

- After that, select cell
**C6**again and drag the**Fill Handle**Finally, the outcome is as follows.

**Approach 2**

**2. Combining COUNTIF Function with Filter Feature**

In this second method, we will combine **COUNTIF function** and **Filter** option to find the first occurrence of a value in a column in Excel. So, let us see the steps to apply this function.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, insert the formula in cell
**C6**.

**=COUNTIF($B$6:B6,B6)**

Here, **COUNTIF** returns the count of cell numbers that has data of **B6 **in the range **$B$6:$B6**.

- After that, select cell
**C6**again and drag the**Fill Handle**.

- Then, select range
**C5:C15**and go to**Filter**option in the**Data**tool of**Home**.

- Next, a dropdown will appear beside the
**Occurrence**Click on that and select**1**option from there and we will have all the first occurred data filtered.

- Finally, the outcome is as follows.

**Approach 3**

**3. Employing COUNTIFS Function**

This method will employ **COUNTIFS** function for finding the first occurrence of a value in Excel. The **COUNTIFS** function counts how many times all criteria are met across multiple ranges. Let us see the steps now.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, insert the formula in cell
**C6**.

**=N(COUNTIFS(B$6:B6,B6)=1)**

**🔨 Formula Breakdown**

Here, the formula we inserted in cell **C6** is:

**N(COUNTIFS(B$6:B6,B6)=1)**

`👉`

**COUNTIFS** returns the count of cell numbers that has data of **B6 **in the range **$B$6:$B6**.

`👉`

Lastly, the **N** function returns a number if the output of **COUNTIF **function equals **1**.

- After that, select cell
**C6**again and drag the**Fill Handle**Lastly, the outcome is as follows.

**Approach 4**

**4. Applying ISNUMBER and MATCH Functions**

Now, this method will employ **ISNUMBER** and **MATCH** functions for finding the first occurrence of a value in Excel. The **ISNUMBER** function returns a value referring to a number and **MATCH** function returns the relative position of a specified item in a range. Let us see the steps now.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, insert the formula in cell
**C6**.

**=1-ISNUMBER(MATCH(B6,B$5:B5,0))**

**🔨 Formula Breakdown**

Here, the formula we inserted in cell **C6** is:

**1-ISNUMBER(MATCH(B6,B$5:B5,0))**

`👉`

**MATCH** searches for the value in **B6** in the range **B$5:B5 **and returns the relative position of the cell if it is an exact match.

`👉`

Then, **ISNUMBER** function returns the number of **MATCH** function returns any value.

- Then, select cell
**C6**once more and drag the**Fill Handle**Finally, the result is as follows.

**Approach 5**

**5. Merging IF, INDEX and FREQUENCY Functions**

Now, this method will employ a formula combining** IF****,**** INDEX****, ****FREQUENCY****, ****ROW****, ****ROWS****, **and **MATCH** functions to find the first occurrence of a value in Excel. The **IF **function returns a blank cell if there is no match and returns “**Same**” otherwise and the **INDEX** function gives you a reference at the intersection of rows or columns on a chosen range. Then, the **FREQUENCY** function calculates the occurrence of a certain value in an array and **ROWS** function returns the row number of a certain cell. Lastly, **MATCH** function returns the relative position of a specified item in a range.

Since this formula applies to an array and gives output based on two criteria. So, we added another row containing the **ID** of students to our previous data.

Let us see the steps now.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, insert the formula in cell
**D6**.

**=IF(INDEX(FREQUENCY(IF($B$6:$B$15&”#”&$C$6:$C$15<>””,MATCH(“~”&$B$6:$B$15&”#”&$C$6:$C$15,$B$6:$B$15&”#”&$C$6:$C$15,0)),ROW($B$6:$B$15)-ROW($B$6)+1),ROWS($B$6:B6))>0,1,0)**

**🔨 Formula Breakdown**

Here, the formula we inserted in cell **D6** is:

**IF(INDEX(FREQUENCY(IF($B$6:$B$15&”#”&$C$6:$C$15<>””,MATCH(“~”&$B$6:$B$15&”#”&$C$6:$C$15,$B$6:$B$15&”#”&$C$6:$C$15,0)),ROW($B$6:$B$15)-ROW($B$6)+1),ROWS($B$6:B6))>0,1,0)**

** 👉 ROW($B$6) **returns the row number of cell

**B6**.

** 👉 ROWS($B$6:B6) **returns the row number of the array

**$B$6:B6**.

** 👉 MATCH** searches for the value in

**“~”&$B$6:$B$15&”#”&$C$6:$C$15**in the range

**$B$6:$B$15&”#”&$C$6:$C$15**and returns the relative position of the cell if it is an exact match.

`👉`

Then,**IF** function tests the logical test **$B$6:$B$15&”#”&$C$6:$C$15<>””** and returns output from **MATCH** function if it is true.

** 👉 FREQUENCY** function returns the frequency of output of

**IF**function.

** 👉 INDEX** function returns the output at the intersection of chosen range.

`👉`

Lastly, **IF** function returns **1** if the **INDEX** gives desired output otherwise returns** 0**.

- Then, select cell
**C6**once more and drag the**Fill Handle**Finally, the result is as follows.

## How to Find Last Occurrence of Value in a Column in Excel

This method will demonstrate how to find the last occurrence of value using **XLOOKUP** function in Excel. **XLOOKUP** function returns the first exact match or approximate match that it finds in a range. Here, we have a serial no of name entries now we want to know a student’s last entry serial.

Let us see the steps now.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, insert the formula in cell
**C18**.

**=XLOOKUP(B18,C6:C15,B6:B15,,,-1)**

Here, **B18** contains the value to lookup and **C6:C15** is the lookup array and **B6:B15 **is the return array. **-1 **indicates to search the value from last to first in the array.

## 📝 Takeaways from This Article

`📌`

This article demonstrated 5 methods to find the first occurrence of a value in a column in Excel.

`📌`

In the first method, we used **COUNTIF **to find the same.

`📌`

Then, we combined **COUNTIF** and **Filter** to find the first occurrence of a value.

`📌`

After that, we employed **COUNTIFS **function to find the first occurrence of a value in a column in Excel.

`📌`

Next, we applied **ISNUMBER** and **MATCH** functions to find the first occurrence of a specific value.

`📌`

Finally, we employed a formula to find the first occurrence of a value in a column in Excel.

## Conclusion

This article has demonstrated 5 methods to find the first occurrence of a value in a column in Excel. All these formulas are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit **Excelden.com**.