Returning value from a previous non-empty cell of the table's column to another table using first table's first column and header as criteria

[Mariami]

Dear friends,

Good afternoon!

I tried both English- and Russian-speaking Google, but couldn't find ANYTHING! So, basically I have two Excel sheets - on the 1st one, I have a table containing prices for the futures at a date (column A) for a particular futures code (row 1). Please, see the table below:

Help Needed.xlsx
	A	B	C	D
1		PA21	PB21	PC21
2	1/1/21		30
3	1/2/21	10		25
4	1/3/21	18
5	1/4/21		35
6	1/5/21	15	36	20
Sheet1

On the 2nd sheet, I have several entities, and each of them comes with a certain date and futures code - see the table below:

Help Needed.xlsx
	A	B	C	D	E
1	Num	Entity Name	Date	Code	Price
2	1	Bear	1/2/21	PA21
3	2	Two Goggles	1/3/21	PB21
4	3	TK	1/3/21	PC21
Sheet2

What I need to do is return prices from the 1st sheet for each entity on the 2nd sheet taking into account the entity-relevant date and futures code. And that's an easy task - I just use the INDEX and MATCH functions (please, see the formula in column E below):

Help Needed.xlsx
	A	B	C	D	E
1	Num	Entity Name	Date	Code	Price
2	1	Bear	1/2/21	PA21	10
3	2	Two Goggles	1/3/21	PB21	#N/A
4	3	TK	1/3/21	PC21	#N/A
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=INDEX(Sheet1!$B$2:$D$6,MATCH(Sheet3!C2,Sheet1!$A$2:$A$6,0),MATCH(Sheet3!D2,Sheet1!B1:D1,0))

As you can see, the formula returns a price only for Bear - it happens because there are no prices for 03/01/2021 & PB21 and 03/01/2021 & PC21 in the table on Sheet1. What I need to happen though is that in case there's an empty cell for a certain date and code on Sheet1, the price from the previous non-empty cell gets returned to column E on Sheet2. In other words, if there's no price for this particular date and code, the price for the earlier and most recent date at which there was a price for this very code should get returned. Hence, the final table should look like this:

Help Needed.xlsx
	A	B	C	D	E
1	Num	Entity Name	Date	Code	Price
2	1	Bear	1/2/21	PA21	10
3	2	Two Goggles	1/3/21	PB21	30
4	3	TK	1/3/21	PC21	25
Sheet2__

(If I didn't explain it well enough before, let me put it differently - there's no price for 03/01/2021 & PB21 on Sheet1, there's also no price for 02/01/2021 & PB21, however, there's a price for 01/01/2021 & PB21, and that's the price that should return for Two Goggles; as for TK, there's no price for 03/01/2021 & PC21, but there is one for 02/01/2021 & PC21, and that's the price that should return for TK.)

Hope I explained my issue well enough. Would highly appreciate getting any help, and lots of thanks in advance!

P.S. I hope on a solution being an Excel formula, but if it can be done with VBA only, that also works, of course.

P.P.S. The very last range in my formula should also include dollars, of course (it should be $B$1:$D$1), but that's not the problem - #N/As or 0s are still returned because of the problem which I described and which requires resolution.

Best,
Mari