Lookup based on max value of a dated range

[Fatumpch]

Hi All,

Ok - my head hurts (sadly not through overindulgence - although I'm considering it as a better option than trying to get my brain around this conundrum) - I'm sure this is simple and I'm probably just getting lost in the logic.... So I call upon your collective genius to remind me how age has destroyed my brain, so much that it can comprehend the weather in greater detail than being able to get the desired result from an equation

So, in the table below I need to bring back the resulting farm that produced the largest amount of fruit in a given month (I can bring back the quantity - as the MAX function happily works this out). The options for which fruit and month are driven by a drop down menu, with the resulting farm and yield formulaically calculated from the data array.

Really appreciate any help!

Range: A1:F16
Fruit to lookup: I2 (drop down list, but for sake of this example - just the single cell)
Month to examine: I3 (drop down list, but for sake of this example - just the single cell)

Resulting Farm: I5
Yield: I6

	A	B	C	D	E	F	G	H	I
1	Fruit	Farm	Jan Yield	Feb Yield	Mar Yield	Apr Yield
2	Apples	Red	10	20	30	40		What Fruit?	Apples
3	Apples	Blue	20	30	20	50		What Month?	Jan
4	Apples	Green	30	10	10	60
5	Pears	Red	10	20	30	40		Best Farm?	Formula?
6	Pears	Blue	20	30	20	50		Qty Yielded?	Formula?
7	Pears	Green	30	10	10	60
8	Kiwi	Red	10	20	30	40
9	Kiwi	Blue	20	30	20	50
10	Kiwi	Green	30	10	10	60
11	Oranges	Red	10	20	30	40
12	Oranges	Blue	20	30	20	50
13	Oranges	Green	30	10	10	60
14	Tomatoes	Red	10	20	30	40
15	Tomatoes	Blue	20	30	20	50
16	Tomatoes	Green	30	10	10	60

<tbody>
</tbody>

 

[Eric W]

Welcome to the board.

I'd recommend you just use Jan, Feb, Mar, etc. in the headings, as it makes the formulas much simpler. Given that, here's one way to do it:

Excel 2012

	A	B	C	D	E	F	G	H	I
1	Fruit	Farm	Jan	Feb	Mar	Apr
2	Apples	Red	10	20	30	40		What Fruit?	Oranges
3	Apples	Blue	20	30	20	50		What Month?	Apr
4	Apples	Green	30	10	10	60
5	Pears	Red	10	20	30	40		Best Farm?	Green
6	Pears	Blue	20	30	20	50		Qty Yielded?	60
7	Pears	Green	30	10	10	60
8	Kiwi	Red	10	20	30	40
9	Kiwi	Blue	20	30	20	50
10	Kiwi	Green	30	10	10	60
11	Oranges	Red	10	20	30	40
12	Oranges	Blue	20	30	20	50
13	Oranges	Green	30	10	10	60
14	Tomatoes	Red	10	20	30	40
15	Tomatoes	Blue	20	30	20	50
16	Tomatoes	Green	30	10	10	60

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula I5 {=INDEX(B2:B16,MATCH(I2&"|"&I6,A2:A16&"|"&INDEX(C2:F16,0,MATCH(I3,C1:F1,0)),0))} I6 {=LARGE(IF(A2:A16=I2,INDEX(C2:F16,0,MATCH(I3,C1:F1,0))),1)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Note that those are both array formulas. There is also no allowance for ties, if there's a tie, the first match will be listed.

Hope this helps.

 

[Aladin Akyurek]

In I5 control+shift+enter, not just enter, and copy across until a blank appears.

=IFERROR(INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=I$2,IF(ISNUMBER(SEARCH(I$3,$C$1:$F$1)),IF($C$2:$C$16=I$6,ROW($A$2:$A$16)-ROW($A$2)+1))),COLUMNS($I$5:I5))),"")

In I6 control+shift+enter, not just enter:

=MAX(IF($A$2:$A$16=I$2,IF(ISNUMBER(SEARCH(I$3,$C$1:$F$1)),$C$2:$F$16)))

Invoke MAXIFS in I6 if you have this function on your system.

 

[Fatumpch]

Thanks for the welcome - and an even bigger thanks for the solution!

Eric - you are a genius - works like a charm, and yes, take your point on the headings (the actual sheet, as you can imagine, is a little more complex, but fortunately the headings are pretty straight forward).

Have a fabulous day and once again a huge thanks! I can now let my brain ponder the wonders of the many varieties of British rainfall once more

 

[Fatumpch]

Thanks also Aladin - that was where I started and can now see where I went astray. Thanks lots!

 

[Aladin Akyurek]

Try some ties and you’ll understand why I posted an alternative...

 

[Fatumpch]

Thanks Aladin I see your point... good call!

 

[Aladin Akyurek]

Thanks Aladin I see your point... good call!

You are welcome.