# vlookup datetime FALSE; return value from 1 of two columns based on exact datetime

Based on data below, I am trying to do a vlookup such that if the datetime in ends in 18:59 return the value in column_3, if the datetime ends in 6:59 return the value in column_4...

``````10/7/2013 18:59  534    2246
10/7/2013 6:59  2265    2241
10/6/2013 18:59 2265    2070
10/6/2013 6:59  2165    2070
10/5/2013 18:59 2165    2076
10/5/2013 6:59  1599    2076
10/4/2013 18:59 1599    1934
10/4/2013 6:59  1772    1934
10/3/2013 18:59 1772    1972
10/3/2013 6:59  2004    1972
10/2/2013 18:59 2004    2047
10/2/2013 6:59  2034    2047
10/1/2013 18:59 2034    1976
10/1/2013 6:59  2074    1976
``````

How in the world do I do this?

-

There's no `VLOOKUP` to do here. Just use `IF` and `HOUR`:

``````=IF(HOUR(A1)=18, B1, C1)
``````

`HOUR` returns the hour, as you might have guessed.

If there can be more hours and minutes:

``````=IF(TEXT(A1,"hh:mm")="18:59", B1, IF(TEXT(A1,"hh:mm")="06:59", C1, "")
``````

`TEXT` returns the text version of the provided format, here I used `hh:mm`.

As per new information in comment:

I'm not sure because it isn't so clear, but a generic formula could perhaps be:

``````=VLOOKUP(A1, 'Pathname[Filename]Sheetname'!A:D, IF(MOD(A1,1)>0.5, 3, 4), 0)
``````

`MOD(A1,1)` gives the fraction of the date. If the date was noon, it would return 0.5. If the time of that date is 18:59, it will return a value above 0.5 and at 6:59 a value below 0.5.

-
The sheet references data in another spreadsheet altogether, but the datetimes must be dead on and only occur once. Is is possible to nest your query within a vlookup against the datetime in another sheet on another server? – Julie Kadas Oct 8 '13 at 20:18
@JulieKadas Amended my answer. See if this suits your situation? – Jerry Oct 8 '13 at 20:50