Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.