CODEWITHSUNDEEP
google-sheets
Minh Duc
Minh Duc

Reputation: 25

Get the Effective Price base on Date in Google Sheet

I have a problem getting the right "Price" for a product based on Effectivity date. I've checked on StackOverFlow but on Google Sheet is not mentioned I need a table of Effective Price table after querying Price change history table Please help me solve this problem Thanks enter image description here

Upvotes: 1

Views: 309

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

Try for chosen codes

=query(A:C,"select B,C where A='"&E3&"' and C<=DATE'"&TEXT($E$1,"yyyy-MM-dd")&"' order by C desc limit 1",0)

enter image description here

other solution, by one formula for all codes at once

=arrayformula(iferror(vlookup(unique(A2:A),query(A2:C,"select A,B,C where C<=DATE'"&TEXT($E$1,"yyyy-MM-dd")&"' order by C desc ",0),{1,2,3},0)))

enter image description here

for all codes at once, including those that are out of bounds

=sort(arrayformula(iferror(vlookup(unique(A2:A),
{query(A2:C,"select A,B,C where C<=DATE'"&TEXT($D$1,"yyyy-MM-dd")&"' order by C desc ",0);
query(A2:C,"select A,' ','-' where A is not null ",0)},
{1,2,3},0))))

enter image description here

Upvotes: 2

idfurw
idfurw

Reputation: 5862

Formula in E3

=QUERY(A:C,"select A,max(C) where C <= date '"&TEXT(E1,"yyyy-MM-dd")&"' and A is not null group by A label A '',max(C) ''",1)

Formula in G3

=ArrayFormula(VLOOKUP(E3:E&F3:F,{A:A&C:C,B:B},2,FALSE))

enter image description here

If you want to have the same sequence of columns as yours, you need 3 formulas.

Upvotes: 0

Related Questions