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.

 

[JohnnySix]

Thanks for the replies!

To answer a few questions.

The table layout may be changing a lot, with new columns etc, and it may be because I've not used array forumulae that much, but the fact they are not easily removable/editable after being set is particularly problematic.

I am unfortunately stuck on version 2007 of Excel, so any functions new to 2010 and beyond are unavailable to me.

I need to take non-linear data, and effectively make it linear for charting, identifying for each date the maximum and minimum values.

Some days there may be one row [ satisfying both min and max where only a single row exists ] , or up to 15 rows, where I'd need to pick from that date range, the minimum and maximum figures.

Since I'd be repeating the process six times for six sets of aggregated data, I'd hoped there was maybe a little used native function for such a task.

Sadly the AGGREGATE function does not exist in Excel 2007 - as this would solve all my issues.

 

[Peter_SSs]

Is there a problem with the suggestion made in post #6?

 

[Marcelo Branco]

Or

Assuming Data in A1:B21 (see Peter's post #7)

Maybe...


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Date​

[/TD]
[TD]

Max​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

16-Feb-14​

[/TD]
[TD]

321​

[/TD]
[/TR]
</TBODY>[/TABLE]


Observe that the header in D1 is identical to A1 (Field name)

Formula in E2
=DMAX(A1:B22,"Value",D1:D2)

M.

 

[JohnnySix]

Is there a problem with the suggestion made in post #6?

I meant to come back, yes , that was the post with the solution!

=MIN(OFFSET(INDIRECT(CONCATENATE("B",MATCH($N2,$N:$N,0)),1),0,0,COUNTIF($N:$N,$N2),1))

With the "B" being the column letter to reference for the MAX value, using N as the measure.

It goes a little squiffy if there's only one line, but this is rare, and can be manually typed in for those instances. Something that wouldn't work if it was done as an array formula.

Thanks again @Peter_SSs - very much appreciate your help.

Hope this thread is of some value to people searching google / forums like I was trying to find an elegant solution to this.

 

[Peter_SSs]

... yes , that was the post with the solution!

=MIN(OFFSET(INDIRECT(CONCATENATE("B",MATCH($N2,$N:$N,0)),1),0,0,COUNTIF($N:$N,$N2),1))

Now I'm confused. That looks more like the suggestion from post #5, not post #6.

Whilst it should produce the results you want (assuming the date column is sorted), I would suggest that it has two features that it would be better without.

1. It uses the volatile functions OFFSET and INDIRECT.

2. It references whole columns. Why check over 1,000,000 rows if you are only using, say, a few hundred rows.



Do these non-array, non-volatile formulas produce the results you want (after ensuring where I have $1000 you adjust that to be at least as large as the number of rows you are likely to have)?

For MAX (as suggested by Jborg in post #6 ):

=SUMPRODUCT(MAX(--($N$2:$N$1000=$N2)*$B$2:$B$1000))


For MIN:

=SUMPRODUCT(SMALL(--($N$2:$N$1000=$N2)*$B$2:$B$1000,COUNTIF($N$2:$N$1000,"<>"&$N2)+1))

 

[JohnnySix]

I take it all back @Peter_SSs - indeed you are right, the formula was volatile and unreliable. I had max figures repeating across different dates.

I just tried Jborg's #6 - works perfectly!

Thanks again for going the extra mile.

 

[Peter_SSs]

Glad we got there in the end.