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

When we are working with Excel, we often need to find first occurrence of a value from a large dataset range to list out individual products or entries. In this article, we will talk about how we can find the first occurrence of a value in a range in Excel using various functions and their combinations and also by using the Filter command from the Sort and Filter option.


📁 Download Excel File

Download the practice workbook below.


Learn to Find First Occurrence of a Value in a Range in Excel with These 5 Approaches

To demonstrate things easily, we will consider a dataset where we have different car names as products of different companies along with a separate ID for each company. We will utilize this dataset to find the first occurrence of a value within a range in Excel by the following methods. The dataset is shown below:

excel find first occurrence of a value in a range sample dataset


Approach

1. Utilizing Nested ISNUMBER and MATCH Functions

We can use the ISNUMBER Function and MATCH Function together to find the first occurrence in a particular range of data. The steps for this process are as follows:

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, we select cell E6.
  • Then we insert the following formula in the formula bar:
=1-ISNUMBER(MATCH(B6,B$5:B5,0))

🔨 Formula Breakdown

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

👉  Here, the MATCH Function looks for the value Royal Royce Spectre specified in cell B6.

👉  B$5:B5 specifies the array argument where we look for the value in B6.

👉  0 indicates exact match to return as the function output.

👉  ISNUMBER Function checks if the argument value is numeric or not. It returns TRUE  if the value is numeric, FALSE if not.

  • The output in cell E6 will be like the following image.
  • After that, we drag Fill Handle up to cell E13.

  • Thus, the final results will have the value 0 for repeated values like the image shown below. In this way, we can find the first occurrence of a value in a range in Excel with the output 1.

excel find first occurrence of a value in a range using Nested ISNUMBER and MATCH Function

📕 Read More: 5 Ways to Return True If Value Exists in Column in Excel


Approach

2. Using Nested INDEX and SMALL Functions

We can use the INDEX Function nested along with some other Functions like IF Function, ROW Function to get first occurrence values in a column with reference to another column. We can do this combination in 3 different ways. We will now discuss these ways.

Approach 2.1

2.1 Implementing Nested INDEX with SMALL, IF, and ROW Functions

We can use the INDEX Function nested with the SMALL Function IF Function and ROW Function to get first occurrence value from another cell reference. This process is explained in the steps below.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell E6.
  • Then we write the following formula in the formula tab.
=INDEX($D$6:$D$13,SMALL(IF($B$6=$B$6:$B$13,ROW($B$6:$B$13)-ROW($B$6)+1),2))

🔨 Formula Breakdown

INDEX($D$6:$D$13,SMALL(IF($B$6=$B$6:$B$13,ROW($B$6:$B$13)-ROW($B$6)+1),2))

👉  Here, the ROW Function returns a number or numbers representing the rows.

👉  The IF Function puts a logic as the first argument, which is $B$6=$B$6:$B$13 and the following two comma-separated values of ROW Functions are the return values of IF Function if the logic is true and false respectively.

👉  The SMALL Function returns the smallest value from the set of numeric data.

👉  The INDEX Function returns a value where the first argument is $D$6:$D$13 that specifies the array range. The second and third arguments in the nested functions indicate the row number and column number.

  • The image below shows the first occurrence of the Rolls Royce and its ID in cell E6.
  • If we change the value to 2 at the end of the formula instead of 1, we can get the second occurrence value of the same product. Let us change the product ID of a Rolls Royce Spectre to 107 in cell E7. After that, we write the following formula in cell E7. The only change we make is to replace 1 with 2 at the end of the previous formula.
=INDEX($D$6:$D$13,SMALL(IF($B$6=$B$6:$B$13,ROW($B$6:$B$13)-ROW($B$6)+1),2))

  • After that, we press Enter and we will see that the second ID of the same product will show up in cell E7. The output will look like the following image.

excel find first occurrence of a value in a range using Nested INDEX with SMALL, IF and ROW Functions

📕 Read More: Excel Formula: Find Last Row Number with Data

Approach 2.2

2.2 Applying Nested INDEX and MATCH Functions

The INDEX and MATCH Functions make use of their arguments to show us first occurrence similar to the previous method. The steps are as follows:

⬇️⬇️ STEPS ⬇️⬇️

  • First, we select cell E6.
  • Then we insert the following formula in the formula bar
=INDEX($D$6:$D$13,MATCH($B$6,$B$6:$B$13,0))

🔨 Formula Breakdown

INDEX($D$6:$D$13,MATCH($B$6,$B$6:$B$13,0))

