Vlookup problem with Date Time
 

Hi

Anyone know how to fix this problem?

I have SHEET1 with a date/time in cell A1 and then a formula to add one hour
to the cell from the row above (for cell A4 the formula is =+A3+(TIME(1,0,0))
to generate a colum of date/times that are 1 hour increments.

On SHEET2 I start with the same date/time in cell A1 (from A1 on SHEET1) and
add several hourly values to it, several times. For example row 5 contains;

cell A5 contains "+G4"
cell B5 contains "=+A5+TIME(J5,0,0)" where J5 contains 0
cell C5 contains "=+B5+TIME(K5,0,0)" where K5 contains 1
cell D5 contains "=+C5+TIME(L5,0,0)" where L5 contains 2
cell E5 contains "=+D5+TIME(M5,0,0)" where M5 contains 6
cell F5 contains "=+E5+TIME(N5,0,0)" Where N5 contains 10
cell G5 contains "=+F5+TIME(O5,0,0)" where O5 contains 8

The next row references G from the previous row. So;
cell A6 contains "+G5"

and so on.
The problem is that if I use Vlookup or Index/Match to search column B on
SHEET2 to match a value from column A on SHEET1, I can't get an exact match.
=INDEX(SHEET2!$B$1:$I$501,(MATCH(A30,SHEET2!$B$1:$ B$501,0)),8)

If I actually type the date/time (that is calculated and displayed in cell
A30) into A30, (type "1/1/06 10:00 AM" into A30) the correct result is
returned from the Index/Match.

I tried changing the Match_type to 1 but then every date/time from SHEET1
returned a match if it was greater than the date/time in SHEET2 but less than
the next day.

Thanks

Norma