Lookup with duplicates & conditions

[Graemea]

Hi,


I have sample data in columns A and B, with product names in column A and sales per product in column B.


The last row in the range has the world "Total" in column A and the total of the sales values above in column B.


In E2:F4 I would like to create formulae to extract from A:B the Sales and associated Product names for the three largest sales values.


Two complications are that there may be more than one product name associated with a sales value (e.g. product AAA and product CCC with 50) and I don't want the value associated with "Total" to be considered among the largest values.


I would rather use formulae than any sort of filtering.


Can someone please suggest the formulae to use?


Thanks!

 

[cunningAce]

Hi,

Is that what your trying to achieve?
You just need the formulas in E2 and F2 then drag down.

	A	B	C	D	E	F
1	Product	Sales			Product	Sales
2	AAA	50			AAA	50
3	BBB	10			CCC	50
4	CCC	50			DDD	30
5	DDD	30			GGG	30
6	EEE	10			FFF	20
7	FFF	20			KKK	20
8	GGG	30			HHH	15
9	HHH	15			BBB	10
10	III	5			EEE	10
11	JJJ	0			III	5
12	KKK	20			JJJ	0
13	Total	240

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =IF($F2="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E1,$A$2:$A$12)+($F2>$B$2:$B$12),),0))) F2 =LARGE($B$2:$B$12,ROWS(E$2:E2)) E3 =IF($F3="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E2,$A$2:$A$12)+($F3>$B$2:$B$12),),0))) F3 =LARGE($B$2:$B$12,ROWS(E$2:E3)) E4 =IF($F4="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E3,$A$2:$A$12)+($F4>$B$2:$B$12),),0))) F4 =LARGE($B$2:$B$12,ROWS(E$2:E4)) E5 =IF($F5="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E4,$A$2:$A$12)+($F5>$B$2:$B$12),),0))) F5 =LARGE($B$2:$B$12,ROWS(E$2:E5)) E6 =IF($F6="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E5,$A$2:$A$12)+($F6>$B$2:$B$12),),0))) F6 =LARGE($B$2:$B$12,ROWS(E$2:E6)) E7 =IF($F7="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E6,$A$2:$A$12)+($F7>$B$2:$B$12),),0))) F7 =LARGE($B$2:$B$12,ROWS(E$2:E7)) E8 =IF($F8="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E7,$A$2:$A$12)+($F8>$B$2:$B$12),),0))) F8 =LARGE($B$2:$B$12,ROWS(E$2:E8)) E9 =IF($F9="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E8,$A$2:$A$12)+($F9>$B$2:$B$12),),0))) F9 =LARGE($B$2:$B$12,ROWS(E$2:E9)) E10 =IF($F10="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E9,$A$2:$A$12)+($F10>$B$2:$B$12),),0))) F10 =LARGE($B$2:$B$12,ROWS(E$2:E10)) E11 =IF($F11="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E10,$A$2:$A$12)+($F11>$B$2:$B$12),),0))) F11 =LARGE($B$2:$B$12,ROWS(E$2:E11)) E12 =IF($F12="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E11,$A$2:$A$12)+($F12>$B$2:$B$12),),0))) F12 =LARGE($B$2:$B$12,ROWS(E$2:E12)) B13 =SUM(B2:B12) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>