Obtaining Price based on Date

[Marhier]

Good afternoon everyone, I hope you're well.
I've got a problem I'm hoping someone can help me solve; I'll try my best to explain what I'm trying to do.

I'm trying to write a formula that looks at cell $D3, finds it from table reference Data2[StockCode] (on a different worksheet), then looks at cell G$2, finds that from table reference Data2[PriceStartDate] and returns the price from table reference Data2[PurchasePrice].

Where I want my results looks as follows:

This would be a lot simpler if the 'Data2' table had each month's price in, but all I have is when the price starts, so as an example:

So I need the formula to look cell reference G$2, then look at Data2[PriceStartDate] and figure out what the price should be for the month in G$2.

The final result should look as follows:

Hopefully I've explained that well.
If someone could come up with a formula for it, it would be greatly appreciated.

I should note that I've added $'s to my references as I want to be able to drag the formula up and down to cover my whole range.
I would also like the table references to be absolute references as well.

All help is greatly appreciated.
Thank you.
Regards,
Marhier.

 

[Fluff]

How about

=INDEX(FILTER(Data2[PurchasePrice],(Data2[StockCode]=d3)*(Data2[PriceStartDate]<=g2)),1)

 

[Marhier]

Thanks for the fast reply, Fluff.
This returns a #CALC!

 

[Fluff]

That suggests that nothing matches both criteria.
Check that the dates in the Data2[PriceStartDate] column are real dates & not numbers. They look as though they could be text.

 

[Marhier]

Hi Fluff.
I've checked, and all the cells in column Data2[PriceStartDate] are formatted as dates.

 

[Fluff]

If you format the entire column as General, do you still see dates?

 

[Marhier]

No, when changing to general, they turn to numbers.

 

[Fluff]

Ok, That's fine. Check that you don't have any leading/trailing spaces in the stock code cells.

 

[Marhier]

Hi Fluff.

Spot on!
I did a find and replace on the stock code column and there was a space at the end of each stock code.

I can confirm the formula you suggested works a treat!
I really can't thank you enough for this; you've helped me a few times in the past and I really appreciate it.

Thank you.
Regards,
Marhier.

 

[Fluff]

Glad to help & thanks for the feedback.