Large function not returning the expected value

[vladi305]

On this worksheet we're looking for the 5 highest values on column D
The formula here is LARGE(B$3:B$38,1) and so on until LARGE(B$3:B$38,5)
Now on column E we're getting the corresponding value from Column A
We're using the match function with the large function
=MATCH(LARGE(B$3:B$38,1),B3:B38,0) and it works fine until it finds multiple values
On the attached picture the first two return the correct corresponding value which is the number 27 being the highest value and 8 being the 2nd highest value but the third then it start returning number 1 which is the 3rd highest value it finds because then it has 19, 24 and 26

Any idea how to work around this to get all these values

 

[Fluff]

How about

=TAKE(SORT(CHOOSECOLS(A3:B38,2,1),1,-1),5)

 

[jdellasala]

Boy, a lot of work when it isn't posted using XL2BB!

Book1
	A	B	C	D	E
1	1	48.38%		87.94%	27
2	2	24.19%		85.40%	8
3	3	62.44%		84.65%	12
4	4	14.01%
5	5	16.10%
6	6	55.44%
7	7	81.22%
8	8	85.40%
9	9	82.06%
10	10	67.04%
11	11	32.28%
12	12	84.65%
13	13	83.35%
14	14	29.81%
15	15	20.40%
16	16	23.35%
17	17	70.62%
18	18	76.14%
19	19	37.61%
20	20	18.90%
21	21	12.38%
22	22	75.76%
23	23	43.31%
24	24	27.46%
25	25	20.46%
26	26	35.17%
27	27	87.94%
28	28	37.69%
Sheet1

Cell Formulas
Range	Formula
A1:A28	A1	=SEQUENCE(28)
B1:B28	B1	=ROUND(RANDARRAY(28,,0.1,0.9,0),4)
D1:D3	D1	=LARGE(B1#,SEQUENCE(3))
E1:E3	E1	=XLOOKUP(D1#,B1#,A1#)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B28	Cell Value	top 3 values	text	NO

 

[vladi305]

How about

=TAKE(SORT(CHOOSECOLS(A3:B38,2,1),1,-1),5)

This method does the job and it's simple. Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[vladi305]

How about

=TAKE(SORT(CHOOSECOLS(A3:B38,2,1),1,-1),5)

Glad we could help & thanks for the feedback.

Can you please explain how this formula works? Because now I want to take the lowest values. Is the Sort function finding the highest value?

 

[Fluff]

Is the Sort function finding the highest value?

That's right, just change the -1 to 1 & you will get the 5 lowest values.

 

[vladi305]

That's right, just change the -1 to 1 & you will get the 5 lowest values.

Awesome!

 

[Fluff]

You're welcome.

 

[vladi305]

You're welcome.

Here's the formula at work