Retrieve last non-empty cell based on date criteria

[Rostyslav]

Hi. I have difficulties with resolving the following issue:

I have the prices for the Product #1 that are monthly revised (Table _ Primary data). Some months are blank, that means there were no price review in this period (e.g. as of 3/1/2019), thus, the price of previous month remains actual. I could not delete the blank row, but it could be left empty or filled with number 0 if it could be helpful (now I have empty cell in TEXT format).
Now I have table below (Table _ Calculated result) where I have different dates. In the second column of this table I need to retrieve from the "Table _ Primary data" the last actual price for each responsible dates (expected results are given in example).

Are there any ways to do it with formula (ideally without array formula as there are huge range of products and dates)?
Just in case I provide link to google spreadsheet with provided data below https://docs.google.com/spreadsheets/d/1r-zM34RKrdEi0Fc4r4B0jyFSPymWDNPvJ9k6o8BkMwo/edit?usp=sharing

Thank you for any help in this challenging task for me

Primary data
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Revised price for Product #1 , $[/TD]
[/TR]
[TR]
[TD]1/1/2019[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]2/1/2019[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3/1/2019[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/1/2019[/TD]
[TD]210[/TD]
[/TR]
</tbody>[/TABLE]

Calculated result
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Current price for Product #1 , $[/TD]
[/TR]
[TR]
[TD]1/15/2019[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]2/15/2019[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3/15/2019[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]4/15/2019[/TD]
[TD]210[/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Are there any ways to do it with formula (ideally without array formula as there are huge range of products and dates)?

Hi, whilst not needing entering with CSE, this is in fact an array processing formula, so input ranges should be limited as much as possible. This also assumes that you primary data is sorted by the date column.

Excel 2013/2016
	B	C
2	Primary data
3	Date (mm/dd/yyyy)	Revised price for Product [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , $
4	01/01/2019	180
5	02/01/2019	200
6	03/01/2019
7	04/01/2019	210
8
9	Calculated result
10	Date (mm/dd/yyyy)	Current price for Product [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , $
11	01/15/2019	180
12	02/15/2019	200
13	03/15/2019	200
14	04/15/2019	210
Sheet1

Cell Formulas
Range	Formula
C11	=LOOKUP(B11,$B$4:$B$7/($C$4:$C$7<>""),$C$4:$C$7)

 

[Rostyslav]

Thank you!