Vlookup date and time issue

**Mitch199** · 05-13-2015, 11:28 PM

Hi experts

I am slowly working through my sheet problem. The stumbling block I am now on is this:

I would like to lookup and display entries in a row of data using the date. However, the first column with the date in it also stores the time next to it, like this:

dd/mm/yyyy hh:mm

I can use a vlookup as follows: =VLOOKUP(DATEVALUE("05/04/2015"),C12:K110,4,FALSE) and when the entry was exactly at midnight, 12:00:00am (very rare but a few samples were done bang on midnight) will give me the result.

I am wondering there is a way for the VLOOKUP to disregard the time, and only look for relevant dates. As I have a lot of entries each with totally random times.
Thankyou for any help, I have been looking all over this site like crazy the last couple of days, its fantastic!

**:) Sixthsense :)** · 05-14-2015, 12:31 AM

Try this...

=INDEX(C12:K110,MATCH("05/04/2015",INDEX(TEXT(C12:C110,"dd/mm/yyyy"),),0),4)

**Mitch199** · 05-14-2015, 12:41 AM

Hey man thanks a lot. Your fix is awesome and worked straight away.

I actually kinda worked it out just as you posted - except I just dropped the ",FALSE" off the end of the VLOOKUP and it worked by finding the nearest entry, and if there was more than two entries on the day it now averages them out.

Thanks so much !