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

[TABLE="width: 672"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Fruit
[/TD]
[TD]Farm
[/TD]
[TD]Jan Yield
[/TD]
[TD]Feb Yield
[/TD]
[TD]Mar Yield
[/TD]
[TD]Apr Yield
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Apples
[/TD]
[TD]Red
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]40
[/TD]
[TD][/TD]
[TD]What Fruit?
[/TD]
[TD]Apples
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Apples
[/TD]
[TD]Blue
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]50
[/TD]
[TD][/TD]
[TD]What Month?
[/TD]
[TD]Jan
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Apples
[/TD]
[TD]Green
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]60
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Pears
[/TD]
[TD]Red
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]40
[/TD]
[TD][/TD]
[TD]Best Farm?
[/TD]
[TD]Formula?
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Pears
[/TD]
[TD]Blue
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]50
[/TD]
[TD][/TD]
[TD]Qty Yielded?
[/TD]
[TD]Formula?
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Pears
[/TD]
[TD]Green
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]60
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Kiwi
[/TD]
[TD]Red
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]40
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Kiwi
[/TD]
[TD]Blue
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Kiwi
[/TD]
[TD]Green
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]60
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Oranges
[/TD]
[TD]Red
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]40
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Oranges
[/TD]
[TD]Blue
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Oranges
[/TD]
[TD]Green
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]60
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]Tomatoes
[/TD]
[TD]Red
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]40
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]Tomatoes
[/TD]
[TD]Blue
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]20
[/TD]
[TD="align: right"]50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]Tomatoes
[/TD]
[TD]Green
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]60
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Fruit	Farm	Jan	Feb	Mar	Apr
Apples	Red	What Fruit?	Oranges
Apples	Blue	What Month?	Apr
Apples	Green
Pears	Red	Best Farm?	Green
Pears	Blue	Qty Yielded?
Pears	Green
Kiwi	Red
Kiwi	Blue
Kiwi	Green
Oranges	Red
Oranges	Blue
Oranges	Green
Tomatoes	Red
Tomatoes	Blue
Tomatoes	Green

<tbody>
[TD="align: center"]1[/TD]

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

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

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]

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

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

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

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

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

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

</tbody>

Sheet1​

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




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.