Solved: vlookup

[maninjapan]

Hi, I am trying to use Vlookup to populate a column with data based on date and time, however the problem I have is they are 2 seperate columns, one with date, the second with time. I have used Vlookup with single columns without issue previously however this is my first attempt at a vlookup using 2 columns.....

I have tried making a 3rd helper column to combine the date and time but it doesnt seem to be recognizing the data to be looked up. Does anyone know of a thread that already covers this, or is able to explain what I am doing wrong? I have attached an example of the data I am trying to use.

There are 2 sheets of price data, 10 minute and 1 minute. I am trying to populate colum G of the 1 minute data (which has been rounded down to the closest 10 minute period) with the close of the 10 minute price for that time period.


Thanks in advance

[SamT]

The Helper column Formulas should both be
[vba]=ROUND(B2+C2,3)[/vba]
And get rid of the Rounding column.

If you format all the Date and Time columns to "Number" with 5 decimal places, you'll see what is happening.

[maninjapan]

SamT, thanks for the reply. I see what you mean by formating the dates and times as numbers. I tried what you said though. I added that above formula in column A for both sheets, then added the following to column G on 1 minute Sheet, but not receiving any data. I dont suppose you are able to put up a working example?

[VBA]=VLOOKUP(A2,'10 minute'!A:E,5)[/VBA]

Thanks

[SamT]

I am trying to populate colum G of the 1 minute data

Bad Dog! No biscuits for you.

First I had to sort the tables Ascending. I also got rid of the columns that I thought you didn't need. Then I Formatted the Columns To Date, Time, and Accounting, (To align the currency signs,) as I thought you might want.

The Final formulas were
Open Column: =VLOOKUP(B2,'1 minute'!B: D,2) Remove space between the colon and the D
Close Column: =VLOOKUP(B2,'1 minute'!B: D,3)
Note that if the 10 Minute sheet times start before the opening of business time, VLookUp returns "N/A" until the Market opens.

Right now the 10 Minute Sheet only works for a 1 Minute sheet with one date. You will need Helper columns to use the date and times, The helper columns are just the Sum of the Date and time columns.

[maninjapan]

Thanks for that effort sam, I didnt realize data needed to be Ascending.
The sheet seems to be backwards though. I needed the close of 10 minute data in the 1 minute sheet. example 9:00 - 9:09 showing the close from 9:00 of the 10 minute chart. However, I have been able to move your excel about a bit to get what I need. Thanks very much for the help!!!

[SamT]

I needed the close of 10 minute data in the 1 minute sheet. example 9:00 - 9:09 showing the close from 9:00 of the 10 minute chart.

Oops! I thought that was a typographical error.

I guess I don't get a biscuit now.