Max + vlookup ? Two columns, range column a, max of column b

[JohnnySix]

Using column 'A' as a measure, how would I pick the highest value on Column 'B' - is there a way to do this without ARRAY formulas ?

Say I have a layout something like so - if I wanted 'MAX' for the 16th of FEB, it should pick 321.00

[TABLE="width: 222"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]321.00[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]32.00[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]213.00[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]32.00[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]68.00[/TD]
[/TR]
[TR]
[TD="align: right"]21-Feb-14[/TD]
[TD="align: right"]5,842.00[/TD]
[/TR]
[TR]
[TD="align: right"]21-Feb-14[/TD]
[TD="align: right"]684.00[/TD]
[/TR]
[TR]
[TD="align: right"]21-Feb-14[/TD]
[TD="align: right"]3,215.00[/TD]
[/TR]
[TR]
[TD="align: right"]24-Feb-14[/TD]
[TD="align: right"]21,561.00[/TD]
[/TR]
[TR]
[TD="align: right"]25-Feb-14[/TD]
[TD="align: right"]58.00[/TD]
[/TR]
[TR]
[TD="align: right"]26-Feb-14[/TD]
[TD="align: right"]56,185.00[/TD]
[/TR]
[TR]
[TD="align: right"]27-Feb-14[/TD]
[TD="align: right"]215.00[/TD]
[/TR]
[TR]
[TD="align: right"]27-Feb-14[/TD]
[TD="align: right"]561.00[/TD]
[/TR]
[TR]
[TD="align: right"]27-Feb-14[/TD]
[TD="align: right"]21.00[/TD]
[/TR]
[TR]
[TD="align: right"]02-Mar-14[/TD]
[TD="align: right"]3,218.00[/TD]
[/TR]
[TR]
[TD="align: right"]03-Mar-14[/TD]
[TD="align: right"]321.00[/TD]
[/TR]
[TR]
[TD="align: right"]04-Mar-14[/TD]
[TD="align: right"]3,218.00[/TD]
[/TR]
[TR]
[TD="align: right"]04-Mar-14[/TD]
[TD="align: right"]321.00[/TD]
[/TR]
[TR]
[TD="align: right"]04-Mar-14[/TD]
[TD="align: right"]82.00[/TD]
[/TR]
[TR]
[TD="align: right"]07-Mar-14[/TD]
[TD="align: right"]854.00[/TD]
[/TR]
[TR]
[TD="align: right"]08-Mar-14[/TD]
[TD="align: right"]384.00[/TD]
[/TR]
</tbody>[/TABLE]

I asked our resident excel guy, and he said the only way to do it was to break it into separate columns for each date on another worksheet and find the max from that.

Is there not a more elegant solution, similar to something like SUMIFS / SUMIF , else I can see the spreadsheet getting VERY slow with so many columns running calculations.

 

[CharlesChuckieCharles]

IF you sort the date column ascending and the value column ascending

Then

=LOOKUP(G2,A1:A21,B1:B21) where G2 is the date you want to check against this will return the max figure for the date

 

[Gilliam]

I found a pretty interesting way to do this, but you will need an extra column.. I'll try and explain as best I can.

If you find a way to populate Column F with the first instance of a new date ( so no repeats... which can be done, there are many posts on this forum like that, i just dont know it off the top of my head), for now, you can insert them manually...

so assuming column F has a date that matches exactly to the date in column A you can use this
=IF(ISBLANK(MATCH(F2,$A$1:$A$21,0)),MAX(INDIRECT("B"&(MATCH(F1,$A$1:$A$21,0)))),MAX(INDIRECT("B"&(MATCH(F1,$A$1:$A$21,0))&":B"&(MATCH(F2,$A$1:$A$21,0))-1)))

so for example, Feb 16 has 5 instances of Feb 16 in column A.
when I use MATCH(F2,$A$1:$A$21,0))-1, it will return the row position of Feb 21, subtract 1 from it, giving row number 5.
indirect passes a range to the MAX function which I intelligently used ("B"&)...
at that point MAX knows which range to use at each date.

 

[daverm]

Using column 'A' as a measure, how would I pick the highest value on Column 'B' - is there a way to do this without ARRAY formulas ?

