DEV Community

loading...

Excel Formulas to Get the Nth Matching Value Using VLOOKUP Function!!

excelgeek profile image Andrews Originally published at geekexcel.com on ・3 min read

So far we have learned how to get the Nth matching value with the INDEX function. Here we will show the formula to get the Nth occurrence of a value by using the VLOOKUP function in Excel. 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 Nth value with VLOOKUP
Formulas to get the Nth value with VLOOKUP

General Formula:

  • The below formula will help you to get the Nth Matching value.

=VLOOKUP(id_formula, data,column,0)

ID Formula =A2&”-“&COUNTIF($A$2:A2,A2)

Syntax Explanations:

  • VLOOKUP – In Excel, this function will help to lookup data in a range or table by row. Read more on the VLOOKUP Function.
  • COUNTIF – This function will help to count the number of cells that meet a single condition or criteria. Read more of the COUNTIF function.
  • Absolute Reference – The absolute reference is an actual fixed location in a worksheet.
  • 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.
  • Data – It represents the input ranges in your worksheet.
  • Column – It represents the column Number of your input data.

Example:

Please do as follows to find the Nth occurrence of a value in Excel.

  • First, you need to give the input values.

Input Ranges
Input Ranges

  • The Nth occurrence of a value by using the VLOOKUP function depends on a helper column, which is added at the first column of the source data table.
  • The helper column contains the given ID formula.

Helper column
Helper column

  • In this example, we need to find the Amount of specified Order ID.

Criteria
Criteria

Enter a formula
Enter a formula

  • Finally, you can get the result as per your data.

Result
Result

Wrap-Up:

So yeah guys, this is how we can get the Nth occurrence of the given criteria in Excel by using the VLOOKUP function. Hope you like it. If you have any queries, don’t forget to let me know. Click here to know more on Geek Excel and Excel Formulas !! Thank you!! 🙏

Read Ahead:

Discussion (0)

pic
Editor guide