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
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.
- 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.
- In this example, we need to find the Amount of specified Order ID.
- Enter the given formula in the formula bar section or any cell in which you want to show your result.
- Finally, you can get the result as per your data.
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!!
Top comments (0)