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

• 8.2 KB Views: 7

AliGW

Active Member
Which version of Excel do you have, please?

Excel 2010

AliGW

Active Member
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.

Ateeb Ali

Member
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

• 7.9 KB Views: 2

AliGW

Active Member
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

GraH - Guido

Well-Known Member
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

• 9.3 KB Views: 13

bosco_yip

Excel Ninja
Try,

In "Price" I3, copied down to I4 :

=LOOKUP(1,0/(A\$3:A\$18<=F3)/(B\$3:B\$18=G3)/(C\$3:C\$18=H3),D\$3:D\$18)

Regards
Bosco

Ateeb Ali

Member
Thanks a lot, this worked out

bosco_yip

Excel Ninja
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.
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

GraH - Guido

Well-Known Member
Thank you Bosco for this insight.

Ateeb Ali

Member
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))"