The VLOOKUP function stands for Vertical search. It is used when the user needs to find a value in a row or in the whole table.
What is Get nth match with VLOOKUP Function in Excel?
The VLOOKUP function to get nth match is used to organize the data so that when you try to look for a value, it will be displayed on the left side of the return value.
How the VLOOKUP function is used in Excel?
The VLOOKUP function searches for a value in the column of the table and returns a value in the same row of another column. The proper working of the VLOOKUP function is explained in the example.
Formula or Syntax
The syntax of the VLOOKUP function to get nth match is:
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Parameters or Arguments of an nth match with VLOOKUP function
The parameters of the VLOOKUP function are explained below:
Ø Lookup_value: The lookup value can be a value or a reference to be searched in the first column of the table array.
Ø Table_array: The two or more columns of the data are the table array. You can use a reference to a range or the range name. The value of the column of table_array is searched by lookup_value.
Ø Col_index_num: This implies the column number in a table_array from which the value is returned. If the argument value is less than 1, then the function returns #VALUE! Error value. And if it is greater than the number of columns in table_array, the function will return #REF! Error value.
Ø Range_lookup: It is the logical value that specifies whether the function wants to find an exact match or an approximate match.
To get the nth Match with VLOOKUP, you will need a Helper column that can construct a unique id that also consists of the count. Follow the step below:
- Make a table of the respective fields. As the formula entirely depends on the Helper column, add it to your table and construct a unique id with the count.
Figure 1. Construct a Table with Helper Column
- The helper column consists of the formula which builds a unique lookup value from already existing id and counter. The formula will pick up the value in the cell and give results similar as the COUNTIF function. As the VLOOKUP function is unable to search the nth value, the COUNTIF criterion of expanding the range is used, i.e. $E$3:E3.
Figure 2. Insert the VLOOKUP function for nth value
- When you apply the formula, the VLOOKUP function will fetch the value of the nth term of the table with similar method as COUNTIF. The result will be given in the cell you selected.
Figure 3. The function will fetch the lookup value
Note on Usage of function
- Remember that the lookup value must lie in the range. i.e., if your lookup value is D3, then your range must start with D.
Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free.