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.

**Table of Contents**Expand

**How to ****Find First Occurrence of a Value in a Range in Excel: 3 Handy Ways**

**Find First Occurrence of a Value in a Range in Excel: 3 Handy Ways**

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

In this scenario, the dataset contains two columns with the **“ Product Name“** and

**“**of the products where you can notice repetitions of the values in the columns. Now, we need to find the first occurrence of a value in the range and explain three different formulas to do this. Therefore, 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.

*ID*“**1. Using COUNTIF or COUNTIFS Functions **

**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 Utilizing COUNTIF Function**

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

📌 **Steps:**

- First of all, enter the formula given below into the
**D5**cell >> drag the**Fill handle**icon to join the data.

`=B5&C5`

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

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

**Read More: How to Find First Occurrence of a Value in a Column in Excel
**

#### 1.2 Implementing 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**.

📌 **Steps:**

- Now, copy and paste the expression into the
**D5**cell.

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

**Formula Breakdown**

**COUNTIFS(B$5:B5,B5,C$5:C5,C5)****→**counts the number of cells specified by a given set of conditions and criteria. Here, the**B$5:B5**cells represent theargument that refers to the**criteria_range1***“Product Name”*, whereas the**B5**argument. Then, the**criteria1****C$5:C5**cells represent theargument that refers to the**criteria_range2***“ID”*, whereas the**C5**argument.**criteria2****Output****→****1**

**N(COUNTIFS(B$5:B5,B5,C$5:C5,C5)=1)****→ converts a non-number value to a number.****Output****→****1**

The result will be the same as** Method 1.1**.

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

**2. Applying Nested ISNUMBER and MATCH Functions**

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

📌 **Steps:**

- To begin with, type in the equation in cell
**D5**as below.

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

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

**Formula Breakdown**

**MATCH(B5,B$4:B4,0) →**returns the relative position of an item in an array matching the given value. Here,**B5**is theargument that refers to the**lookup_value***Wi-Fi Router*. Following,**B$4:B4**represents theargument from where the value is matched. Lastly,**lookup_array****0**is the optionalargument which indicates the**match_type****Exact match**criteria.**Output → #N/A**

**ISNUMBER(MATCH(B5,B$4:B4,0)) →**checks whether a value is a number and returns**TRUE**or**FALSE**. Here,**MATCH(B5,B$4:B4,0)**is theargument, and it returns**value****FALSE**.**Output → FALSE**

**1-FALSE → 1**

**3. ****Employing Nested INDEX with other Functions**

**Employing Nested INDEX with other Functions**

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 Nested INDEX and MATCH Functions**

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

📌 **Steps:**

- Here, the formula for the given dataset will be:

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

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 Utilizing Nested INDEX with SMALL, IF, and ROW Functions**

Besides, the **INDEX **function** **can also be nested with functions like **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.

📌 **Steps:**

- For instance, 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))`

**Formula Breakdown**

**IF($B$5=$B$5:$B$11,ROW($B$5:$B$11)-ROW($B$5)+1)**checks whether a condition is met and returns one value if**→****TRUE**and another value if**FALSE**. Here,**$B$5=$B$5:$B$11**argument while the function returns**logical_test****ROW($B$5:$B$11)-ROW($B$5)+1**argument) otherwise it returns (**value_if_true**argument).**value_if_false****SMALL(IF($B$5=$B$5:$B$11,ROW($B$5:$B$11)-ROW($B$5)+1),1)****→ returns the kth smallest value in data set.****Output****→****1**

**INDEX($C$5:$C$11,SMALL(IF($B$5=$B$5:$B$11,ROW($B$5:$B$11)-ROW($B$5)+1),1))****→**returns a value at the intersection of a row and column in a given range. In this expression, the**$C$5:$C$11**is theargument. Next,**array****SMALL(IF($B$5=$B$5:$B$11,ROW($B$5:$B$11)-ROW($B$5)+1),1)**is theargument that indicates the row location.**row_num****Output****→****“#11_01”**

Further, the result will be the same as **Method 3.1 **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**. - Now, let’s change the
**ID**number for the 2nd occurred “**Wi-Fi Router”**value to “**#11_00″**.

At this point, the result shows **“#11_00”** which is the ID number of the 2nd time-occurring of the value “**Wi-Fi Router” **in the range.

#### 3.3 **Applying Nested INDEX with ISNUMBER & SEARCH Functions**

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

📌 **Steps:**

- In this case, select cell
**D5**and insert the formula as shown below.

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

In addition, you can notice that the output at **Cell D9** shows invalid results. It is because it has no duplicates within the range.

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

Lastly, we can use the** XLOOKUP **function to obtain the last occurrence of a value in a column in Excel.

📌 **Steps:**

- Initially, enter the formula given below into the
**G5**cell.

`=XLOOKUP(G4,C5:C12,D5:D12,,,-1)`

Here, the **C5:C12** and **D5:D12** cells refer to the **“Item”** and **“Price”** columns.

Admittedly, we’ve skipped other relevant examples of how to find the last occurrence of a value in Excel, which you may explore if you wish.

**Things to Remember**

- Firstly, use the
**Fill handle**icon to drag the formula for finding results for the rest of the values in the range. - Second, you have to understand how you want your result and then apply any of the methods which suit you.

## Practice Section

Additionally, we have provided a** Practice** section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

**Download Practice Workbook**

## Conclusion

To sum up, this 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 queries you can write those in the comment section.

## Related Readings

- How to Find Multiple Values in Excel
- How to Find First Value Greater Than in Excel
- Find Last Value in Column Greater than Zero in Excel
- How to Find Value in Column in Excel

**<< Go Back to Find Value in Range | Excel Range | Learn Excel**