• 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

    Hui...

  • 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

Member
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
 

Attachments

  • Chandoo.xlsx
    8.2 KB · Views: 9
Try this:

=LOOKUP(2,1/(($A$3:$A$18=MAX(IF($B$3:$B$18=G3,IF($C$3:$C$18=H3,$A$3:$A$18))))*($B$3:$B$18=G3)*($C$3:$C$18=H3)),$D$3:$D$18)

Needs to be array-entered by pressing CTRL+SHIFT+ENTER, not just ENTER.
 
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
 

Attachments

  • Chandoo.xlsx
    7.9 KB · Views: 3
Sorry. Amended formula below:

=LOOKUP(2,1/(($A$3:$A$18<=MAX(IF($B$3:$B$18=G3,IF($C$3:$C$18=H3,IF($A$3:$A$18<=F3,$A$3:$A$18)))))*($B$3:$B$18=G3)*($C$3:$C$18=H3)),$D$3:$D$18)

with CTRL+SHIFT+ENTER
 
Perhaps this?

[I3]=INDEX($D$3:$D$18,AGGREGATE(14,6,(ROW($D$3:$D$18)-2)/((($C$3:$C$18)=H3)*(($B$3:$B$18)=G3)*(($A$3:$A$18)<=F3)),1))
And drag down.
 

Attachments

  • Copy of Chandoo.xlsx
    9.3 KB · Views: 15
Hi GraH - Guido,

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

=AGGREGATE(14,6,D$3:D$18/($C$3:$C$18=H3)/($B$3:$B$18=G3)/($A$3:$A$18<=F3),1)

Regards
Bosco
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
 
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.
 
Back
Top