Hello,

**Can someone provide an explanation for a couple of parts of the array-entered Excel formula described below?**

I've been tinkering with an array formula for several days, trying to understand how it does what it does as the solution to the thread located here:

**Extract certain items from one list to create another list in Excel**

A shortened version (10 rows instead of 20 rows) of the formula is:

*(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.

Thanks, Steve

__________________________________________________

**Footnotes:**

***a —** The formula in column E is:

**=IFERROR(IF(OFFSET(E4,0,1)**<>"",OFFSET(E4,-1,0)+1,""),"")

pasted down to row 13.

***b —** I got the **F9 tip from Rory Archibald** about two years ago and here is the **Microsoft doc** on the same topic.

Also, Ctrl+z undoes the function (if you remember to do it before leaving the Formula Bar).

***c —** Here is a link I found with some **information about Excel4 functions**. I'm interested in finding other/better references on this topic in case anyone knows of one.

By the way, the:

**=GET.CELL(6, [ref])**

function does not, for some reason, display curly brackets when an array formula is present, so I manipulated it a bit to make it work in those cases:

**="{"&GET.CELL(6,AutoFilter**!H16)&"}"

This is shown in the Name Manager where I named the standard function "Formula" and the manipulated form "ArrayFormula" as shown in the next two screenshots:

File: AutoFilter-array-formula-evaluation.xlsm

I've been tinkering with an array formula for several days, trying to understand how it does what it does as the solution to the thread located here:

A shortened version (10 rows instead of 20 rows) of the formula is:

```
{=IFERROR(INDEX(B$1:B$13,SMALL(IF((C$4:C$13=F$2)*ROW(C$4:C$13)=0,1E+38,(C$4:C$13=F$2)*ROW(C$4:C$13)),ROWS($4:4))),"")}
```

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).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.

Thanks, Steve

__________________________

pasted down to row 13.

Also, Ctrl+z undoes the function (if you remember to do it before leaving the Formula Bar).

By the way, the:

function does not, for some reason, display curly brackets when an array formula is present, so I manipulated it a bit to make it work in those cases:

This is shown in the Name Manager where I named the standard function "Formula" and the manipulated form "ArrayFormula" as shown in the next two screenshots:

File: AutoFilter-array-formula-evaluation.xlsm

As a workaround, I normally post these kind of formulas making reference only to the rows actually being used. To do this, I convert the row numbers from the array expression into index numbers (i.e. starting with 1 instead of 4). I make the index numbers by subtracting the row number of the first cell in the range, then adding 1 to that result using ROW(B$4:B$13)-ROW(B$4)+1. This subtraction may be performed either within the numerator of the array expression or from the result of the AGGREGATE. The latter is a little tidier.

The formula then becomes:

```
=IFERROR(INDEX(B$4:B$13,AGGREGATE(15,6,ROW(C$4:C$13)/(C$4:C$13=F$2),ROWS($4:4))-ROW(B$4)+1),"")
```