Explanation of an array formula with SMALL()

[wayusei]

I saw this post, which was solved using this formula
=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND("REQUEST ID",Sheet2!$A$1:$A$50000)))),"",INDEX(Sheet2!$A$1:$A$50000,SMALL(IF(ISNUMBER(FIND("REQUEST ID",Sheet2!$A$1:$A$50000)),ROW(Sheet2!$A$1:$A$50000)),ROW(A1))))

I'm trying to understand how does it work, or what does each part is doing but am currently stuck. Could anyone explain what is going on, particularly with the SMALL ()?

Thanks!

[Andrew-R]

Thanks for the new thread.

OK, I'll work with a simpler example. We have a sheet with gender (M or F) in cells A1:A10 and forename in B1:B10 and we want a formula to return a list of all of the boys names.

If we start with the formula:

=IF($A$1:$A$10="M",ROW($A$1:$A$10))

Then it will return an array containing row numbers (where the gender for the row is M) or False (where the Gender is F). So if it happens that the males are on the odd number rows our array will look like this:

{1,False,3,False,5,False,7,False,9,False}

So we are interested in the values in column B for the rows numbered in that array, so we can nest the IF inside a SMALL:

=SMALL(IF($A$1:$A$10="M",ROW($A$1:$A$10)),ROW(A1))

SMALL returns the nth smallest number in a range or array, and will ignore False values, as they are not numeric. So:

=SMALL({1,False,3,False,5,False,7,False,9,False},1)

Will return 1, as 1 is the smallest value in that array

=SMALL({1,False,3,False,5,False,7,False,9,False},3)

Will return 5, as 5 is the 3rd smallest number.

So we're using ROW(A1) as the parameter indicating which value we want returning, because on the first row we place our formula this will evaluate to 1, and when we copy our formula down a row it will update to ROW(A2) and return a 2, and so on.

So this returns the row numbers that we want, but now we want to get the values, so we wrap these returned row numbers inside and INDEX statement:

=INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10="M",ROW($A$1:$A$10)),ROW(A1)))

So index returns the value from the nth row of a specified range, in this case B1:B10

So as we drag this formula down rows it will return the values in B1, B3, B5, B7 and B9 successively.

All we need to do now is stop it trying to return a value if all of the Male names have been returned.

We can count the number of male names with:

=SUMPRODUCT(--($A$1:$A$10="M"))

This will return a True for every cell in A1:A10 which equals "M" and False for every cell in that range which is <> "M".

The -- at the start of the brackets will force these True/False values into their numerical equivalents, 1 and 0, respectively, leaving us with an array that looks like this:

{1,0,1,0,1,0,1,0,1,0}

These will then be added up, to give us a total of 5 rows which have a value of M in column A.

[Aside - for this example there are easier ways to get this number, COUNTIF, for example, but we needed SUMPRODUCT for the example you're citing)

So, now we have a formula which counts how many values will be returned, and a formula to join them together, so we just need to add them together:

=IF(ROW(A1)>SUMPRODUCT(--($A$1:$A$10="M")),"",INDEX($B$1:$B$10,SMALL(IF($A$1:$A$10="M",ROW($A$1:$A$10)),ROW(A1))))

So if the ROW, which we're using as a counter, is greater than the number of values in the range the formula will return blank, otherwise it will return the value from column B equating to the ROWth male name from the source range.

Does that make sense/help you out?

[wayusei]

WOW, explained this way it makes a lot of sense now.

My main problem was that I didn't understand that SMALL(array,n) returned the n-th smallest term we asked. But This post clarifies a lot of other stuff I was struggling with.

Thanks a lot for this!

[bebo021999]

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(FIND("REQUEST ID",Sheet2!$A$1:$A$50000)))),"",INDEX(Sheet2!$A$1:$A$50000,SMALL(IF(ISNUMBER(FIND("REQUEST ID",Sheet2!$A$1:$A$50000)),ROW(Sheet2!$A$1:$A$50000)),ROW(A1))))
The code in red is the main part, the other is the error trap. So we just forcus on the red.
Knowing that INDEX(A1:A5000,n) returns the value in line n of A1:A5000.
Let says A1:A5000, in which, line 2nd,4th,9th (A2,A4,A9) contains text "REQUEST ID". In result table, in Z1,Z2,Z3, we wish to draw the contain of A2,A4,A9
FIND("REQUEST ID",Sheet2!$A$1:$A$50000)establish array with number>0 if found: NA1,number,NA2,number,...,NA5000 (NA:#N/A!)
ISNUMBER(FIND("REQUEST ID",Sheet2!$A$1:$A$50000))establish array with 1 if found, 0 if not: 0,1,0,1,...,0
IF(ISNUMBER(...)) erase 0 in the array to use SMALL
Now translate 1 to position of 1 in the array:
IF(ISNUMBER(...),{1,2,3,...5000}) => IF(ISNUMBER(...),ROW(A1:A5000))
Now the array is: {false,2,false,,4,...,false,9,...,false}
In Z1, use: SMALL(array,1) = SMALL(array,row(a1))= 2; INDEX(A1:A5000,SMALL())=A2
Z2=A4
Z3=A9
...
In general, the tip here is translating the rows where met condition to order numbers, then use INDEX, SMALL to get the content.
Array formula, should be confirmed with holding ctrl-shift then press enter.
Hope it is clear enough for you.
Nice to get edition from all of you for promotion.