Match Exact Row Number of Nth Largest Value In Data With Duplicates

Question

One can achieve the Nth largest value in a column of data like so:

=LARGE(A1:A5, N)

But in data with multiple repeat values, one cannot MATCH the result to it's accurate position in the column.

Take the following simple example: Column A=c(3,3,3,1,2,2,1,4,3) and Column B=c(1,2,4,8,16,32,64,11,12). I want to get the value in column B matching up with say the Nth largest value in Column A. But =INDEX(A1:B5, MATCH(LARGE(A1:A5,N), A1:A5, 0), 2) with N<4 will always return 1 instead of a mix of 1, 2, and 4 (order is not important to me so long as all appear).

Specifically looking for a way to do this without array formulas or VBA. Also want this to work in the general case, and to be able to further manipulate the results. See in the example that Column V gives the desired output, but one cannot edit further (Column W).

Example Output:

Please link such examples and then flag this post as a duplicate — EDS, May 27 '21 at 04:56
@Euler'sDisgracedStepchild What is your excel version. I think this can be achieved by `Excel365` without array formula. — Harun24hr, May 27 '21 at 04:58
@Euler'sDisgracedStepchild Can you show output from above sample data? — Harun24hr, May 27 '21 at 05:02
Suppose we are taking `n as 2` so `LARGE(A1:A5,2)` means 3 which is not exist in column B and formula will return error. — Harun24hr, May 27 '21 at 05:05
I gave you a heads up that solutions exist on here, but I don’t have time this morning to do your resseach for you. — Solar Mike, May 27 '21 at 05:37
@Euler'sDisgracedStepchild , If I'm not wrong then you have misinterpreted the LARGE function,, considering the given sample data if formula is LARGE(A2:A5,1) or LARGE(A2:A5,3) will return 1st & 3rd Largest value are 3. Wrapped with MATCH return their respective ROW numbers, but one at a time. And in Column A the Largest is 3 but adjacent column doesn't have 3 so the formula returns #NA!! — Rajesh Sinha, May 27 '21 at 05:48
**Cont,,,** And considering the used formula with screen shot the formula will return 1,1,1,16,8 ,,, Largest 1st ,2nd ,3rd, 4th & 5th Largest from Col T and are correct sequence. Now consider expected out put 1, 2 & 4 from col T regardless of their ORDER as you have mentioned above,,, actually U want to get 3 Smallest numbers are 1, 2 & 4 ,, use this one `=IF(ROW(A1)<=3,SMALL(T$2:T$6,COUNT($T$2:T2)),"")`, formula with screen shot will return 8, 16 & 1. Plz confirm through comments whether my reasoning is correct or not !! — Rajesh Sinha, May 27 '21 at 05:50
@Solar Mike Notice my question specifically says no array and no VBA. Your link and the link in that link both give solutions for arrays. — EDS, May 27 '21 at 13:12
@Rajesh S I think you are mistaken to your point about the NA results-- the MATCH returns the row of the index; it doesn't matter whether the value we already MATCHed is in a different column. Your solution in 2nd comment may work, but I'm looking for a general solution (where I don't know which values are repeated, or where they are located) — EDS, May 27 '21 at 13:25
@Euler'sDisgracedStepchild ,, check my post I've suggested tow possible formula & both are working getting results in desire order,, I do believe this help U ☺ — Rajesh Sinha, May 28 '21 at 06:43

score 3 · Answer 1 · answered May 27 '21 at 05:55

3

From your sample data it seems just sorting will work. Use SORTBY() function.

=SORTBY(T3:T7,S3:S7,-1)

answered May 27 '21 at 05:55

Harun24hr

30,391
4
21
36

This indeed works in the specific case, but I am looking to further manipulate the results, so an overflow will probably not achieve what I am looking for. For example, I only want to return an nth value if it is above a certain point. I need something like =IF(T>9,"", SORTBY(T3:T7,S3:S7,-1)) but that does not get what I'm looking for. See my edits for clarification – EDS May 27 '21 at 17:23

bosco_yip · Accepted Answer · 2021-05-27T17:41:25.823

1

Edit : in follow the OP's comment, formula revised to :

In V2, formula copied down :

 =INDEX(S$2:T$7,AGGREGATE(15,6,ROW(A$1:A$6)/(S$2:S$7=LARGE(S$2:S$7,ROW(A1))),SUMPRODUCT(0+(LARGE(S$2:S$7,ROW(INDIRECT("1:"&ROW(A1))))=LARGE(S$2:S$7,ROW(A1))))),2)

edited May 27 '21 at 17:41

answered May 27 '21 at 05:55

bosco_yip

3,762
2
5
10

This is a really interesting answer, have never seen the AGGREGATE function before. However, don't think it works in the general case (add another 2 to column S and a 32 to column T, change the ranges in the formula, and the output is not correct). Maybe something to do with the array generated from "LARGE...=S2"? When Isolating that with the new example I gave, it is TTTFFF instead of TTTFTT as I believe would be required. Any ideas? – EDS May 27 '21 at 17:04
@Euler'sDisgracedStepchild, please see my new edit in revise the formula as per your comment. – bosco_yip May 27 '21 at 17:45
A really great answer and clever work around. I have explained what your formula is doing in an answer of my own; feel free to copy and paste over to yours if you so desire and I will delete mine. – EDS May 31 '21 at 03:34

EDS · Answer 3 · 2021-05-31T14:41:33.720

An effort to explain the solution graciously provided by @bosco_yip ...

The key to this question is to find the row number of each specific instance. Once we get the row number, we can use INDEX as we already have the range ($S$2:$T$10) and the column number (2) we want.

To get the correct row numbers, we use the AGGREGATE function (documentation here: https://exceljet.net/excel-functions/excel-aggregate-function), which takes four arguments:

the operation to be performed (the 15 in the formula, which represents the function SMALL)
how to treat certain values (the 6 in the formula, which represents ignoring error values)
the array being looked at
which nth value in the array to evaluate.

Since we have the first two arguments of the function, we turn our attention to generating the array, which we get with ROW($A$1:$A$9)/($S$2:$S$10=LARGE($S$2:$S$10,ROW(A1))). There are two clever tricks that make this solution work without strict array formulas -- changing the array that the function looks at for each row, and utilizing formulas that spill over data to create the array.

The first part of the formula is given by =($S$2:$S$10=LARGE($S$2:$S$10,ROW(A1))) For each row, this creates a True/False Array that looks like the below. Notice row i generates an array whose value is true wherever the ith largest value in the range is met.

We would like to take this a step further and instead have each array display the row number where the ith largest value in the range is. To do so, we simply divide another range of numbers 1:9 by the prior array since "TRUE" is crudely evaluated as 1 and "FALSE" as 0. This gives us the following array for each row (recall we exclude errors in the AGGREGATE function with a second of "6").

We now have the array for each row and turn our attention to the last argument of the AGGREGATE function. If the jth greatest number in our original data is repeated k times, then we need k separate arguments for each array. We get these separate arguments by using the below formula: SUMPRODUCT(0+LARGE($S$2:$S$10,ROW(INDIRECT("1:"&ROW(A1))))=LARGE($S$2:$S$10,ROW(A1))))

The first part of this formula, LARGE($S$2:$S$10,ROW(INDIRECT("1:"&ROW(A1)))) generates an array that simply lists the elements in our original data from greatest to least up to the first i items for row i. As an example, the array generated for row 6 would be c(4,3,3,3,3,2).

Each ith array is then tested against the ith largest value in the original data to generate a TRUE/FALSE array as shown below.

Finally, we use SUMPRODUCT on the array (SUMPRODUCT of a single array is the same as adding the array) to count the number of entries that are true (note that SUM or SUMIF or COUNTIF will not work). So the ending value of the AGGREGATE function is shown below (different colors represent different values in the original data) next to the results of the entire function

score 0 · Answer 4 · answered May 28 '21 at 06:41

0

I would like to suggest two array (CSE) formula which gets desire result.

How it works:

Without an array (CSE) formula it's not possible, since the formula needs to read each largest elements from column K while skip duplicates to pair the correct value from column L, eventually are same value.

An array (CSE) Formula in cell S2:

{=IF(ROW(A1)<=3,INDEX($K$2:$L$7,MATCH(LARGE($K$2:$K$7-ROW($K$2:$K$7)/10^3,ROWS($2:2)),$K$2:$K$7-ROW($K$2:$K$7)/10^3,0),2),"")}

Array (CSE) formula in cell T2:

{=IF(ROW(A1)<=3,INDEX($K$2:$L$7,MATCH(LARGE(INDEX($K$2:$K$7+(ROWS($K$2:$K$7)-ROW($K$2:$K$7))/10^3,0),ROWS($2:2)),INDEX($K$2:$K$7+(ROWS($K$2:$K$7)-ROW($K$2:$K$7))/10^3,0),0),2),"")}

Finish formula with Ctrl+Shift+Enter.
Adjust cell references in the formula as needed.

answered May 28 '21 at 06:41

Rajesh Sinha

197
3
8

thanks for the work, I appreciate it. But the accepted answer has a clever workaround that avoids the use of strict array formulas (meaning having to do Ctrl_Shift+Enter). – EDS May 31 '21 at 03:35
@Euler'sDisgracedStepchild ,, glad to help you,, as I've written why an array (CSE) formula is required,,, and the Formula used by #bosco_yip Array & Non Array both formula returns **32, 16, 8, 4, 2, 1**,,, I've tested it,,, **the reason is formula has both,, first is Aggregate 15, is for SMALL and ahead used is LARGE that's the reason it produces wrong ORDER,,, any way keep asking ☺** – Rajesh Sinha May 31 '21 at 06:25
Hi Rajesh, just for clarification SMALL is working on the inside of the array (meaning on repeat values, it lists from smallest to largest). It's strange you're seeing the reverse order though, did you copy the full formula? With the newest edited data I am getting c(11,1,2,4,12,16,32,8,64) and it has worked for any variation or changes I make – EDS May 31 '21 at 14:46

Match Exact Row Number of Nth Largest Value In Data With Duplicates

4 Answers4

Linked