Pull the price before a certain date

[asharma]

Hello all Excel Gurus,
I need help in pulling the price paid BEFORE a particular date. I am attaching 2 sample spreadsheets. One is the price sheet for a particular claim product ID and the date when the price was paid. The second sheet is the "Date of Service" sheet where I need to pull the price paid BEFORE the date of service but only pull the price that was right before the date of service. In the "Date of Service" sheet, I've manually entered the price paid right before the date of service 3/2/18 (3523.23). That was the purchase price paid on 2/26/18, right before 3/2/18. I have 40,000 rows that I need to do this for. Any help would be greatly appreciated.

Price Sheet.xlsx
	A	B	C
1	Claim Product ID	Date	Price
2	50242008701	02/06/2018	$3,523.23
3	50242008701	02/13/2018	$3,523.23
4	50242008701	02/13/2018	$3,523.23
5	50242008701	02/13/2018	$3,523.23
6	50242008701	02/21/2018	$3,523.23
7	50242008701	02/26/2018	$3,523.23
8	50242008701	03/06/2018	$3,523.23
9	50242008701	03/06/2018	$3,523.23
10	50242008701	03/27/2018	$3,523.23
11	50242008701	04/24/2018	$3,525.39
12	50242008701	05/16/2018	$3,525.39
13	50242008701	05/22/2018	$3,525.39
14	50242008701	06/04/2018	$3,525.39
15	50242008701	08/20/2018	$3,570.44
16	50242008701	08/27/2018	$3,570.44
Sheet3

Date of service.xlsx
	A	B	C
1	Clm Product ID	Date of Service	Price
2	50242008701	3/2/2018	$ 3,523.23
3	50242008701	3/2/2018
4	50242008801	3/2/2018
5	50242008701	3/23/2018
6	50242008701	3/23/2018
7	50242008801	3/23/2018
8	50242008801	5/24/2018
9	50242008801	5/24/2018
10	50242008801	5/24/2018
Sheet1

 

[jtakw]

Hi,

If your Dates in Sheet 3 are in Ascending order:

Book1
	A	B	C
1	Clm Product ID	Date of Service	Price
2	50242008701	3/2/2018	3523.23
3	50242008701	3/2/2018	3523.23
4	50242008801	3/2/2018	Not Found
5	50242008701	3/23/2018	3523.23
6	50242008701	8/25/2018	3570.44
7	50242008801	3/23/2018	Not Found
8	50242008801	5/24/2018	Not Found
9	50242008801	5/24/2018	Not Found
10	50242008801	5/24/2018	Not Found
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=IFERROR(LOOKUP(2,1/(FIND(A2,Sheet3!A$2:A$16)*(Sheet3!B$2:B$16<=B2)),Sheet3!C$2:C$16),"Not Found")

Book1
	A	B	C
1	Claim Product ID	Date	Price
2	50242008701	2/6/2018	3523.23
3	50242008701	2/13/2018	3523.23
4	50242008701	2/13/2018	3523.23
5	50242008701	2/13/2018	3523.23
6	50242008701	2/21/2018	3523.23
7	50242008701	2/26/2018	3523.23
8	50242008701	3/6/2018	3523.23
9	50242008701	3/6/2018	3523.23
10	50242008701	3/27/2018	3523.23
11	50242008701	4/24/2018	3525.39
12	50242008701	5/16/2018	3525.39
13	50242008701	5/22/2018	3525.39
14	50242008701	6/4/2018	3525.39
15	50242008701	8/20/2018	3570.44
16	50242008701	8/27/2018	3570.44
Sheet3

 

[asharma]

That worked! You're a genius my friend. Thank you so much!

 

[jtakw]

You're welcome, thanks for the feedback.

Don't forget to mark the post that solved your question as Solution, Thanks.

In case you have IDs that are Different in character length (i.e. 12345, 123456, 1234567, etc.), this modified version of my formula will be More reliable:

=IFERROR(LOOKUP(2,1/((Sheet3!A$2:A$16=A2)*(Sheet3!B$2:B$16<=B2)),Sheet3!C$2:C$16),"Not Found")

 

[asharma]

Got it! Thank you so much again!