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.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.