Excel Formulas to Get the Nth Matching Value With INDEX Function!!
Tags: excelformula, excelformulas
Published: 2021-01-18 03:55:16

---
title: Excel Formulas to Get the Nth Matching Value With INDEX Function!!
published: true
date: 2021-01-06 14:48:40 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/excel-formulas-nth-matching-value-with-index/
---

In the previous post, we have learned the steps to **[find the position of N<sup>th</sup> matching value](https://geekexcel.com/excel-formulas-to-find-position-of-nth-matching-value/)** in Excel. Now here, we are going to see how to get the N<sup>th</sup> matching value using the **[INDEX](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/)****  **function in Excel. Let’s get into this article!! Get an official version of**  MS Excel** from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

[![Formulas to get the Nth matching value with INDEX](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Formulas-to-get-the-Nth-matching-value-with-INDEX.png)](https://geekexcel.com/excel-formulas-nth-matching-value-with-index/formulas-to-get-the-nth-matching-value-with-index/#main)<figcaption id="caption-attachment-20718">Formulas to get the Nth matching value with INDEX</figcaption>
## Generic Formula:

- Use the below formula to find the Nth matching value.

**=[INDEX](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/)(array,[SMALL](https://geekexcel.com/how-to-use-excel-small-function-in-office-365-with-examples/)([IF](https://geekexcel.com/use-if-function-in-microsoft-excel-365-simple-steps/)(range=value,[ROW](https://geekexcel.com/row-function-in-microsoft-excel-365-how-to-use/)(range)-ROW(INDEX(range,1,1))+1),N))**

## Syntax Explanations:

- **INDEX** – The [**INDEX**  function](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/) returns the value at a given position in a range or array. INDEX is frequently used together with the **[MATCH](https://geekexcel.com/excel-formulas-to-find-position-of-nth-matching-value/)** function.
- **SMALL** – This function returns the [N<sup>th</sup> smallest value from the given range of data](https://geekexcel.com/excel-formulas-find-nth-smallest-value/). Read more on **[SMALL Function](https://geekexcel.com/how-to-use-excel-small-function-in-office-365-with-examples/)**.
- **IF** – The Excel[**IF function**](https://geekexcel.com/use-if-function-in-microsoft-excel-365-simple-steps/) return one value for a TRUE result, and another for a FALSE result.
- **ROW** – It will return the row number for reference. Read more on the **[ROW function](https://geekexcel.com/row-function-in-microsoft-excel-365-how-to-use/)**.
- **Comma symbol (,)** – It is a separator that helps to separate a list of values.
- **Parenthesis ()** – The main purpose of this symbol is to group the elements.
- **Range** – It represents the input ranges in your worksheet.
- **Minus Operator (-)** – This symbol will help to subtract any two values.
- **Plus operator (+)** – This symbol is used to add the values.

## Practical Example:

Let’s see some practical examples to get the N<sup>th</sup> value.

- In the below screenshot, we will enter the input values in **[Column](https://geekexcel.com/excel-office-365-columns/) B**, **Column C** , and **Column D**.

![Input ranges](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Input-ranges.png)<figcaption id="caption-attachment-20719">Input ranges</figcaption>

- Here, we need to find the amount of the specified Order ID.

![Criteria](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Criteria.png)<figcaption id="caption-attachment-20720">Criteria</figcaption>

- **[Enter the formula](https://geekexcel.com/how-to-enter-formula-in-excel/)** in the **[formula bar](https://geekexcel.com/how-to-use-formula-bar-for-various-purpose-in-excel-365/)** section or **[cell](https://geekexcel.com/excel-cells-options/) G3**.

[![Enter a formula](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Enter-a-formula.png)](https://geekexcel.com/?attachment_id=20721#main)<figcaption id="caption-attachment-20721">Enter a formula</figcaption>

- After entering the formula, you need to press **CTRL** + **SHIFT** + **ENTER**  Keys.
- It will display the result as shown in the below image. You can [**copy the formula**](https://geekexcel.com/how-to-copy-a-formula-in-excel/) and paste it into the next cell to get the corresponding values.

![Example](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Example-3.png)<figcaption id="caption-attachment-20717">Result</figcaption>

## A Brief Summary:

Here, we explained the step-by-step procedure to **get the Nth value by using the INDEX function** in Excel. If you have any **questions ![❓](https://s.w.org/images/core/emoji/13.0.1/72x72/2753.png),** feel free to **comment**. Click here to know more on **[Geek Excel](https://geekexcel.com/)** and [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!** Thank you!!

### Related Articles:

- **[Excel Formulas to Find the Exact INDEX Match in Office 365!!](https://geekexcel.com/excel-formulas-find-exact-index-match/)**
- **[Excel Formulas to Find the Last Matching Value from the Text String!!](https://geekexcel.com/excel-formulas-find-last-matching-value/)**