Index match with closest date

[Legacy 386498]

Hi all,

I want to create a formula that will search the amount of a product in a division for the closest date given.

So, for the 2 images link, if I want to know the amount for Cakes in division 161 the nearest to the December 2nd, it would be 3.50$.

https://imageshack.com/i/pmP1dYlLj

https://imageshack.com/i/pn6sqAaXj

I thought that index and match would be the best formula, but I'm open to any suggestion.

Thanks and have a nice day.

 

[AhoyNC]

This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Division	Prod	Amount	Date
2	166	Cakes	13	11/6/2017
3	161	Cakes	14.5	11/10/2017
4	161	Cakes	3.5	11/17/2017
5	165	Cakes	5	11/27/2017
6	164	Fruit	2	12/3/2017
7	164	Pan	4	10/30/2017
8	162	Pan	4	11/23/2017
9	161	Pan	4.6	10/30/2017
10
11
12	Find	Div	161
13		Prod	Cakes
14		Date	12/2/2017
15
16		Amount	3.5
Sheet

 

[Legacy 386498]

Thanks for the quick reply AhoyNC!! It works perfectly.

 

[AhoyNC]

Your welcome. Thanks for the feedback.