• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup Formula in between dates

Ateeb Ali

attached an example file, i need help in vlookup formula or any other way to look up values in between dates
For example if abc purchased on dated: 01-01-19, its next buying date is 01-02-19, so in betwen these two dates whatever dates are, the price lookup should show price on 01-01-19.

If last buying is on 01-04-19, and no record after that, it should lookup that last figure on all afterwards date



Active Member
Try this:


Needs to be array-entered by pressing CTRL+SHIFT+ENTER, not just ENTER.

Ateeb Ali

sorry not working, may be because you did not take notice of date in F column
I can concatenate the values, just tell me simple formula please, i have include simplified file here



Active Member
Sorry. Amended formula below:



Ateeb Ali

Hi GraH - Guido,

If the result range is numeric value, INDEX can be saved, then use AGGREGATE directly, something like :


Sir, that formula not working "=INDEX($AA$2:$AA$64,AGGREGATE(14,6,(ROW($AA$2:$AA$64)-2)/((($Z$2:$Z$64)=F2)*(($Y$2:$Y$64)=E2)*(($X$2:$X$64)<=C2)),1))"
can you please help in this
"X2 to AA64" is my data, AA64 is pricing which I want to fetch

GraH - Guido

Well-Known Member
Hi, in the sample date your data started at row 3, hence this part (ROW($D$3:$D$18)-2) was used.
You need to change to (ROW($AA$2:$AA$64)-1)

EDIT: as kindly suggested by Bosco =AGGREGATE(14,6,AA$2:AA$64/($Z$2:$Z$64=F2)/($Y$2:$Y$64=E2)/($X$2:$X$64<=C2),1), is a shorter version that works.