Find The Nth Entry In a List

Introduction

Excel has great tools to search and manipulate data in a workbook. The functions INDEX(), MATCH(), VLOOKUP are just a few. However there are times when the requirements are ‘special’ and Excel would not have a function that can help.

One request I regularly see in posts is to find the nth match in a column/row. The VLOOKUP() and MATCH() functions can only return the first match (especially with text data). What if occasionally we need the 2nd or 3rd match? Is there a way to do that?

Download the Zip file containing a PDF document with the sample Excel workbook used in this post. The PDF documents is the full tutorial.

Good News

There are a few solutions scattered around multiple posts and many look scary for an intermediate Excel user. I finally found one that is good enough for users familiar with some of the lookup family of functions mentioned earlier.

Below is the definition of the formula. I will clearly explain how it works:

=SMALL(IF([range]=”[search string]”,ROW([range]),””),[position])

It’s pretty simple really.

• Range — The range containing the data you want to search. It is used twice.
• Search string — The entry you want to search.
• Position — The ordinal position of the item to find (1st, 2nd, etc.)

Please download and open the provided workbook Find nth smallest.xlsx and follow along. In the list on the right you notice that the first column contains duplicates. You need to find any instance of an entry in the Items column and return its corresponding value in the Colour column.

Finding the nth item

Let’s see how the formula above would be used for find the nth instance of an entry in column A then we will see how to return the corresponding entry in column B.

In the workbook, the entry you want to find is in cell D1 and the position is in cell D2. Copy the formula below then in cell D3 hit the F2 key to enter Edit mode then paste the formula:

=SMALL(IF(A1:A11=D1,ROW(A1:A11),””),D2)       then press CTRL+SHIFT+ENTER

Note

This formula is an array formula, you must press CTRL+SHIFT+ENTER. Pressing ENTER will display an error.

So, if you are looking for the 2nd instance of Eraser in column A this formula will return 10. The 2nd entry Eraser is in position 10 in the list (starting in A1). To display the corresponding value in B you will need to use the INDEX() function. We will discuss the INDEX() function further down.

How does this work?

Because it’s an array formula, the reference to the range A1:A11 returns all values in that range to the formula, not just a single value.

Let’s do an experiment to get familiar with array formulas. Make sure the workbook Find nth smallest is open. Click any cell and do the following:

• Type =A1:A11
• Hit CTRL+SHIFT+ENTER

The cell displays the entry ‘Items’ which is the first entry in that column. In the Formula Bar you can see the formula {=A1:A11}. The curly braces indicate that it is an array formula; it can handle a range of cells. Now highlight the whole formula in the Formula Bar (or hit F2) and hit the F9 key. The formula bar displays all the elements in the range A2:A11 as an array.

={“Items”;”Pens”;”Sharpener”;”Highlighter”;”Marker”;”Eraser”;”Sharpener”;”Marker”;”Binder”;
“Eraser”;”Sharpener”}

Each element is separated by a semi colon because the range is in a column. If the Items were entered in a row, the array would have use a comma instead. Once you’re done looking at the array hit ESC to cancel this operation. The Formula Bar displays {=A2:A11}.

Using the F9 key when selecting a portion of a formula evaluates that selection. It’s a fantastic way to understand / debug a long formula bit by bit. To cancel a usage of the F9 key, I prefer to use CTRL+Z (Undo). If I press F9 multiples times at various places in a long formula, using CTRL+Z will undo one usage of F9 at a time. Pressing ESC will immediately return to the original formula.
Of course, when evaluating a portion of a formula, carefully balance the brackets!

Scanning the column A using an IF()

Let’s look at the IF() inside the formula:

IF(A1:A11=D1,ROW(A1:A11),””)

Array formulas can do things that normal formula can’t do! Here, the IF() function scans every element in the array A1:A11 and against the value in cell D1. Here A1:A11=D1 is a logical statement and returns TRUE when an element matches the value in D1 and FALSE when it does not.

Highlight the part A1:A11=D1 in the IF() function in the Formula Bar then hit F9. The expression is temporarily replaced by an array of 10 elements TRUE and FALSE!

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

Whenever there is a FALSE, the entry in the corresponding cell did not match the value in D1. Conversely, when it is TRUE, the entry in the corresponding cell did match the value in D1. Can you see where we’re heading with this? Hit ESC to return to the original formula.

Converting all TRUE values to the row number in the list.

The ROW() function returns the row number where is it entered. It’s one of those that, for the untrained eye, may appear to be odd. In our project, it will become essential.

In any cell type the following formula: =ROW(). Click inside the brackets then select all the data in the Items column (including the column heading) then hit CTRL+SHIFT+ENTER. The formula bar shows {ROW(A1:A11)} and the cell displays 1 because the first entry is in row 1. Now select the formula in the Formula Bar and hit F9. The result is the row numbers for every item in column A.

={1;2;3;4;5;6;7;8;9;10;11}    11 numbers, one for each element in the array (including the heading)

Wait, there’s more!

This material comes from the first five sections of the document Find the nth entry in a list available for download at the top of this post. The full tutorial has just over 3 more pages explaining how to complete the formula with full explanations

Hopefully you have learned something useful in this post.

Daniel from ComboProjects