Position of k-th value in SMALL(array, k)

Thanks:  0
Likes:  0

Thread: Position of k-th value in SMALL(array, k)

1. Position of k-th value in SMALL(array, k)

Hi All,
I cannot find the simple way of how to get the position (row number) of the k-th value for the functions like
SMALL(array, k).
please find an example:

 value k k-th smallest 1 1 1 2 2 1 1 3 2 3 4 3 4 5 4

Formula in C2 is the following: =SMALL(\$A\$2:\$A\$6,B2)
Formula in C3 is logically as: =SMALL(\$A\$2:\$A\$6,B3)

So Excel understands that even though values are repetitive ("1" in this example), they can take different places in grading (1st and 2nd in this example)

lets say that result in C2 is the "1" in the 1st matching row of the range, and result in C3 is the "1" in the 2nd matching row of the range (if excel gives "1" twice).

So my question: is there a way to reveal the number of the row, where the 2nd "1" is, based on the criteria "k" entered in formulas above?

something like:
if k=1, show me row number of the 1st smallest value
if k=2, show me row number of the 2nd smallest value (even though values repetitive)

Solution might be simple if values (in column A) are different- we could use MATCH, VLOOKUP or other formulas, but if there are the repetitive values, MATCH and VLOOKUP give the result of the 1st match only, so if we used those, they would reveal the ROW number 2 for both cases, meaning that both 1st and 2nd smallest value is in the row 2, what is not really logical.

I tried to use OFFSET, COUNTA and other functions, but the formulas become a Kilometre of length and they do not cover cases when value repeats 3,4 or n times.
Helper Columns might be helpful but maybe there's another way how to get the result?

Hope my problem is clear enough, can't wait for the solution

Thanks in advance

2. Re: Position of k-th value in SMALL(array, k)

Hi
Welcome to the board

This is one solution. please try:

ABCD
1valuerowrow k-th smallest
2111
3223
4139
5342
6456
7264
8475
9587
10198
11
[Book1]Sheet1

 Addr Formula [Book1]Sheet1 C2 =SMALL(IF(\$A\$2:\$A\$10=SMALL(\$A\$2:\$A\$10,ROWS(\$C\$2:C2)),\$B\$2:\$B\$10),ROWS(\$C\$2:C2)-COUNTIF(\$A\$2:\$A\$10,"<"&SMALL(\$A\$2:\$A\$10,ROWS(\$C\$2:C2)))) This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER. Copy down

3. Re: Position of k-th value in SMALL(array, k)

... or, if you calculate the k-th smallest in another column, the formula is simpler:

ABCDE
1valuerowk-th smallestrow k-th smallest
21111
32213
41319
53422
64526
72634
84745
95847
101958
11
[Book1]Sheet1

 Addr Formula [Book1]Sheet1 C2 =SMALL(\$A\$2:\$A\$10,ROWS(\$C\$2:C2)) Copy down D2 =SMALL(IF(\$A\$2:\$A\$10=C2,\$B\$2:\$B\$10),ROWS(\$D\$2:D2)-COUNTIF(\$A\$2:\$A\$10,"<"&C2)) This in an array formula and so MUST be confirmed with CTRL+SHIFT+ENTER and not just ENTER. Copy down

4. Re: Position of k-th value in SMALL(array, k)

... if you don't want to use an array formula, use in my last example in D2:

=AGGREGATE(15,6,\$B\$2:\$B\$10/(\$A\$2:\$A\$10=C2),ROWS(\$D\$2:D2)-COUNTIF(\$A\$2:\$A\$10,"<"&C2))

Copy down

5. Re: Position of k-th value in SMALL(array, k)

Thank you so much, you did a great job!

1st option works perfectly, however the goal of my calculations is slightly different, and i still cannot find an answer to it.

I will show you the exact example that i currently have:

I have a range of data:
 price quantity 78 3 60 1 80 3 206 4 87 3 60 5 114 2 92 4

and afterwards i have another table, with 5 lowest prices from the above and the respective quantity

 60 1 60 5 78 3 87 3 92 4

So my question is: how to get the "5" in the 2nd table? and what the formula should look like, if price repeated n times in the 2nd table?

Thank you in advance and Karma points granted from my part

6. Re: Position of k-th value in SMALL(array, k)

I think I found it, just had to clarify the logics of the formula.
Thank you, pgc01!

7. Re: Position of k-th value in SMALL(array, k)

I'm glad it helped.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•