Vlookup not working with dragged down dates!

jacc99

New Member
Joined
Jun 14, 2021
Messages
12
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. MacOS
Hey guys.
I will try and explain this in as much detail as I can and hope you understand!

So effectively i have a data set of around 370,000 rows of data, one row (price) for every minute of the year. However, there are many minutes that are missing and thus i need to flag them as missing by inserting the missing times & dates with a "N/A" instead of a price.
So, on a seperate sheet I have used the =sequence function to insert 1 to 525643 in rows, and then auto filled down the dates and times from 01/01/2020 17:00 to 31/12/2020 16:58.
I have then tried to use the =IFError(Vlookup(...)) function to bring over the prices that match a date and time, and insert "N/A" where there is not match.

However, my problem starts when i try to drag down the vlookup function. It works for the first few dates&times that i wrote in myself or copied over from the orignial worksheet, however from the row that i dragged down it automatically returns the "N/A" rather than the relevant price. Even though i can see that the dates on the two sheets do in fact match.

I have attached some photos which may make this a bit clearer.
I auto filled by dragging down from row 17:05. As you can see, for example the 17:06 time stamp on both sheets is clearly the same format and date/time, however the vlookup does not seem to recognise the price in column B.

*I have realised this is an issue with the date formatting - when i chose a random time e.g 17.30, the formula tab presents it as 17:30:00 if i delete the 00 at the end, it suddenly returns the correct price value rather than the "N/A". However, it is not feasible to do this for half a million data points and hence i ask if there is a alternative method? I have tried to reformat the dates on both worksheets to be the same however this does not fix the issue.*
Screenshot 2021-06-14 at 23.02.37.png
Screenshot 2021-06-14 at 23.13.44.png
Screenshot 2021-06-14 at 23.13.33.png


I hope that makes sense. Please let me know your thoughts and whether i am missing something!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,925
Office Version
  1. 2016
Platform
  1. Windows
Hi Jacc99,

Changing the format does not actually change the cell contents, just how they appear. I suspect this is the challenge with how Excel holds floating point numbers such that two date/time cells look the same but are actually held as different binary numbers.

Your two options would be:
a. Convert the date and time columns to a text field (e.g. TEXT(A2,"mm/dd/yy h:mm") and the VLOOKUP should work using the new columns.
b. Excel holds dates as integer days since 1/1/1900 and time as fractions of a day. One minute = 0.000694444 so if you add a new column for the EURUSD data and round the date/time then you can round the search Date/Time cell also and it should work.

Jacc99.xlsx
ABC
1Date and time StampRoundedClose Quote
201/01/20 17:0001/01/20 17:001.12127
301/01/20 17:0101/01/20 17:011.12128
401/01/20 17:0201/01/20 17:021.12129
501/01/20 17:0301/01/20 17:031.1213
601/01/20 17:0401/01/20 17:041.12131
701/01/20 17:0501/01/20 17:051.12132
801/01/20 17:0601/01/20 17:061.12133
901/01/20 17:0701/01/20 17:071.12134
1001/01/20 17:0801/01/20 17:081.12135
1101/01/20 17:0901/01/20 17:091.12136
1201/01/20 17:1001/01/20 17:101.12137
1301/01/20 17:1101/01/20 17:111.12138
1401/01/20 17:1201/01/20 17:121.12139
1501/01/20 17:1301/01/20 17:131.1214
EURUSD
Cell Formulas
RangeFormula
B2:B15B2=ROUND(A2,5)


Jacc99.xlsx
BC
1Date and time StampClose Quote
201/01/20 17:001.12127
301/01/20 17:011.12128
401/01/20 17:021.12129
501/01/20 17:031.1213
601/01/20 17:041.12131
701/01/20 17:051.12132
801/01/20 17:061.12133
901/01/20 17:071.12134
1001/01/20 17:081.12135
1101/01/20 17:091.12136
1201/01/20 17:101.12137
1301/01/20 17:111.12138
1401/01/20 17:121.12139
1501/01/20 17:131.1214
Sheet2
Cell Formulas
RangeFormula
C2:C15C2=VLOOKUP(ROUND(B2,5),EURUSD!B:C,2,0)
 

jacc99

New Member
Joined
Jun 14, 2021
Messages
12
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. MacOS
Hi Jacc99,

Changing the format does not actually change the cell contents, just how they appear. I suspect this is the challenge with how Excel holds floating point numbers such that two date/time cells look the same but are actually held as different binary numbers.

Your two options would be:
a. Convert the date and time columns to a text field (e.g. TEXT(A2,"mm/dd/yy h:mm") and the VLOOKUP should work using the new columns.
b. Excel holds dates as integer days since 1/1/1900 and time as fractions of a day. One minute = 0.000694444 so if you add a new column for the EURUSD data and round the date/time then you can round the search Date/Time cell also and it should work.

Jacc99.xlsx
ABC
1Date and time StampRoundedClose Quote
201/01/20 17:0001/01/20 17:001.12127
301/01/20 17:0101/01/20 17:011.12128
401/01/20 17:0201/01/20 17:021.12129
501/01/20 17:0301/01/20 17:031.1213
601/01/20 17:0401/01/20 17:041.12131
701/01/20 17:0501/01/20 17:051.12132
801/01/20 17:0601/01/20 17:061.12133
901/01/20 17:0701/01/20 17:071.12134
1001/01/20 17:0801/01/20 17:081.12135
1101/01/20 17:0901/01/20 17:091.12136
1201/01/20 17:1001/01/20 17:101.12137
1301/01/20 17:1101/01/20 17:111.12138
1401/01/20 17:1201/01/20 17:121.12139
1501/01/20 17:1301/01/20 17:131.1214
EURUSD
Cell Formulas
RangeFormula
B2:B15B2=ROUND(A2,5)


Jacc99.xlsx
BC
1Date and time StampClose Quote
201/01/20 17:001.12127
301/01/20 17:011.12128
401/01/20 17:021.12129
501/01/20 17:031.1213
601/01/20 17:041.12131
701/01/20 17:051.12132
801/01/20 17:061.12133
901/01/20 17:071.12134
1001/01/20 17:081.12135
1101/01/20 17:091.12136
1201/01/20 17:101.12137
1301/01/20 17:111.12138
1401/01/20 17:121.12139
1501/01/20 17:131.1214
Sheet2
Cell Formulas
RangeFormula
C2:C15C2=VLOOKUP(ROUND(B2,5),EURUSD!B:C,2,0)
Hi Toadstool, That was great help! thanks
 

Forum statistics

Threads
1,141,581
Messages
5,707,202
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top