Lookup with duplicates & conditions

G

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
119
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Hi,

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

ABCDEF
ProductSalesProductSales
AAAAAA
BBBCCC
CCCDDD
DDDGGG
EEEFFF
FFFKKK
GGGHHH
HHHBBB
IIIEEE
JJJIII
KKKJJJ
Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]50[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]50[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]15[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]240[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF($F2="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E1,$A$2:$A$12)+($F2>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E2))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=IF($F3="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E2,$A$2:$A$12)+($F3>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E3))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=IF($F4="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E3,$A$2:$A$12)+($F4>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E4))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=IF($F5="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E4,$A$2:$A$12)+($F5>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E5))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=IF($F6="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E5,$A$2:$A$12)+($F6>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E6))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]=IF($F7="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E6,$A$2:$A$12)+($F7>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F7[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E7))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]=IF($F8="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E7,$A$2:$A$12)+($F8>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F8[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E8))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E9[/TH]
[TD="align: left"]=IF($F9="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E8,$A$2:$A$12)+($F9>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F9[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E9))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E10[/TH]
[TD="align: left"]=IF($F10="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E9,$A$2:$A$12)+($F10>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F10[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E10))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E11[/TH]
[TD="align: left"]=IF($F11="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E10,$A$2:$A$12)+($F11>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F11[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E11))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E12[/TH]
[TD="align: left"]=IF($F12="","",INDEX($A$2:$A$12,MATCH(0,INDEX(COUNTIF(E$1:E11,$A$2:$A$12)+($F12>$B$2:$B$12),),0)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F12[/TH]
[TD="align: left"]=LARGE($B$2:$B$12,ROWS(E$2:E12))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]=SUM(B2:B12)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Calculate top 70% based on total spend
Replies
2
Views
388
StephenCrump
S
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
220
Pbobas
P
K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
291
RobP
R
Z
  • Solved
Formula for Summing Total Sales Count by Submission Date and Agent Name
Replies
2
Views
223
Zee996
Z
S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
426
STARL0RD
S

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top