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