[TABLE="width: 222"]
<tbody>[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]321.00[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]32.00[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]213.00[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]32.00[/TD]
[/TR]
[TR]
[TD="align: right"]16-Feb-14[/TD]
[TD="align: right"]68.00[/TD]
[/TR]
[TR]
[TD="align: right"]21-Feb-14[/TD]
[TD="align: right"]5,842.00[/TD]
[/TR]
[TR]
[TD="align: right"]21-Feb-14[/TD]
[TD="align: right"]684.00[/TD]
[/TR]
[TR]
[TD="align: right"]21-Feb-14[/TD]
[TD="align: right"]3,215.00[/TD]
[/TR]
[TR]
[TD="align: right"]24-Feb-14[/TD]
[TD="align: right"]21,561.00[/TD]
[/TR]
[TR]
[TD="align: right"]25-Feb-14[/TD]
[TD="align: right"]58.00[/TD]
[/TR]
[TR]
[TD="align: right"]26-Feb-14[/TD]
[TD="align: right"]56,185.00[/TD]
[/TR]
[TR]
[TD="align: right"]27-Feb-14[/TD]
[TD="align: right"]215.00[/TD]
[/TR]
[TR]
[TD="align: right"]27-Feb-14[/TD]
[TD="align: right"]561.00[/TD]
[/TR]
[TR]
[TD="align: right"]27-Feb-14[/TD]
[TD="align: right"]21.00[/TD]
[/TR]
[TR]
[TD="align: right"]02-Mar-14[/TD]
[TD="align: right"]3,218.00[/TD]
[/TR]
[TR]
[TD="align: right"]03-Mar-14[/TD]
[TD="align: right"]321.00[/TD]
[/TR]
[TR]
[TD="align: right"]04-Mar-14[/TD]
[TD="align: right"]3,218.00[/TD]
[/TR]
[TR]
[TD="align: right"]04-Mar-14[/TD]
[TD="align: right"]321.00[/TD]
[/TR]
[TR]
[TD="align: right"]04-Mar-14[/TD]
[TD="align: right"]82.00[/TD]
[/TR]
[TR]
[TD="align: right"]07-Mar-14[/TD]
[TD="align: right"]854.00[/TD]
[/TR]
[TR]
[TD="align: right"]08-Mar-14[/TD]
[TD="align: right"]384.00[/TD]
[/TR]
</tbody>[/TABLE]

Any particular reason not to use an array formula?

If the information above starts in A1 and the date you are searching is in cell A23 then the following array formula is pretty simple:

{=MAX(IF(($A$1:$A$21=$A$23),$B$1:$B$21))}

(entered with Ctrl+Shift+Enter)

 

[daverm]

Assuming same set up as my previous post think this will achieve the same result without using an array formula:

=MAX(OFFSET(INDIRECT(CONCATENATE("B",MATCH($A$23,$A$1:$A$21,0)),1),0,0,COUNTIF($A$1:$A$21,$A$23),1))

 

[Jborg]

Data Starts from A1 to B21. D1 houses the date you wish to extract the MAX of: Put this formula in F1 and wrap into a sumproduct. In this way you do not have to press Ctrl=Shift+enter, if that is what you are after for not using an array formula.
=SUMPRODUCT(MAX(--($A$1:$A$21=$D$1)*$B$1:$B$21))

 

[Peter_SSs]

Similar concept to JBorg and for Excel 2010 or later.

Excel Workbook
	A	B	C	D	E
1	Date	Value		Date of Interest	Max
2	16-Feb-14	321		16-Feb-14	321
3	16-Feb-14	32
4	16-Feb-14	213
5	16-Feb-14	32
6	16-Feb-14	68
7	21-Feb-14	5,842.00
8	21-Feb-14	684
9	21-Feb-14	3,215.00
10	24-Feb-14	21,561.00
11	25-Feb-14	58
12	26-Feb-14	56,185.00
13	27-Feb-14	215
14	27-Feb-14	561
15	27-Feb-14	21
16	2-Mar-14	3,218.00
17	3-Mar-14	321
18	4-Mar-14	3,218.00
19	4-Mar-14	321
20	4-Mar-14	82
21	7-Mar-14	854
22	8-Mar-14	384
23
Max

 

[MAHMOUD-LEE]

This formula can be used also
It must be pressure on the "CTRL + SHIFT + ENTER"
{=MAX(IF($A$2:$A$22=D2;$B$2:$B$22))}

 

[Peter_SSs]

This formula can be used also
It must be pressure on the "CTRL + SHIFT + ENTER"
{=MAX(IF($A$2:$A$22=D2;$B$2:$B$22))}

Not sure how that really differs from post #4 suggestion..

{=MAX(IF(($A$1:$A$21=$A$23),$B$1:$B$21))}

(entered with Ctrl+Shift+Enter)

.. or if you noticed the original request ..

.. is there a way to do this without ARRAY formulas ?

 

[MAHMOUD-LEE]

I am very sorry Mister "Peter"
I did not notice it