DEV Community

Andrews
Andrews

Posted on • Originally published at geekexcel.com on

Excel Formulas to Find the Position of Nth Matching Value!!

At some points, we need to find the position of Nth matching value in Excel. How can you calculate them quickly? Today I introduce you to some formulas to solve this problem. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Formulas to get the position of Nth match value
Formulas to get the position of Nth match value

General Formula:

  • To get the position of the nth matching value in Excel, you can use the below formula.

=SMALL(IF(logical,ROW(list)-MIN(ROW(list))+1),n)

Syntax Explanations:

  • SMALL – This function returns the Nth smallest value from the given range of data. Read more on SMALL Function.
  • IF – The ExcelIF function 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.
  • MIN – In Excel, the MIN Function returns the smallest numeric value from the range of input values.
  • 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.
  • List – 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:

  • Now we are going to see how to find the location of the nth value.
  • First, we will give the input ranges in Column C and Column C.
  • Then, enter the given formula in the formula bar section.
  • After that, Press CTRL + SHIFT + ENTER Keys.
  • Finally, we will get the result in Cell G4.

Example
Example

Verdict:

Hope you understood the simple steps to find the position of Nth matching value in Excel. If you have any other ideas or question, do share with us here in the comments section below. Click here to know more on Geek Excel and Excel Formulas !!

Read Ahead:

Top comments (0)