Large Function with Ranking

[rex759]

Hello,
There are times where I have to add 2-4 more orders from the Master column to the Individual worksheet.
Example, B12 might have prices that auto populate from another program.

I would need to add the 4 highest prices from the Master columns with order numbers below it without duplicating any of them.

If B12 & B13 had prices, I would need to add 3 more orders. I need to keep the prices that populate in column B and add to it.
I thought I could use the Large function which works perfectly if the prices that auto populate are the highest prices in the list.

For example, if I change the price of B12 to 35.02, the next entry becomes 3487.76. It should be 5894.21.

Is there a way to show the next highest prices without duplicating what is already listed?
Any help is appreciated

Book1
	A	B	C	D	E	F	G
1	Individual				Master
2	Order	Price	Code		Order	Price	Code
3	w1111	752	BK		1026w8	14.12	Crt
4	w1112	367.94	BK		1026w6	20.32	Crt
5	w1113	358.56	BK		1026w28	47.47	Crt
6	w1234	97.89	BK		1026w26	41.66	Crt
7	w8769	1112.89	BK		1026w24	35.02	Crt
8	w5421	3574.76	BK		1026w22	29.28	Crt
9	1202	5981.21	BK		1026w20	23.08	Crt
10	1203	107.32	BK		1026w18	16.88	Crt
11	1204	101.12	BK		1026w16	10.68	Crt
12	1025w6	5894.21	Crt		1026w14	9.58	Crt
13	9999	3487.76			1026w12	1.72	Crt
14	9988	1025.89			1026w10	7.92	Crt
15	9984	665			1025w6	5894.21	Crt
16	9985	280.94			9999	3487.76	Crt
17					9988	1025.89	Crt
18					9987	10.89	Crt
19					9986	271.56	Crt
20					9985	280.94	Crt
21					9984	665	Crt
Sheet1

Cell Formulas
Range	Formula
A12	A12	=INDEX($E$3:$E$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$3)+1)/($F$3:$F$21=$B12),COUNTIF($B12:B$12,$B12)))
A13:A16	A13	=INDEX($E$3:$E$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$3)+1)/($F$3:$F$21=$B13),COUNTIF($B$12:B13,$B13)))
B13:B16	B13	=LARGE($F$3:$F$21,ROWS(B$12:B13))

 

[jasonb75]

Try this in B13

=AGGREGATE(14,6,$F$3:$F$21/ISNA(MATCH($E$3:$E$21,$A$3:$A12,0)),ROWS(B$13:B13))

 

[rex759]

Thank you for responding. The Aggregate function has so many uses! I think its closer to what I need but... if I change the price in B12 to 14.12, the next entries should be 5894.21, 3487.76, 1025.89 & 280.94.

Book1
	A	B	C	D	E	F	G
1	Individual				Master
2	Order	Price	Code		Order	Price	Code
3	w1111	752	BK		1026w8	14.12	Crt
4	w1112	367.94	BK		1026w6	20.32	Crt
5	w1113	358.56	BK		1026w28	47.47	Crt
6	w1234	97.89	BK		1026w26	41.66	Crt
7	w8769	1112.89	BK		1026w24	35.02	Crt
8	w5421	3574.76	BK		1026w22	29.28	Crt
9	1202	5981.21	BK		1026w20	23.08	Crt
10	1203	107.32	BK		1026w18	16.88	Crt
11	1204	101.12	BK		1026w16	10.68	Crt
12	1026w8	14.12	Crt		1026w14	9.58	Crt
13	1025w6	5894.21			1026w12	1.72	Crt
14	9988	1025.89			1026w10	7.92	Crt
15	9985	280.94			1025w6	5894.21	Crt
16	1026w28	47.47			9999	3487.76	Crt
17					9988	1025.89	Crt
18					9987	10.89	Crt
19					9986	271.56	Crt
20					9985	280.94	Crt
21					9984	665	Crt
Sheet1

Cell Formulas
Range	Formula
A12	A12	=INDEX($E$3:$E$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$3)+1)/($F$3:$F$21=$B12),COUNTIF($B12:B$12,$B12)))
A13:A16	A13	=INDEX($E$3:$E$21,AGGREGATE(15,6,(ROW($F$3:$F$21)-ROW($F$3)+1)/($F$3:$F$21=$B13),COUNTIF($B$12:B13,$B13)))
B13:B16	B13	=AGGREGATE(14,6,$F$3:$F$21/ISNA(MATCH($E$3:$E$21,$A$3:$A12,0)),ROWS(B$13:B13))

 

[jasonb75]

Sorry, that was my daily brainfart

Try this one instead.

=AGGREGATE(14,6,$F$3:$F$21/ISNA(MATCH($E$3:$E$21,$A$3:$A12,0)),1)

 

[rex759]

Works perfectly! Wondering why you choose to use the Aggregate instead of the Large?

 

[jasonb75]

Aggregate doesn't need array confirmation and it handles errors better, but mostly personal preference.

Guess you had similar reasons for using Aggregate instead of Small with your formula in A12?