To Pick data values of later date against the missing dates for a given range of date / period

S

Sheikh Mubin Ashraf

New Member
Joined
Aug 17, 2024
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I'm working on preparing a separate sheet where a set of data points is available for certain dates for a month of July 2024. I want to pick the data points for the missing dates from the next available date data point.
For e.g. data point for July 01, July 02 and July 05 is available, I want that same date point of July 05 must be copied against July 03 and July 04. I have applied VLOOKUP function but it is picking data point of previous date i.e. July 02.
 

Attachments

  • Picture1.jpg
    Picture1.jpg
    64.5 KB · Views: 6
  • Picture2.png
    Picture2.png
    5.6 KB · Views: 6

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Try XLOOKUP:
Book1
ABCDEF
11/07/2024 12:003.8482791/07/20243.848279
22/07/2024 12:003.0069922/07/20243.006992
33/07/2024 12:001.2104373/07/20241.210437
44/07/2024 6:003.3175124/07/20243.317512
55/07/2024 12:002.54465/07/20242.5446
68/07/2024 10:452.1032196/07/20242.103219
710/07/2024 23:500.9386777/07/20242.103219
811/07/2024 10:401.6377298/07/20242.103219
912/07/2024 9:552.8960959/07/20240.938677
1013/07/2024 9:503.02977210/07/20240.938677
1115/07/2024 10:201.83274611/07/20241.637729
1217/07/2024 11:501.10434612/07/20242.896095
1313/07/20243.029772
1414/07/20241.832746
1515/07/20241.832746
1616/07/20241.104346
1717/07/20241.104346
Sheet1
Cell Formulas
RangeFormula
F1:F17F1=XLOOKUP(E1,INT($A$1:$A$12),$B$1:$B$12,,1)
 
Upvote 0
Solution
myall_blues said:
Try XLOOKUP:
Book1
ABCDEF
11/07/2024 12:003.8482791/07/20243.848279
22/07/2024 12:003.0069922/07/20243.006992
33/07/2024 12:001.2104373/07/20241.210437
44/07/2024 6:003.3175124/07/20243.317512
55/07/2024 12:002.54465/07/20242.5446
68/07/2024 10:452.1032196/07/20242.103219
710/07/2024 23:500.9386777/07/20242.103219
811/07/2024 10:401.6377298/07/20242.103219
912/07/2024 9:552.8960959/07/20240.938677
1013/07/2024 9:503.02977210/07/20240.938677
1115/07/2024 10:201.83274611/07/20241.637729
1217/07/2024 11:501.10434612/07/20242.896095
1313/07/20243.029772
1414/07/20241.832746
1515/07/20241.832746
1616/07/20241.104346
1717/07/20241.104346
Sheet1
Cell Formulas
RangeFormula
F1:F17F1=XLOOKUP(E1,INT($A$1:$A$12),$B$1:$B$12,,1)
Click to expand...
Thank You so much. Highly appreciated your response.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Counting zero billing days in Excel until current date
Replies
5
Views
910
awoohaw
awoohaw
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
297
Fuji
F
A
  • Question
Calculate Month End Dates (Weekend dates) based on the previous weekend dates and update values
Replies
3
Views
688
awoohaw
awoohaw
SimonHughes
  • Question
Data Validation - Two Entries in One Cell
Replies
5
Views
148
kvsrinivasamurthy
K
A
  • Question
How do I lock a worksheet and stop data entry after a certain date and time?
Replies
1
Views
388
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,221,417
Messages
6,159,789
Members
451,589
Latest member
Harold14

Share this page

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
Back
Top