Vlookup by date/time in pivot table

Occasional Contributor

hi i'm trying to lookup a value in a pivot table by date/time in previous month and am only getting the top value from the column. how do i add time to the eomonth function or date/time to vlookup, help please!

=VLOOKUP(EOMONTH(TODAY(),-1) +13,1:1048576,5,FALSE)

11 Replies






By the way, EOMONTH(TODAY(),-1)+13 is the 13th of the current month (i.e. December 13, 2021 as I write)

yes i thought that might work so i went to 13.5 but it comes back with NA. i know i'm in this month because i just started generating my data this month but in January I'm hoping to use this function to generate a report. thankyou @Hans Vogelaar 


Could you attach a small sample workbook without sensitive/proprietary information?

i would be glad to but i dont see a way to add attachments@Hans Vogelaar 


If you don't see an area below the box where you compose a reply that says "Drag and drop here or browse files to attach", you could upload the workbook to a cloud service such as OneDrive, Google Drive or Dropbox, then obtain a link to share the uploaded file, and paste the link into your reply.


I'm afraid I don't have access to that SharePoint site.

do you have a email account i could send it to?



hans dot vogelaar at gmail dot com


I have received your email and replied to it.

thanks @Hans Vogelaar im still working on this but i appreciate the reply.