max number in a lookup

Alexandra12

New Member
Joined
May 29, 2008
Messages
33
Hello Guys,


Does anyone know what the formula is to get the max value from the below


I need to look up the date and hour in 1 cell and give me back the highest number for that DATE AND THAT HOUR in another table.

At the moment I tried this, but it does not help :

=VLOOKUP(B2,'1245'!A:F,MAX('1245'!F:F)*6,0)

I also tried =vlookup(max(
but it gives me back the 1st result for that hour and not the highest value.



Here is data from the sheet where I need to have my results



B D
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=131 border=0 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl18 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 98pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=131 height=34> Hourly Data</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl17 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39588">"0/05/2008 00:00</TD></TR></TBODY></TABLE>
Here is the data from the other sheet that I am doing the Vlookup on to get the highest number :


The data in column D is automatically pasted in while gathering the data due to the format it comes in, so I would prefer to leave this as is if possible and for the lookup to compare against the truncated date and hour in column A

A D E F<TABLE style="WIDTH: 285pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=379 border=0 x:str><COLGROUP><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 115pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=153 height=17>Truncated Text</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 91pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=121>Localtimestamp</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 33pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=44>Value</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 46pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=61>Units</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39568">30/04/2008 00:00</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="39568">04/30/08 00:00</TD><TD class=xl21 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>625</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">kW</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39568">30/04/2008 00:00</TD><TD class=xl23 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num="39568.010416666664">04/30/08 00:15</TD><TD class=xl21 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right x:num>608</TD><TD class=xl19 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">kW</TD></TR></TBODY></TABLE>



Any ideas ?


Thanks in advance
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
I doubt if VLOOKUP is appropriate here. Try something like

=MAX(IF('1234'!A1:A100=B2,'1234'!F1:F100))

This formula needs to be confirmed with CTRL + SHIFT +ENTER so that curly braces like { and } appear around the formula in the formula bar.

Note: I'm assuming that B2 will exactly match some values in '1234'!A1:A100, you talk about matching date and hour.....will minutes be shown?
 
Upvote 0
This is an array formula, and therefore you have to press Ctrl+Shift+Enter after you paste it in the formula bar, not just Enter.

My ranges are as follows:

F4:F13 - the values of which you're trying to find the max
E4:E13 - the dates to which each value in column F corresponds
A4 - the result of VLOOKUP of the date you want; you can insert your VLOOKUP function in its place

=INDEX($F$4:$F$13,MATCH(MAX($F$4:$F$13*($E$4:$E$13=$A$4)),$F$4:$F$13,0))

Assuming all values in F4:F13 (red) are greater than 0. Hope this helps.
 
Upvote 0
Hello Barry,

There should be no minutes, all minutes should be zero.

I tried your formula as I was just working on that as per a previous post, but it brings back a 0 result :

Formula is : =MAX(IF('1245'!$A:$A=B3,'1245'!$E:$E))

When I do the crtl/shift/enter i get a NUM Error

Any suggestions ?

Thanks in advance
 
Upvote 0
You can't use whole column references like $A:$A with that type of formula, you need to use a range like $A1:$A1000
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top