Find the Nth instance of a text string within a range?

Question

I have a list of names next to a list of shifts. I want to collate a list of people on each shift by day, which will automatically and dynamically update. I can't work out the best way to do it.

In the main roster, there will be a column of employee names, followed by 14 columns of shifts. I then want to create 14 lists of names based on who is on what shift in the first table.

Eg: Main range has:

        A      B   C   D   E
   ---------- --- --- --- ---
1  Joe Blogs   E   E   L   O
2  Jill Bleg   L   L   E   E
3  Geoff Ted   O   L   L   L

I would like to then lookup colum D, for example, and search for the first instance of L to list Joe Blogs, then search for the second instance of L to list Geoff Ted, and so on.

Is there a way I can search for the Nth instance of a string within a range of cells?

Answer 1

This is not as easy as it seems. First, to find the row numbers of all occurrences of "L" in column D, you'd use:

=IF(D1:D3="L"; ROW(D1:D3))

Save this as an array formula: after typing it, hit Ctrl+Shift+Return (or Command+Shift+Return on a Mac). It will then show in curly braces. And it will not have a single cell as its results, but as many cells as the range you're working on. In the example above, you'd get 3 cells, with values 1, blank and 3.

Next, use SMALL to find the Nth value:

SMALL(numberlist; n)

returns the n^th smallest number within the (unordered) range or array of numbers numberlist.

Applying SMALL to the above, you're back to having a single cell as the result:

=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1)
=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2)

Still, despite the result being a single cell, this needs to be saved using Ctrl+Shift+Return.

Now, knowing the row number, INDEX can find the name in the first column of A1:A3:

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1); 1)
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2); 1)

Again, all those need to be saved using Ctrl+Shift+Return.

However, such formula cannot be dragged to be extended into other cells, as the ranks "1" and "2" would then not automatically become "3", and so on. Instead, to calculate the required rank based on the row in which the formula is:

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); ROW()); 1)

After saving this as an array formula somewhere in row 1, one can drag it down to add it to row 2, 3, and so on.

Alternatively, instead of extending the result, you can copy/paste the array formula to have column and row references adjusted on the fly. In the screenshot below, I copied the following from B7, B12 and B17 into the other cells:

=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$6; ROW(B$1:B$3)); ROW()-ROW(B$6)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$11; ROW(B$1:B$3)); ROW()-ROW(B$11)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$16; ROW(B$1:B$3)); ROW()-ROW(B$16)); 1)

Beware that the keyboard shortcut for array formulas will only work after actually making changes to the formula; when you simply hit Return, then going into the formula again and then hitting Ctrl+Shift+Return will have no effect.

Also, once an array formula has been extended, you'll need to select all result cells to change that formula. Otherwise you'll get "You cannot only change part of an array".