Priti
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.
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.
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.
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.
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.
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.
