# max number in a lookup

#### Alexandra12

##### New Member
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 ?

Last edited:

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### barry houdini

##### MrExcel MVP
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?

#### iliace

##### Well-known Member
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.

#### Alexandra12

##### New Member
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 ?

#### barry houdini

##### MrExcel MVP
You can't use whole column references like \$A:\$A with that type of formula, you need to use a range like \$A1:\$A1000

#### Alexandra12

##### New Member
Thanks guys,

It worked a treat

Replies
0
Views
1K
Replies
2
Views
202
Replies
10
Views
715
Replies
0
Views
471
Replies
1
Views
366

1,190,631
Messages
5,982,035
Members
439,750
Latest member
megaman777

### 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.

### Which adblocker are you using?

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

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