Get the Effective Price base on Date in Google Sheet-CodePudding

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

CodePudding user response：

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

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

CodePudding user response：

Try

=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)

other solution, by one formula

=query(query({arrayformula(C2:C&"~"&A2:A),A2:C},"select Col1,Col2,Col3,Col4 where Col1<>'~' and Col4<=DATE'"&TEXT($E$1,"yyyy-MM-dd")&"'  order by Col1 desc limit "&counta(unique(A2:A))&" "),"select Col2,Col3,Col4")