Find the nth largest value and return the value in another column

[gberg]

I have a table and want to find the nth largest value but then return the result from a different column in the same row the nth value is located in. I have a large table so this is a small example

Object	#		n	Result
Orange	54​		1	Pineapple
Apple	35​		2	Pear
Pear	68​		3	Orange
Grape	1​
Pineapple	548​

The table would be "Object" and "#" (Table A) and the results would be the N and Results


I would want to look for the largest value in the "#" column and return the result from the "Object" column. In this case it would be Pineapple with 548. It would go down the list and look for the second largest value (68 / Pear) and so on.

Thanks for any help you can give me.

Greg

 

[dreid1011]

Perhaps this:

Book1
	A	B	C	D	E
1	Object	#		n	Result
2	Orange	54		1	Pineapple
3	Apple	35		2	Pear
4	Pear	68		3	Orange
5	Grape	1
6	Pineapple	548
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=INDEX(SORTBY($A$2:$A$6,$B$2:$B$6,-1),D2)

 

[gberg]

dreid1011,
That worked, but I'm an idiot and forgot that there are other conditions that need to be met. The formula would need to look at multiple columns for other conditions.

Object	In Stock	Expired	#		n	Result
Orange	Yes	No	54​		1	Orange
Apple	Yes	No	35​		2	Apple
Pear	Yes	Yes	68​		3	Grape
Grape	Yes	Almost	1​
Pineapple	No	No	548​

The "In Stock" column would need to be "Yes" and the "Expired" column could not equal "Yes". I have updated the results based on this additional criteria. Sorry about leaving this part out.

Thanks,

Greg

 

[dreid1011]

Alright, try this, I also changed the #s to test because your sample just so conveniently had them in the desired order in the original list:

Book1
	A	B	C	D	E	F	G	H
1	Object	In Stock	Expired	#		n	Result	Result 2
2	Orange	Yes	No	54		1	Pineapple	Grape
3	Apple	Yes	No	77		2	Grape	Apple
4	Pear	Yes	Yes	68		3	Apple	Orange
5	Grape	Yes	Almost	99
6	Pineapple	No	No	548
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=INDEX(SORTBY($A$2:$A$6,$D$2:$D$6,-1),F2)
H2:H4	H2	=LET(n,($B$2:$B$6="Yes")*($C$2:$C$6<>"Yes"),INDEX(SORTBY(FILTER($A$2:$A$6,n),FILTER($D$2:$D$6,n),-1),F2))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G
1	Object	In Stock	Expired	#		3	Result
2	Orange	Yes	No	54		1	Orange
3	Apple	Yes	No	35		2	Apple
4	Pear	Yes	Yes	68		3	Grape
5	Grape	Yes	Almost	1
6	Pineapple	No	No	548
7
Data

Cell Formulas
Range	Formula
F2:F4	F2	=SEQUENCE(F1)
G2:G4	G2	=TAKE(SORT(FILTER(A2:D100,(B2:B100="yes")*(C2:C100<>"yes")),4,-1),F1,1)
Dynamic array formulas.

 

[gberg]

Thank you both! Each solution works great but now I don't know which to use? Is there a benefit to using the Dynamic array over the LET formula or vise versa? You both make this look so easy!

Thanks,

Greg

 

[Fluff]

Unless you have massive amounts of data, I doubt there's much difference in speed.
So use the one you prefer.

 

[gberg]

I actually do have a massive amount of data. 36,000 plus rows x 24 columns. Probably better suited for a database, but I don't know Access so I stick with Excel

 

[Fluff]

In that case try both solutions on the real data & see if there is any difference in speed.

 

[gberg]

Will do and thanks again for the help!