Large Lookup Problem w Duplicates

mgirvin

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,248
Office Version
  1. 365
Platform
  1. Windows
Dear Smartest Excelers In The World,

In cells A1: E7, I have this data set:

YEAR MONTH RESTAURANT PRODUCTS SALES
2008 October McDonald's Big Mac $100,000.00
2008 October Burger King Double Whopper $1,500.00
2008 October McDonald's Triple Whopper $100,000.00
2008 November McDonald's Filet O Fish $20,000.00
2008 October McDonald's Tofu $55,000.00
2008 October McDonald's Apples $99,855.00

Using a formula, I want to return the three largest values and the associated PRODUCTS based on these three criteria:
YEAR = 2008
MONTH = October
RESTAURANT = McDonald's

The resultant data set should be:

SALES PRODUCTS
$100,000.00 Big Mac
$100,000.00 Triple Whopper
$99,855.00 Apples

What formula would I use to return the values $100,000, $100,000, and $99,885 for the Sales column? And what formula would I use to return Big Mac, Triple Whopper, and Apples for the PRODUCTS column?

Any ideas, Excel formula Masters?
 
Sort by date Sort by votes
Is it possible to have the data on one worksheet and the formula results on another?
The formulas only worked for me while the data and results were on the same page, but I've been asked to create a dashboard on a separate worksheet in the same workbook.
Do you know why this wouldn't work?
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Brooklyn22 said:
Is it possible to have the data on one worksheet and the formula results on another?
The formulas only worked for me while the data and results were on the same page, but I've been asked to create a dashboard on a separate worksheet in the same workbook.
Do you know why this wouldn't work?
Click to expand...

You can rebuild the following set up...

Sheet1, A1:E7, (Data)
[TABLE="width: 386"]
<colgroup><col style="width: 48pt;" width="64"> <col style="width: 69pt; mso-width-source: userset; mso-width-alt: 3271;" width="92"> <col style="width: 93pt; mso-width-source: userset; mso-width-alt: 4408;" width="124"> <col style="width: 105pt; mso-width-source: userset; mso-width-alt: 4977;" width="140"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3356;" width="94"> <tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]YEAR[/TD]
[TD="class: xl64, width: 92, bgcolor: transparent"]MONTH[/TD]
[TD="class: xl64, width: 124, bgcolor: transparent"]RESTAURANT[/TD]
[TD="class: xl64, width: 140, bgcolor: transparent"]PRODUCT[/TD]
[TD="class: xl65, width: 94, bgcolor: transparent"]SALES[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]2008[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]October[/TD]
[TD="class: xl67, width: 124, bgcolor: transparent"]McDonald's[/TD]
[TD="class: xl67, width: 140, bgcolor: transparent"]Big Mac[/TD]
[TD="class: xl68, width: 94, bgcolor: transparent"]$100,000.00 [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]2008[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]October[/TD]
[TD="class: xl67, width: 124, bgcolor: transparent"]Burger King[/TD]
[TD="class: xl67, width: 140, bgcolor: transparent"]Double Whopper[/TD]
[TD="class: xl68, width: 94, bgcolor: transparent"]$1,500.00 [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]2008[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]October[/TD]
[TD="class: xl67, width: 124, bgcolor: transparent"]McDonald's[/TD]
[TD="class: xl67, width: 140, bgcolor: transparent"]Triple Whopper[/TD]
[TD="class: xl68, width: 94, bgcolor: transparent"]$100,000.00 [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]2008[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]November[/TD]
[TD="class: xl67, width: 124, bgcolor: transparent"]McDonald's[/TD]
[TD="class: xl67, width: 140, bgcolor: transparent"]Filet O Fish[/TD]
[TD="class: xl68, width: 94, bgcolor: transparent"]$20,000.00 [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]2008[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]October[/TD]
[TD="class: xl67, width: 124, bgcolor: transparent"]McDonald's[/TD]
[TD="class: xl67, width: 140, bgcolor: transparent"]Tofu[/TD]
[TD="class: xl68, width: 94, bgcolor: transparent"]$55,000.00 [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]2008[/TD]
[TD="class: xl67, width: 92, bgcolor: transparent"]October[/TD]
[TD="class: xl67, width: 124, bgcolor: transparent"]McDonald's[/TD]
[TD="class: xl67, width: 140, bgcolor: transparent"]Apples[/TD]
[TD="class: xl68, width: 94, bgcolor: transparent"]$99,885.00 [/TD]
[/TR]
</tbody>[/TABLE]

Sheet2, A:B, (Processing)
[TABLE="width: 192"]
<colgroup><col style="width: 111pt; mso-width-source: userset; mso-width-alt: 5262;" width="148"> <col style="width: 81pt; mso-width-source: userset; mso-width-alt: 3840;" width="108"> <tbody>[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]YEAR[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]2008[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]MONTH[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]October[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]RESTAURANT[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]McDonald's[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]Top[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]Largest Value[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]99885[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]Count[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 148, bgcolor: transparent"]SALES[/TD]
[TD="class: xl68, width: 108, bgcolor: transparent"]PRODUCT[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 148, bgcolor: transparent"]$100,000.00 [/TD]
[TD="class: xl66, width: 108, bgcolor: transparent"]Big Mac[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 148, bgcolor: transparent"]$100,000.00 [/TD]
[TD="class: xl66, width: 108, bgcolor: transparent"]Triple Whopper[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 148, bgcolor: transparent"]$99,885.00 [/TD]
[TD="class: xl66, width: 108, bgcolor: transparent"]Apples[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 148, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 108, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


B5, control+shift+enter, not just enter:
Rich (BB code): 
=LARGE(IF(Sheet1!$A$2:$A$7=B1,IF(Sheet1!$B$2:$B$7=B2,
  IF(Sheet1!$C$2:$C$7=B3,Sheet1!$E$2:$E$7))),B4)

B6, just enter:
Rich (BB code): 
=SUMPRODUCT(--(Sheet1!$A$2:$A$7=B1),--(Sheet1!$B$2:$B$7=B2),
  --(Sheet1!$C$2:$C$7=B3),--(Sheet1!$E$2:$E$7>=B5))

A8, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IF(ROWS($A$8:A8)<=B$6,
  LARGE(IF(Sheet1!$A$2:$A$7=B$1,IF(Sheet1!$B$2:$B$7=B$2,
   IF(Sheet1!$C$2:$C$7=B$3,Sheet1!$E$2:$E$7))),ROWS($A$8:A8)),"")

B8, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IF(ISNUMBER(A8),INDEX(Sheet1!$D$2:$D$7,SMALL(
  IF(Sheet1!$A$2:$A$7=B$1,IF(Sheet1!$B$2:$B$7=B$2,
   IF(Sheet1!$C$2:$C$7=B$3,IF(Sheet1!$E$2:$E$7=$A8,
    ROW(Sheet1!$D$2:$D$7)-ROW(Sheet1!$D$2)+1)))),COUNTIF($A$8:A8,A8))),"")

Here is a link to the file, using the set up above:

http://tinyurl.com/c7apaje
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,225,202
Messages
6,183,541
Members
453,168
Latest member
Luggsy

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