👉  Here, $B$6 is the value to look for, ,$B$6:$B$13 is the array range where to look for the value, and 0 is the exact match return output in MATCH Function.

👉  The INDEX Function returns a value where the first argument is $D$6:$D$13 that specifies the array range. The second argument in the nested function indicates the row number.

  • The output in cell E6 will be like the following image. This output, like the previous submethod, shows us the first occurrence of the Rolls Royce and its ID.

excel find first occurrence of a value in a range using Nested INDEX and MATCH Function

Approach 2.3

2.3 Inserting Nested INDEX with ISNUMBER and SEARCH Functions

In this alternative approach, we will use the INDEX Function nested with ISNUMBER and SEARCH Functions to get the first occurrence within a range. This approach matches data with other data and gives only output for duplicates.

⬇️⬇️ STEPS ⬇️⬇️

  • We create a row and specify the names of four individual companies and leave out a company.
  • After that, we select the cell E6 and write the following formula in the formula tab.
=INDEX($F$6:$F$8,MATCH(TRUE,ISNUMBER(SEARCH($F$6:$F$8,B6)),0))

🔨 Formula Breakdown

INDEX($F$6:$F$8,MATCH(TRUE,ISNUMBER(SEARCH($F$6:$F$8,B6)),0))

👉 In the SEARCH Function, $F$6:$F$8 is the range where to look for the entry specified in cell B6.

👉  ISNUMBER Function checks if the argument value is numeric or not. It returns TRUE  if the value is numeric, and FALSE if not.

👉 The TRUE Function returns the value TRUE if the argument inside is true.

👉 Here, the MATCH Function looks for a certain value in argument 1 across a range specified in argument 2 and returns an exact match for value 0.

👉The INDEX Function returns a value where the first argument is$F$6:$F$9 that specifies the array range. The second argument in the nested function indicates the row number.

  • We drag Fill Handle and get the results for the other cells in the same column.
  • The output image will look like the one below. We can see that the two cells do not give value and rather gives an output of #N/A because the Volkswagen Atlas and Toyota Camry are not present more than once and are also absent in the Unique cars column.

excel find first occurrence of a value in a range using Nested INDEX with ISNUMBER and SEARCH Function

📕 Read More: 4 Quick Ways to Find Last Column with Data in Excel


Approach

3. Inserting Combined Functions

Combined Functions consisting of INDEX, FREQUENCY, IF, MATCH, ROW, and ROWS Functions can also find the first occurrence within a range. The steps for this approach are as follows:

⬇️⬇️ STEPS ⬇️⬇️

  • First, we choose cell E6.
  • Then, we choose the formula tab and enter the subsequent formula.
=IF(INDEX(FREQUENCY(IF($D$6:$D$13&"#"&$B$6:$B$13<>"",MATCH("~"&$D$6:$D$13&"#"&$B$6:$B$13,$D$6:$D$13&"#"&$B$6:$B$13,0)),ROW($D$6:$D$13)-ROW($D$6)+1),ROWS($D$6:D6))>0,1,0)

🔨 Formula Breakdown

