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:
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.
📕 Read More: 5 Ways to Return True If Value Exists in Column in Excel
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.
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.
📕 Read More: Excel Formula: Find Last Row Number with Data
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.
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.
📕 Read More: 4 Quick Ways to Find Last Column with Data in Excel
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.
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.
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:
📕 Read More: 3 Ways to Check If Range of Cells Contains Specific Text in Excel
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:
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.
📄 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.