Matching Product Costs Using Product ID and Dates (multiple ID with varied Dates)

[k______S]

Hello,

I have a file A with product ID's and the date they were sold. I have another file B with the historical cost of these products and the dates the cost was changed/updated.

I need to find the pricing for the products ID's in file A on the date they were sold (also in File A). Is there a way to use the data from File B (the dates will not match up exactly) to find the historical pricing for File A?

EX:

File A has a product ID of 1234 and was sold on 10/01/2018
File B has product ID 1234 on two lines. Line 1 has a price of $4.25 and a date of 04/01/2018 and line 2 has a price of $4.50 with a date of 12/31/2018.

This means that the price of product 1234 was $4.25 from 04/01/2018 through 12/30/2018. On 12/31/2018 the price changed to $4.50.

How can I use these two files to match the date on File A to the time range on File B and return the correct price?

Please let me know if you need additional information

 

[Aladin Akyurek]

What is the answer that you have for your example? That would help a helper...

 

[k______S]

What is the answer that you have for your example? That would help a helper...

The correct answer should be $4.25 since the example of 10/01/2018 falls after the original price of $4.25 but before the updated price of $4.50.

Thanks!

 

[Aladin Akyurek]

Book1
	A	B	C
1	Product ID	Sold	Price
2	1234	4/1/2018	4.25
3	1234	12/31/2018	4.5
4
5
6
7
8
9
10
Sheet1

The dates are assumed to be chronological in FileB.

Book1
	A	B	C
1	Product ID	Sold	Price
2	1234	10/1/2018	4.25
3
4
Sheet1

In C2 of Sheet1 in File A, control+shift+enter, not just enter:

=LOOKUP(B2,IF([FileB.xlsx]Sheet1!$A$2:$A$10=$A2,[FileB.xlsx]Sheet1!$B$2:$B$10),[FileB.xlsx]Sheet1!$C$2:$C$10)