IF(INDEX(FREQUENCY(IF($D$6:$D$13&”#”&$B$6:$B$13<>””,MATCH(“~”&$D$6:$D$13&”#”&$B$6:$B$13,$D$6:$D$13&”#”&$B$6:$B$13,0)),ROW($D$6:$D$13)-ROW($D$6)+1),ROWS($D$6:D6))>0,1,0)

👉  The ROWS Function returns the number of rows present in an array.

👉  The MATCH Function looks for a certain value in argument 1 that is “~”&$D$6:$D$13&”#”&$B$6:$B$13 across a range specified in argument 2 that is $D$6:$D$13&”#”&$B$6:$B$13 and returns an exact match for value 0. The ampersand (&) sign joins two or more entities.

👉  The IF Function puts a logic as the first argument, which is $D$6:$D$13&”#”&$B$6:$B$13<>”” and the following two comma-separated values are the return values of IF Function if the logic is true and false respectively.

👉  FREQUENCY Function returns a vertical array of frequencies.

👉  The INDEX Function returns a value where the first argument specifies the array range. The second and third arguments in the nested functions indicate the row number and column number.

  • To obtain all of the Result items, we drag Fill Handle up to cell E13 and the results will display 1 for the first occurrence and 0 for repeated occurrences as shown in the following image.

excel find first occurrence of a value in a range using Combined Functions


Approach

4. Applying COUNTIF or COUNTIFS Function

The COUNTIF and COUNTIFS Functions count cells within a range that satisfies a condition. We can use COUNTIF for a single condition and COUNTIFS for multiple conditions.

Approach 4.1

4.1 Utilizing COUNTIF Function

The COUNTIF Function provides a simplistic solution to the problem of finding the first occurrence in Excel. The steps for this process are as follows:

⬇️⬇️ STEPS ⬇️⬇️

  • First, we select the cell E6 and write the following formula in the formula bar to join the text.
=C6&D6

  • Thus, we will see that the texts in the Company and ID columns have merged.
  • Now we select Fill Handle from the bottom right of cell E6.

  • Then, we drag Fill Handle up to cell E13 to get all the joined text entries.

  • Next, we move to cell F6 and write the following formula in the formula bar.
=(COUNTIF($E$6:$E6,$E6)=1)+0

🔨 Formula Breakdown

(COUNTIF($E$6:$E6,$E6)=1)+0

👉  In the COUNTIF Function, the first argument $E$6:$E6 indicates the range of cells to count to look for a criterion.

👉  The second argument $E6 indicates the criterion.

  • The results will look like the following image:

excel find first occurrence of a value in a range using COUNTIF Function

📕 Read More: 3 Ways to Check If Range of Cells Contains Specific Text in Excel

Approach 4.2

4.2 Using COUNTIFS Function

The COUNTIFS Function does not require us to get a joined text helper column. This approach is, therefore, much easier. Here, the COUNTIFS Function is nested with IF Function and N Function to bring together the results.

⬇️⬇️ STEPS ⬇️⬇️

  • At first, we select cell E6.
  • After that, we go to the formula tab and write the following formula down.
=N(COUNTIFS(B$6:B6,B6,D$6:D6,D6)=1)

🔨 Formula Breakdown

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

👉  The COUNTIFS Function can return the count of cells for one or multiple criteria. In this formula, B$6:B6 is the first range and B6 is the first criterion.

👉  D$6:D6 and D6 are the second range and second criterion respectively.

👉  The N Function returns a value converted to a number.

  • We drag Fill Handle up to cell E13 to get all the Result entries.

  • We will find out the results showing 1 for the first occurrence and 0 for more than one occurrence like the following image:

excel find first occurrence of a value in a range using COUNTIFS Function


Approach

5. Using Filter Command

The Filter command can be useful in ways that it can show us the first occurrence of all the individual items after filtering them from a list of datasets. Therefore, the steps for this process are explained below.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we select the cell E6 and write the following formula in the formula tab.
=COUNTIF($B$6:B6,B6)

  • The result will come out in cell E6 with a value of 1 for the first occurrence.
  • After that, we drag Fill Handle up to cell E13.

  • We will see that the results will show the value 1 for the first occurrence and 2 for the second occurrence of a value as the following image:

  • Next, we select the cell range B5:E13 and go to Sort and Filter from the Home option. Then we select the Filter option.

  • A drop-down icon will appear on the column headings. We select the icon of the Result column heading. As a result, a drop-down menu will appear. We check only 1 and then press OK.

  • The result will show only the values of items that have the first occurrence like the image below.

excel find first occurrence of a value in a range using Filter Command


📄  Important Notes

🖊️  We need to find out in which approach we want to show the first occurrence in Excel and select the method accordingly.

🖊️  We should take care while nesting other functions within one function to give arguments of the functions properly.

🖊️  In the second method that uses the Nested INDEX Function with SMALL, IF, and ROW Functions, we should take care while altering the values to obtain desired results properly.


📝  Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌  We can use nested ISNUMBER and MATCH Functions to get binary outputs of the first occurrence values where 1 indicates the first occurrence and 0 is the output of multiple occurrences.

📌  Furthermore, by a combination of INDEX Function nested with other functions, we can get first occurrence outputs in different ways.

📌  In addition, the COUNTIF and COUNTIFS Functions provide the easiest ways of finding the value of the first occurrence.

📌  We can also find the first occurrence by using a combination of functions and by using the Filter command from the Sort and Filter option.


Conclusion

In conclusion, I hope that this article has provided you with insights about how we can find the first occurrence of a value in a range in Excel using a variety of functions. Additionally, if you want to know more about Excel, please visit our site ExcelDen and if you have any queries regarding this topic, feel free to let us know down below in the comment section.

(Visited 58 times, 1 visits today)
Abrar

Abrar

Hello there! I am Mohammad Abrar Uddin, an engineering graduate from BUET. I write articles and blogs related to Excel in this site and hope that the viewers can learn simple to complex solutions of Excel problems easily by reading such articles.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo