Lookup the Second, the Third, or the Nth Value in Excel

When it comes to looking up data in Excel, there are a few amazing functions that I often use, XLOOKUP, 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.

XLOOKUP can also give you the last match, but not the second or third by itself.

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.

For example, John has taken Excel, PowerPoint and Word training. But looking up his name with VLOOKUP or INDEX/MATCH always returns Excel, his first training in the list.

In this tutorial, I’ll show you FILTER formulas for the Nth match and every match at once in Excel 365, 2024 and 2021.

Then I’ll show you a helper column you can use with XLOOKUP, VLOOKUP or INDEX MATCH, and an INDEX/SMALL array formula that needs no helper column.

Let’s get started.

Get the Nth Match with INDEX and FILTER

Works in: Excel for Microsoft 365, Excel 2024 and Excel 2021

If you only need one training, you can tell the formula which match to return.

Below I have employee names and their training, and I want John’s second training in E3.

E1 contains the employee’s name, John, and E2 contains the match number, 2.

Enter this formula in E3 and press Enter:

=IFERROR(INDEX(FILTER(B2:B14,A2:A14=E1),E2),"")

It returns PowerPoint, John’s second training, which sits in row 8.

The FILTER function finds John’s trainings and returns them as a list: Excel, PowerPoint and Word.

INDEX picks the item at the position given in E2. Since E2 contains 2, it returns the second item, PowerPoint.

Change E2 to 3 to get Word, the third match. You can also change E1 to another employee’s name.

FILTER creates the list inside the formula, but INDEX returns just one item to E3. You don’t need to drag the formula or use Ctrl+Shift+Enter.

If you enter 4 in E2, the INDEX function returns #REF! because John has only three trainings.

If the name isn’t in the list, the FILTER function returns #CALC!.

The IFERROR function returns a blank for both errors.

If you need the last occurrence, that tutorial shows you how to find it.

For older versions, use the helper column or array formula methods below.

List All Matches Across Columns with FILTER

Works in: Excel for Microsoft 365, Excel 2024 and Excel 2021

Now let’s list all the training in front of each employee’s name, with one training per column.

Below I have employee names and their training, and I want to list each employee’s training across a row, starting in column E.

In E2, enter this formula and press Enter, then copy it down to E9:

=TRANSPOSE(FILTER($B$2:$B$14,$A$2:$A$14=D2,""))

John’s row shows Excel, PowerPoint and Word in E2:G2. Barb’s row shows Excel and Word, while Jill’s row shows Word and Excel.

Scott gets PowerPoint and Excel. Stan, Louise and Martha each get Excel, while Greg gets PowerPoint.

The FILTER function returns the training for the name in D2 as a vertical list.

The TRANSPOSE function turns that list into a row, so the results fill across the columns.

If the employee has no match, the FILTER function returns a blank because its if_empty argument is "".

Each formula spills to the right on its own across as many columns as that person has trainings. You don’t need to drag it across or use Ctrl+Shift+Enter.

You still copy the formula down because each employee needs a separate row of results. One formula fills that person’s whole row.

The Training 1 to Training 3 headers are typed by hand. If someone has a fourth training, the formula spills into column H, so add a header there.

If you get #SPILL!, something is blocking the cells where the results need to go. Clear those cells and try again.

Pro Tip: An Excel Table includes new training rows automatically and makes formulas easier with structured references. Convert the data with Ctrl+T.

These examples use range references. With a Table named Training, the formula uses structured references instead:

=TRANSPOSE(FILTER(Training[Training Done],Training[Name]=D2,""))

If you want all the matches separated by commas in one cell, see how to return multiple values in one cell.

Create a Helper Column for Any Lookup Function

Works in: All versions of Excel

XLOOKUP, VLOOKUP and INDEX/MATCH all stop at the first match by default. To get this done, we can use a helper column and create unique lookup values in it.

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.

Below I have employee names and their training, and I want to create a unique lookup value for each entry in column C.

This helper column sits beside the data, so the original names and training stay as they are.

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

In C2, enter this formula and copy it down to C14:

=A2&COUNTIF($A$2:$A2,A2)

C2:C14 now contains John1, Stan1, Barb1, Scott1, Jill1, Louise1, John2, Greg1, Martha1, Jill2, John3, Barb2 and Scott2.

The COUNTIF formula makes each employee’s name unique by adding a number. The first John becomes John1, the second becomes John2, and so on.

The range $A$2:$A2 grows as you copy down, so the COUNTIF function counts how many times the name has appeared so far.

The & joins that count to the name, giving each entry a unique lookup value.

The next three sections use this helper column. Pick the one for the lookup function you use.

Get the Second or Nth Match with XLOOKUP

Works in: Excel for Microsoft 365, Excel 2024 and Excel 2021

XLOOKUP returns the first match, or the last with its search mode. It can’t return the second match by itself, so we’ll use the helper column.

If you have XLOOKUP, you also have FILTER. The first two sections show how to get the Nth match or all matches without a helper column.

Below I have the employee training list and the helper column from earlier, and I want each employee’s training across a row, starting in column F.

In F2, enter this formula and copy it across and down to fill F2:H9:

=XLOOKUP($E2&COLUMNS($F$1:F1),$C$2:$C$14,$B$2:$B$14,"")

John’s row shows Excel, PowerPoint and Word in F2:H2. Barb gets Excel and Word, Scott gets PowerPoint and Excel, and Jill gets Word and Excel.

Stan, Louise and Martha each get Excel, while Greg gets PowerPoint. The remaining result cells are blank.

$E2&COLUMNS($F$1:F1) builds the lookup value. In F2 it becomes “John1”, in G2 “John2”, and in H2 “John3”.

The range $F$1:F1 widens as you copy right, so the COLUMNS function returns 1, 2 and 3.

The dollar sign in $E2 keeps the employee’s name in column E as you copy across.

XLOOKUP finds that value in the helper column C and returns the training from column B.

When there’s no Nth match, the XLOOKUP function’s if_not_found argument ("") shows a blank, so you don’t need the IFNA function.

VLOOKUP Second Match with a Helper Column

Works in: Excel 2013 and later (Ctrl+Shift+Enter in Excel 2019 and earlier)

If you prefer VLOOKUP or use an older Excel version, the helper column lets you return each employee’s second, third or Nth training.

Below I have the employee training list and the helper column from earlier, and I want each employee’s training across a row, starting in column F.

Enter the following formula in F2:

=IFNA(VLOOKUP($E2&COLUMNS($F$1:F1),CHOOSE({1,2},$C$2:$C$14,$B$2:$B$14),2,0),"")

In Excel 2019 and earlier, press Ctrl+Shift+Enter to enter the formula. In Excel 2021 and Microsoft 365, just press Enter.

Then copy it across and down to fill F2:H9.

The formula returns each employee’s trainings in the order they appear in the list. It returns a blank when there’s no further training.

John’s row shows Excel, PowerPoint and Word in F2:H2. Barb gets Excel and Word, Scott gets PowerPoint and Excel, and Jill gets Word and Excel.

Stan, Louise and Martha each get Excel, while Greg gets PowerPoint. The remaining result cells are blank.

How does this formula work?

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 F2 it becomes “John1”, in G2 it becomes “John2”, and so on.

As you copy right, $F$1:F1 widens, so the COLUMNS function returns 1, 2 and 3.

The dollar sign in $E2 keeps the name column fixed as you copy across.

VLOOKUP can only return a value from a column to the right of the column it searches. Here, the helper is in C, to the right of Training Done in B.

CHOOSE({1,2},$C$2:$C$14,$B$2:$B$14) builds a two-column table inside the formula, with the helper first and Training Done second.

VLOOKUP searches the first column of that table. The 2 returns the training from the second column, and 0 means an exact match.

IFNA returns a blank instead of #N/A when there’s no Nth match.

XLOOKUP in the earlier section and INDEX MATCH in the next section are easier here because they can return a column to the left without CHOOSE.

Find the Second or Nth Match with INDEX MATCH

Works in: Excel 2013 and later

If you use INDEX MATCH, you can keep the helper column on the right and return each employee’s trainings across a row.

Below I have the employee training list and the helper column from earlier, and I want each employee’s training across a row, starting in column F.

In F2, enter this formula and copy it across and down to fill F2:H9:

=IFNA(INDEX($B$2:$B$14,MATCH($E2&COLUMNS($F$1:F1),$C$2:$C$14,0)),"")

John’s row shows Excel, PowerPoint and Word in F2:H2. Barb gets Excel and Word, Scott gets PowerPoint and Excel, and Jill gets Word and Excel.

Stan, Louise and Martha each get Excel, while Greg gets PowerPoint. The remaining result cells are blank.

$E2&COLUMNS($F$1:F1) becomes “John1” in F2, “John2” in G2 and “John3” in H2 as you copy across.

The MATCH function finds that value’s position in the helper column C. The 0 asks for an exact match.

The INDEX function then returns the training at that position from column B.

INDEX/MATCH can return a column to the left of the lookup column directly, so it doesn’t need the CHOOSE step VLOOKUP needs.

IFNA returns a blank when there’s no Nth match.

Use INDEX, SMALL and IF in Older Excel

Works in: All versions of Excel (Ctrl+Shift+Enter in Excel 2019 and earlier)

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.

This method works in older Excel versions that don’t have FILTER.

Below I have employee names and their training, and I want to list each employee’s training across a row, starting in column E.

Enter this formula in E2:

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

In Excel 2019 and earlier, use Ctrl+Shift+Enter. Hold the Control and Shift keys and press Enter to confirm the array formula.

In Excel for Microsoft 365, Excel 2021 and later, just press Enter. Copy the formula across to G2 and down to row 9 to fill E2:G9.

John gets Excel, PowerPoint and Word in E2:G2. Barb gets Excel and Word, Scott gets PowerPoint and Excel, and Jill gets Word and Excel.

Stan, Louise and Martha each get Excel, while Greg gets PowerPoint. The cells for missing trainings show blanks.

How does this formula work?

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

$A$2:$A$14=$D2 compares each cell in A2:A14 with D2. Since D2 contains John, it checks whether each cell contains “John”.

It returns an array of TRUE or FALSE. A cell containing John gives TRUE; any other name gives FALSE. Here is the resulting array:

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

TRUE appears in the 1st, 7th and 11th positions, which is where John appears in the list.

The next part uses IF to turn those TRUE values into positions:

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

ROW($A$2:$A$14)-1 gives the positions in the list. IF replaces TRUE with those positions and FALSE with "" (blank), giving this array:

{1;"";"";"";"";"";7;"";"";"";11;"";""}

Note that 1, 7 and 11 are the positions where John occurs. Subtracting 1 changes worksheet rows 2 to 14 into list positions 1 to 13.

Important: If data starts outside row 2, subtracting 1 can silently return wrong rows. Use ROW($A$2:$A$14)-ROW($A$2)+1 with your actual data range instead.

That version subtracts the first data row and adds 1. It gives the same list positions wherever your data starts, so John’s positions remain 1, 7 and 11.

Next, SMALL picks the first, second or third smallest number from that array:

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

The COLUMNS function supplies the match number, or k, that SMALL needs.

In E2, COLUMNS($E$1:E1) returns 1, so SMALL returns 1. In F2, the range widens to $E$1:F1, so COLUMNS returns 2 and SMALL returns 7.

In G2, COLUMNS returns 3 and SMALL returns 11. These are John’s first, second and third positions in the training list.

INDEX uses the position returned by SMALL to get the training from column B:

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

In E2, INDEX returns Excel, the first item in B2:B14. In F2, it returns PowerPoint, the seventh item. In G2, it returns Word, the eleventh item.

Some employees have only one or two trainings, so there’s no position for a later match. The formula would return an error for those missing trainings.

The IFERROR function returns a blank in place of that error.

What do you do when you have to look-up the second, third, or the Nth value? I am sure there are more ways to do this.

If you use something easier than the one listed here, do share with us all in the comments section.

You May Also Like the Following Excel Tutorials:

• VLOOKUP vs INDEX/MATCH
• INDEX MATCH Combo in Excel (examples)
• How to make VLOOKUP Case Sensitive
• How to Use VLOOKUP with Multiple Criteria
• Use IFERROR with VLOOKUP to Get Rid of #N/A Errors
• Find the Second Highest Value in Excel
• VLOOKUP vs XLOOKUP
• INDEX MATCH with Multiple Criteria in Excel