Values corresponding to the closest date match

[Suza2007]

Hi All,

I have the following raw data:

[TABLE="width: 479"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD][/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Material
[/TD]
[TD]Bill Date
[/TD]
[TD]Unit Price
[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]14/05/2018
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]03/05/2018
[/TD]
[TD]323
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]10/05/2018
[/TD]
[TD]300
[/TD]
[TD][/TD]
[TD]Material
[/TD]
[TD]Unit Price
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]28/05/2018
[/TD]
[TD]300
[/TD]
[TD][/TD]
[TD]A
[/TD]
[TD]323
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]02/06/2018
[/TD]
[TD]278
[/TD]
[TD][/TD]
[TD]C
[/TD]
[TD]1050
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]16/06/2018
[/TD]
[TD]210
[/TD]
[TD][/TD]
[TD]R
[/TD]
[TD]500
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]21/12/2017
[/TD]
[TD]1050
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]22/12/2017
[/TD]
[TD]1050
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]27/12/2017
[/TD]
[TD]1050
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]29/01/2018
[/TD]
[TD]900
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]29/01/2018
[/TD]
[TD]900
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]29/03/2018
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]16/04/2018
[/TD]
[TD]490
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]19/04/2018
[/TD]
[TD]490
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]22/05/2018
[/TD]
[TD]470
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]25/05/2018
[/TD]
[TD]470
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]15/06/2018
[/TD]
[TD]450
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]20/06/2018
[/TD]
[TD]450
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R
[/TD]
[TD]21/06/2018
[/TD]
[TD]450
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Am trying to create a report as on 14^th May 2018 where I need unit price corresponding to the nearest date.
Hence for “A” I must get unit price as 300 & “C” as 900 as that is last latest date (29/01/2018) & “R” as 490.

Am using “=IFERROR(VLOOKUP(F5,B2:D20,3,0), INDEX(C3:C20,MATCH(G2,D3:D20,-1)+1))”, however it gives me the very first value(unit price) for each material.

Any help is much appreciated.

Thanks

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G
1	Material	Bill Date	Unit Price			5/14/2018
2	A	5/3/2018	323
3	A	5/10/2018	300		Material	Unit Price
4	A	5/28/2018	300		A	300
5	A	6/2/2018	278		C	900
6	A	6/16/2018	210		R	490
7	C	12/21/2017	1050
8	C	12/22/2017	1050
9	C	12/27/2017	1050
10	C	1/29/2018	900
11	C	1/29/2018	900
12	R	3/29/2018	500
13	R	4/16/2018	490
14	R	4/19/2018	490
15	R	5/22/2018	470
16	R	5/25/2018	470
17	R	6/15/2018	450
18	R	6/20/2018	450
19	R	6/21/2018	450
Sheet13

Cell Formulas
Range	Formula
F4	{=VLOOKUP($F$1,IF($A$2:$A$19=E4,$B$2:$C$19),2,1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[kvsrinivasamurthy]

Post deleted.

 

[Suza2007]

Amazing. Thank you so much for the help.

just one quick question, after entering the formula in cell "F4" when i double clicked it was giving me #N/A error.

 

[sheetspread]

The control and shift keys must be pressed along with enter (every time)

 

[Suza2007]

Thanks Much

 

[Suza2007]

This formula isnt working for me on F5 & F6 cells though i tried entering it manually instead of copy paste. please help

 

[sheetspread]

What I said about holding down ctrl-shift with the enter doesn't apply when you drag the formula or copy and paste, only if you actually enter it (either for the first time or after double clicking/pressing F2 etc)