How to vlookup second column?

When it comes to looking up data in Excel, there are two amazing functions that I often use – VLOOKUP and INDEX (mostly in conjunction with the MATCH function).

However, these formulas are designed to find only the first instance of the lookup value.

But what if you want to look-up the second, third, fourth or the Nth value.

Well, it’s doable with a little bit of extra work.

In this tutorial, I will show you various ways (with examples) on how to look up the second or the Nth value in Excel.

In this tutorial, I will cover two ways to look-up the second or the Nth value in Excel:

Let’s get started and dive right in.

Suppose you are a training coordinator in an organization and have a dataset as shown below. You want to list all the training in front of an employee’s name.

In the above dataset, the employees have been given training on different Microsoft Office tools (Excel, PowerPoint, and Word).

Now, you can use the VLOOKUP function or the INDEX/MATCH combo to find the training an employee has completed. However, it will only return the first matching instance.

For example, in the case of John, he has taken all the three training, but when I look up his name with VLOOKUP or INDEX/MATCH, it will always return ‘Excel’, which is the first training for his name in the list.

To get this done, we can use a helper column and create unique lookup values in it.

Here are the steps:

The above formula would return the training for each employee in the order it appears on the list. In case there are no training listed for an employee, it returns a blank.

How does this formula work?

The COUNTIF formula in the helper column makes each employee’s name unique by adding a number to it. For example, the first instance of John becomes John1, the second instance becomes John2 and so on.

The VLOOKUP formula now uses these unique employee names to find the matching training.

Note that $E2&COLUMNS($F$1:F1) is the lookup value in the formula. This would add a number to the employee name based on the column number. For example, when this formula is used in cell F2, the lookup value becomes “John1”. In cell G2, it becomes “John2” and so on.

If you don’t want to alter the original dataset by adding helper columns, you can also use an array formula to look up the second, third, or the nth value.

Suppose you have the same dataset as shown below:

Here is the formula that will return the correct lookup value:

Copy this formula and paste it in cell E2.

Note that this is an array formula and you need to use Control + Shift + Enter (hold the Control and Shift keys and press the Enter key), instead of hitting just the Enter key.

Click here to download the example file.

How does this formula work?

Let’s break this formula into parts and see how it works.

$A$2:$A$14=$D2

The above part of the formula compares each cell in A2:A14 with the value in D2. In this dataset, it checks whether a cell contains the name “John” or not.

It returns an array of TRUE of FALSE. If the cell has the name ‘John’ it would be True, else it would be False.

Below is the array you would get in this example:

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

Note that it has TRUE in 1st, 7th and 111th position, as there is where the name John appears in the dataset.

IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,””)

The above IF formula uses the array of TRUE and FALSE, and replaces TRUE with the position of its occurrence in the list (given by ROW($A$2:$A$14)-1) and FALSE with “” (blanks). The following is the resulting array you get with this IF formula:

{1;””;””;””;””;””;7;””;””;””;11;””;””}

Note than 1, 7, and 11 are the position of occurence of John in the list.

SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,””),COLUMNS($E$1:E1))

The SMALL function now picks the first smallest, second smallest, third smallest number from this array. Note that it uses the COLUMNS function to generate the column number. In cell E2, the COLUMNS function returns 1 and the SMALL function returns 1. In cell F2, COLUMNS function returns 2 and the SMALL function returns 7.

INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14=$D2,ROW($A$2:$A$14)-1,””),COLUMNS($E$1:E1)))

INDEX function now returns the value from the list in Column B based on the position returned by the SMALL function. Hence, in cell E2, it returns ‘Excel’, which is the first item in B2:B14. In cell F2, it returns PowerPoint, which is the 7th item in the list.

Since there are cases where there are only one or two training for some employees, INDEX function would return an error. The IFERROR function is used to return a blank in place of the error.

Note that in this examples, I have used range references. However, in practical examples, it’s beneficial to convert he data into an Excel Table. By converting into an Excel Table, you can use structured references, which makes it easier to create formulas. Also, an Excel Table can automatically account for any new training items that are added to the list (so you don’t have to adjust the formulas every time).

• For lookup_value (1st argument), use the topmost cell from List 1.
• For table_array (2nd argument), supply the entire List 2.
• For col_index_num (3rd argument), use 1 as there is just one column in the array.
• For range_lookup (4th argument), set FALSE - exact match.

Supposing you have two columns with Products and quantities as below screenshot shown. For quickly finding out the quantities of the first or second banana, what would you do?

Please do as follows to find the first, 2nd or nth match value in Excel.

1. In cell D1, enter the criteria which you want to vlookup, here I enter Banana.

2. Here we will find the first match value of banana. Select a blank cell such as E2, copy and paste formula =INDEX($B$2:$B$6,MATCH(TRUE,EXACT($D$1,$A$2:$A$6),0)) into the Formula Bar, and then press Ctrl + Shift + Enter keys simultaneously.

Note: In this formula, $B$2:$B$6 is the range of the matching values; $A$2:$A$6 is the range with all the criteria for vlookup; $D$1 is the cell containing the specified vlookup criteria.

Then you will get the first match value of banana in cell E2. With this formula, you can only get the first corresponding value based on your criteria.

To get any nth relative values, you can apply the following formula: =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1)) + Ctrl + Shift + Enter keys together, this formula will return the first matched value.

Notes:

1. To find the second match value, please change the above formula to =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),2)), and then press Ctrl + Shift + Enter keys simultaneously. See screenshot:

2. The last number in the above formula means the nth match value of the vlookup criteria. If you change it to 3, it will get the third match value, and change to n, the nth match value will be found out.

You can easily find the first match value in Excel without remembering formulas with the Look for a value in list formula formula of Kutools for Excel.

1. Select a cell for locating the first matching value (says cell E2), and then click Kutools > Formula Helper > Formula Helper. See screenshot:

3. In the Formula Helper dialog box, please configure as follows:

Now the corresponding cell value will be auto-populated in cell C10 based on drop-down list selection.

VLOOKUP is one of the most useful functions in Excel, but after learning its use most people quickly realize some of its limitations.

How can you get around these limitations and use VLOOKUP in any situation whether the results column is to the right or left and without worrying about adding columns and breaking the formula later on?

We can use the CHOOSE function with the VLOOKUP function.

In this example the formula is looking up a value in Lookup Column and gives a result in Results Column. The CHOOSE function references each column individually and then creates a two column range for the the VLOOKUP. This avoids the issue of a changing column index number when a new column is inserted as the index number will always be 2.