Find First Occurrence of a Value in a Range in Excel (3 Ways)

In the corporate world, we are used to seeing databases where a few things occurred more than once. One may want to find out the first occurrence of a value in a range. Microsoft Excel has various useful formulas to perform this task with ease. The article will explain 3 different formulas with variations in them to find the first occurrence of a value in a range in Excel.


Download Practice Workbook

For practice, you can download the workbook from here.


3 Ways to Find First Occurrence of a Value in a Range in Excel

We will use the following dataset to explain formulas to find the first occurrence of a value in a range in Excel.

Excel find first occurrence of a value in range: Sample dataset

The dataset contains two columns with the product name and ID of the products. You can notice there are repetitions of the values in the columns. We need to find the first occurrence of a value in the range. We will explain three different formulas to do this. Go through the rest of the article to know about the formulas and how they work to find the first occurrence of a value in a range in Excel.


1. Using the COUNTIF or COUNTIFS Functions to Find the First Occurrence of a Value in a Range in Excel

COUNTIF is an easy and useful function to find the first occurrence of a value in a range. It takes single or multiple criteria and ranges based on singular or plural function usage. We will see each of them below.

1.1 Using the COUNTIF Function

The easiest formula to find the first occurrence of a value in a range is using the COUNTIF function.

Follow the steps to find the occurrence of a value in a range:

  • Write a formula to join the data of two columns in a new column.

The formula: =B5&C5.

This joins the two data.

  • Drag down the Fill handle icon (the (+) like sign at the right bottom of the first cell) and you will get the result of joining two data into a single column as below.

Joining columns into a single column

  • After that, in a new column write the formula shown below:
=(COUNTIF($D$5:$D5,$D5)=1)+0
  • Now, drag accordingly to get the result for all the rows. Follow picture numbering for this.

Result of using COUNTIF formula

The result shows 1 for the values of the first occurrence in the range of cells D5:D11.

Note: Instead of adding zero we can use N Function nested with COUNTIF to get the same result.


1.2 Using COUNTIFS with N Function

The above method is a bit slow. For faster results, you can use the COUNTIFS function nested with the N function.

The formula for the dataset will be as follows:

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

Result of using nested COUNTIFS and N formula

The result will be the same as method 1(a).

The formula is the same as the method. The only difference is that here we do not need a join column. Again, COUNTIFS can take multiple ranges and criteria.


Similar Readings:


2. Using the Nested ISNUMBER and MATCH functions to Find the First Occurrence of a Value in a Range in Excel

Moreover, you can also use nest the ISNUMBER and the MATCH functions to find the first occurrence of a value in a range.

The formula will be:

=1- ISNUMBER(MATCH(B5,B$4:B4,0))

Result of using ISNUMBER and MATCH nested formula

The result shows 1 for the first occurrence of the values in the range.


3. Using the Nested INDEX with other Functions to Find First Occurrence of a Value in a Range in Excel

Furthermore, we can extract values of the first occurrence in a column by the reference of another column using INDEX nested with other functions such as MATCH, SMALL, IF, SEARCH, and so forth. We will see a few of them below.

3.1 Using the Nested INDEX and MATCH Functions

Let’s start with the nested INDEX and MATCH formula.

The formula for the given dataset will be:

=INDEX($C$5:$C$11,MATCH($B$5,$B$5:$B$11,0))

Result of using nested INDEX and MATCH

The result shows the value of Cell C5 with the first occurrence of the value of Cell B5 in the range B5:B11.


3.2 Using the Nested INDEX with SMALL, IF, and ROW Functions

Besides, the INDEX function can also be nested with functions like the SMALL, IF and ROW functions to get the desired value of the first occurrence of a column from the reference of another one in the range.

The formula is:

=INDEX($C$5:$C$11,SMALL(IF($B$5=$B$5:$B$11,ROW($B$5:$B$11)-ROW($B$5)+1),1))

Result of using INDEX with SMALL, IF, ROW functions

The result will be the same as method 3(a) of this section.

Furthermore, with this formula, you can also get the value of the 2nd time occurring value in the range by changing the 1 at the end of the formula by 2.

Let us change the ID number for the 2nd occurred “Wi-Fi Router” value to “#11_00″.

Changing value in dataset for second occurrence of value in range

The result will show the ID of the 2nd time occurring value named “Wi-Fi Router”.

Result for 2nd time occurrence of value in range

The result shows “#11_00” which is the ID number of the 2nd time occurring value in the range.


3.3 Using the Nested INDEX with ISNUMBER & SEARCH Functions

Finally, we are going to use another formula with the nested INDEX, ISNUMBER, and SEARCH functions which matches a data with another given data and gives outputs for duplicates only.

The formula for this will be:

=INDEX($E$5:$E$7,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0))

Follow the picture for steps needed to apply this formula.

Result of using INDEX with ISNUMBER and SEARCH functions

You can notice that the output at Cell D9 shows invalid results. It is because it has no duplicates within the range.


Things to Remember

1. You have to use the Fill handle icon to drag the formula for finding results for the rest of the values in the range. Apply this where the pictures show down-arrow.

2. You have to understand how you want your result and then apply any of the methods which suit you.


Conclusion

The article explains three different formulas to find the first occurrence of a value in the range in Excel. The formulas include functions like COUNTIF, INDEX, ISNUMBER, SMALL, ROW, MATCH, and so on. I hope the article has helped you find your solution. If you have any further query you can write those in the comment section.


Related Readings

Syeda Fahima Nazreen

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo