The purpose of the formula is depicted in the following screenshot where its function is to create an ordered list (in column F) of the names in column B which have an adjacent letter in column C that matches a manually-entered letter in the box at cell F2. (By the way, column E contains a formula which displays numbering only if the adjacent column-F cell is not blank — see footnote *a).
(Note: all screenshots in this post are taken from the attached Excel file.)
The formula works great but I really want to understand each part of what it's doing because I have several other places where it would be useful. So working from the inside out, I looked at the cell-display results of various segments when they are entered 1) as standard formulas and 2) when they are entered as array formulas (Ctrl+Shift+Enter).
I also used a trick I learned a couple of years ago which enables you to 3) see the complete results of a formula by selecting the formula in the Formula Bar then pressing F9 (see footnote *b).
The following screenshot shows these three results for several segments of the formula:
• #1 & #2 are shown in column F and
• #3 (the F9 result), is pasted with a black font in column H:
By the way, cells with red italics use the Excel4 function =GET.CELL(6, [ref]) to display the formula residing two cells to the left (eg H40 shows the formula in F40) (see footnote *c).
The next thing I did was to transpose the F9 results into a table with heading numbers corresponding to the evaluated segments and wrote down definitions/explanations (blue font) of what I think is happening. These steps are shown in the next two screenshots:
I think I understand most of the process up through item #7 and I'm fairly familiar with the INDEX() & IFERROR() functions (in #11 & #12). But I have not previously used the ROWS() or SMALL() functions — although I've read about them and gone through some examples and they seem to be quite straightforward.
I suspect the biggest hurdle is understanding how to get from essentially evaluating each original-data row one-at-a-time to get an array of 10 results at each step (if that's the correct terminology) to having the three names (Abigail, Hannah, & Sophia) which are "still standing" after all the steps (ie do not get assigned the huge number in #7), compiled into the final list of three names.
Maybe if someone could respond with how you would edit/fill-in the blue-font descriptions for #8, #9, & #10, that would do the trick. Other similar examples are always helpful as well.