Link to home
Start Free TrialLog in
Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How does this work? SMALL((A6#,C10:C18),SEQUENCE(ROWS(A6#)+ROWS(C10:C18)))


Open in new window

=SMALL(array, k)
I mean: the help syntax bears barely any relation with the solution above.

SHORT VERSION: Why does that work where UNIQUE() does not?

School's in.

So far I understand that small() picks only one example of each item in the list.

I can see that (A6#,C10:C18) concatenates two arrays, not just one, but it creates one out of two. I can also see that when I get my Value! error it is because I can reproduce the error thus:
Where B6:B7 are in Sheet1!
=B6:B7:Sheet2!B9:B10.        ==Value!
and also
=B6:B7:Sheet1!J6:J7            == Value! So even though in the same sheet, the Sheet1! reference generates the error.
=B6:B7:J6:J7                        ==0 No error.
So we just have to know this.

k is represented by: 

Open in new window

In this case only the rows argument is used.
This generates a series of numbers starting from 1 and running to the number of rows (and thus items) in the concatenated range A6#and C10:C18 (both same/current sheet) (51 in thee example)

So k is not one digit.. but it is; it is 51.

And the penny drops!! kinda.

Where we have k being the same as the number of items in range, SMALL() is looking for .. what?
Help seems confusing because it says k returns the k-th smallest value. That is ONE VALUE only.
Then it says "...return values...." which is what is being done in the above. So SMALL is returning not just one value, but every value smaller or equal to  the largest value in the range, and it returns only one example of each, no point in returning duplicates as that is not answering the question.

How is that different from UNIQUE() ?


This is like being on one of those never ending treadmills.. exercise for my TV watching brain.


Notes for self:


Returns the k-th smallest value in a data set.
Use this function to return values with a particular relative standing in a data set.


SMALL(array, k)
The SMALL function syntax has the following arguments:
  • Array    Required. An array or range of numerical data for which you want to determine the k-th smallest value.
  • K    Required. The position (from the smallest) in the array or range of data to return.

Avatar of byundt
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Anthony Mellor


Hi Brad,
I am disappointed because Excel treats each of the comma separated ranges as a separate parameter.

that sounds like it explains why I get differing results when trying the union with different functions, which seems to have neither rhyme nor reason.


Hoist a mug and say "Cheers!" whenever the Union trick works.

Obviously, a better solution is needed when two arrays need to be joined...

Shame SQL isn't built in to the functions list; so close in MS Query.