Vlookup and max() returning a wrong value

[Hodder]

Hi

Ive been struggeling with a problem for some time now, which should be rather easy to solve but excel is not cooperating with me.

I´m making a worksheet where cell "A1" should show the last time an action have been made. This should be found in another worksheet where date and time for previous the actions have been made, and is continously beening updated.

Im using a formula to pick the most recent date using MAX().

I then want to compine the max function with Vlookup, so when i find the last "date" where an action has been made it shows the time for the action in the collumn next to the date.

My formula is like this =Vlookup(Max('sheet2!'$D:$D);'sheet2!'!$D:$E;2)

The referance is the Date collumn D , and the time is Collumn E.
When is enter this, it returns the minimum date instead of the maximum date and then "offset" correctly in the collum next to the "wrong" date.

Can anyone give help me with this??

 

[Comfy]

Try this:

=VLOOKUP(MAX(Sheet2!D:D),Sheet2!D:E,2,0)

I'm not familiar with other versions of excel e.g. by language.

But you appear to be using semi-columns to separate the function objects and you have lots of apostrophes in your function too so you will need to amend this if needed.

Try adding a value for the range lookup zero = False.

 

[Hodder]

Yeha im using a danish version, so there are alot of diffrent functions/objects and symbols that are diffrent.

But adding the ",0" in the end of the function worked!

Thanks alot!

 

[Comfy]

No problem.

If that value is TRUE (1) or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than the lookup_value is returned

 

[Aladin Akyurek]

What happens when you have 2 or more instances of the max value?

 

[Hodder]

In my case it picks the first occurance of the max value, so if three dates are the same and it picks the most recent entered.

 

[Aladin Akyurek]

In my case it picks the first occurance of the max value, so if three dates are the same and it picks the most recent entered.

How about the time that correspond to it?

 

[Hodder]

Lets say i have this:

A B
25-02-2014 10:00
25-02-2014 09:00
25-02-2014 08:00

Then the max value returns the first 25-02-2014 and 10:00.

 

[Aladin Akyurek]

Lets say i have this:

A B
25-02-2014 10:00
25-02-2014 09:00
25-02-2014 08:00

Then the max value returns the first 25-02-2014 and 10:00.

Why 10:00, not 09:00 or not all of them?