Index match large problem

[ebea]

Hi! I run into problems, to get get the highest values in a range, listed correctly. The code I use, are originally grabbed from: Index Match Duplicate Values With No Helper Cells [SOLVED]

I have changed the code, delivered from T. Valko (in the Link) a bit, to fit into my use. But when I try to list the highest values, I do only get the value listed as they comes from the match, and not the highest values, as they should be.
I put in here, the example, below!
I only need the 6 highest values.

Do anyone have an idea, how to correct this?
Thank you!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	200	200	198	200	200	200	200	200	199	199	198	200	200	200	199	199
2	15	11	15	12	12	15	10	16	12	11	9	11	10	18	17	14
3
4	Large 1	Large 2	Large 3	Large 4	Large 5	large 6
5	15	11	12	12	15	10	<---------	Formula result
6
7	18	16	15	15	12	12	<---------	Should be
Sheet1

Cell Formulas
Range	Formula
A5:F5	A5	{=INDEX($A2:$P2,MATCH(LARGE($A1:$P1-COLUMN($A1:$P1)/10^10,COLUMNS($A5:A5)),$A1:$P1-COLUMN($A1:$P1)/10^10,0))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fluff]

Maybe

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	200	200	198	200	200	200	200	200	199	199	198	200	200	200	199	199
2	15	11	15	12	12	15	10	16	12	11	9	11	10	18	17	14
3
4	Large 1	Large 2	Large 3	Large 4	Large 5	large 6
5	18	16	15	15	12	12
Database

Cell Formulas
Range	Formula
A5:F5	A5	=AGGREGATE(14,6,$A$2:$P$2/($A$1:$P$1=200),COLUMNS($A5:A5))

 

[ebea]

Hi Fluff! In my exact (short) example, your solution works perfectly. But then when I copied down of the list, which are on the sheet, it produced errors, which I cannot see how they occur!
I put in an example with some more lines (and also empty columns in the list).

So far, thank you!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	200	200	198	200	200	200	200	200	199	199	198	200	200	200	199	199
2	14	16	15	15	13	15	14	18	15	16	13	15	16	16	14	15		18	16	16	16	15	15
3	196	196	0	0	0	0	0	0	0	0	0	0	198	199	0	0
4	8	11											12	10				#NUM!	#NUM!	#NUM!	#NUM!	#NUM!	#NUM!
5	197	199	195	200	196	199	199	199	194	194	199	199	198	199	198	200
6	13	9	8	17	10	8	14	15	5	6	13	15	7	9	11	11		17	11	#NUM!	#NUM!	#NUM!	#NUM!
Sheet1

Cell Formulas
Range	Formula
R2:W2, R6:W6, R4:W4	R2	{=AGGREGATE(14,6,$A2:$P2/($A1:$P1=200),COLUMNS($R2:R2))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fluff]

Row 4 is all errors because you don't have 200 anywhere in row 3 & row 5 only has two 200 so you only get 2 answers.
To get rid of the errors you can use
=IFERROR(AGGREGATE(14,6,$A4:$P4/($A3:$P3=200),COLUMNS($R4:R4)),"")

 

[ebea]

Ah, Fluff! Why did I not catch this I have to put in a < So the formula became: {=AGGREGATE(14,6,$A2:$P2/($A1:$P1=<200),COLUMNS($R2:R2))}

Thank you Fluff. Problem solved!

 

[Fluff]

Glad you sorted it & thanks for the feedback

 

[ebea]

..Best as I thought all was okay, I can see, that the solution do not work correctly. As soon, I point only on max value 200, it works when all values are 200. And when I put in to have values below 200, the problem comes, as the list becomes wrong. Try to look at the example below. The index has to return the numbers, related to the highest numbers in the match!

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	197	199	195	200	196	199	199	199	194	194	199	199	198	199	198	200		200	200	199	199	199	199
2	13	9	8	17	10	8	14	15	5	6	13	15	7	9	11	11		17	15	15	14	13	13	<------ result
3
4																		17	11	15	15	14	13	<----- should be
Sheet1

Cell Formulas
Range	Formula
R2:W2	R2	{=AGGREGATE(14,6,$A2:$P2/($A1:$P1<=200),COLUMNS($R2:R2))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Fluff]

Can you please explain, in words, what you are trying to do?

 

[ebea]

I will try! I want to take max 6 numbers, out of the list which you see in A:P. These 6 numbers, are related to the highest numbers in that list. So if there are 3 with 200, and 3 with 199, it will be 3 of each of these (index) numbers. And so on, downwards in number size.
If you see in the actual example, there are 2 on 200, but they have not the highest (index) numbers. There are numbers 199 which have higher (index) number, than the 200, but they have to become lower in the generated list. I hope it gives more meaning. Else, I try again
Thank you!

 

[ebea]

My original formula, actually list the index number correctly, but the problem was, that if there was duplicates in the index numbers, it only list the first of each these duplicate numbers!