Values corresponding to the closest date match

[Suza2007]

Hi All,

I have the following raw data:

A	B	C		F	G
Material	Bill Date	Unit Price			14/05/2018
A	03/05/2018	323
A	10/05/2018	300		Material	Unit Price
A	28/05/2018	300		A	323
A	02/06/2018	278		C	1050
A	16/06/2018	210		R	500
C	21/12/2017	1050
C	22/12/2017	1050
C	27/12/2017	1050
C	29/01/2018	900
C	29/01/2018	900
R	29/03/2018	500
R	16/04/2018	490
R	19/04/2018	490
R	22/05/2018	470
R	25/05/2018	470
R	15/06/2018	450
R	20/06/2018	450
R	21/06/2018	450

<tbody>
</tbody>

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)