Large Lookup Problem w Duplicates

[mgirvin]

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?

[gauntletxg]

Have you tried using AutoFilter?

[Aladin Akyurek]

If you insist on or desire a formula system...

YEAR	MONTH	RESTAURANT	PRODUCT	SALES		YEAR	2008
2008	October	McDonald's	Big Mac	$100,000.00		MONTH	October
2008	October	Burger King	Double Whopper	$1,500.00		RESTAURANT	McDonald's
2008	October	McDonald's	Triple Whopper	$100,000.00		Top	3
2008	November	McDonald's	Filet O Fish	$20,000.00		Largest Value	$99,885.00
2008	October	McDonald's	Tofu	$55,000.00		Count	3
2008	October	McDonald's	Apples	$99,885.00		SALES	PRODUCT
						$100,000.00	Big Mac
						$100,000.00	Triple Whopper
						$99,885.00	Apples

H1:H4 houses the criteria/conditions.

H5:

Control+shift+enter, not just enter...

=LARGE(IF($A$2:$A$7=H1,IF($B$2:$B$7=H2,IF($C$2:$C$7=H3,$E$2:$E$7))),H4)

H6:

=SUMPRODUCT(--($A$2:$A$7=H1),--($B$2:$B$7=H2),--($C$2:$C$7=H3),--($E$2:$E$7>=H5))

G8:

Control+shift+enter, not just enter...

=IF(ROWS($G$8:G8)<=H$6,
  LARGE(IF($A$2:$A$7=H$1,IF($B$2:$B$7=H$2,IF($C$2:$C$7=H$3,$E$2:$E$7))),
    ROWS($G$8:G8)),"")

and copy down.

H8:

Control+shift+enter, not just enter...

 
=IF(ISNUMBER(G8),INDEX($D$2:$D$7,SMALL(
    IF($A$2:$A$7=H$1,IF($B$2:$B$7=H$2,IF($C$2:$C$7=H$3,IF($E$2:$E$7=$G8,
      ROW($D$2:$D$7)-ROW($D$2)+1)))),COUNTIF($G$8:G8,G8))),"")

and copy down.

[mgirvin]

Dear gauntletxg,

Thanks for the tip! Yes, Auto Filter would be so simple, but what I was after was a dynamic formula that sat on a sheet and looked at the Table on another sheet. And the Table will get data added to it periodically and so the formula "always update" solution was preferable.

[mgirvin]

Dear Aladin Akyurek,

You are like a super hero that flies in and saves the day!

The most amazing part of your formula was the fourth IF that asked the sales range if it was equal to the value one cell to the left, and then you SMALLed with a COUNTIF!

So elegant and efficient!

Thanks for putting up with my desire for a formula when Auto Filter would be so easy!

You and many others at this Message Board really make the world a better place with your amazing Excel EXCELlence!!

[sitngo]

hi guys,

Having just looked through the steps of this formula, I'm having trouble understanding what the 'small' function does (why aren't we looking for the large), as well as the Row - Row part of the function. I know it's a lot of work, but i'd like to understand the steps of this formula, please help!

[mgirvin]

Dear sitngo,

The LARGE is used to find the values. The LARGE function can deal with duplicates. However, the formula that retrieves the restaurants needs a way to deal with duplicates. The SMALL and COUNTIF functions help to deal with duplicate values. After the array part of the formula delivers an array of FALSEs and values to the SMALL function, the SMALL and COUNTIF functions will retrieve the first duplicate when the COUNTIF is 1 and the second duplicate when the COUNTIF is 2.

When you type the formulas and this example into your spreadsheet, type a duplicate value in, and then run the formula evaluator to see how it is helping with duplicates.

I hope that helps.

[Aladin Akyurek]

Originally Posted by sitngo

hi guys,

Having just looked through the steps of this formula, I'm having trouble understanding what the 'small' function does (why aren't we looking for the large), as well as the Row - Row part of the function. I know it's a lot of work, but i'd like to understand the steps of this formula, please help!

I guess you mean SMALL that appears in the last formula... You can substitute LARGE for SMALL in that formula. The substitution will affect the order of items in the result, not the content.

SMALL works on the result array that IF delivers. This result array consists of a relevant set of ascending row numbers, adjusted to start at 1...

In

ROW($D$2:$D$7)-ROW($D$2)+1

ROW($D$2:$D$7) would yield: 2,3,4,5,6,7

ROW($D$2) would yield 2

Substraction would result: 0,1,2,3,4,5

Addition (+1) would then give: 1,2,3,4,5,6

Depending on the test condition in IF some of the numbers in the result set will be replaced with FALSE. SMALL will act on the surviving numbers and feed INDEX for retrieval.