Multiple vlook up

[JOHN007]

I need to lookup for nearest max data and nearest min date and return apprioprate code (code on nearest min date and code nearest max date date) based on two parameters
can anyone help me out?

based on Sheet 2 I need to return CODE in Sheet 1

Sheet 1
	A	B	C	D	EXAMPLE DATE	EXAMPLE CODE
1	Name	DATE	Code on nearest Min date	Code on nearest Max date	expected code on min date to be returned	expected code max date to be returned	expected code on min date to be returned	expected code max date to be returned
2	Apple	01-01-2018	?	?	02-01-2018	NA	123	NA
3	Apple	03-01-2018	?	?	03-01-2018	03-01-2018	56	56
4	Orange	08-01-2018	?	?	NA	09-01-2018	NA	2365
5	banana	11-01-2018	?	?	10-01-2018	12-01-2018	3241	236

<tbody>
</tbody>

Sheet 2
	A	B	c
	Name	Date	CODE
1	Apple	02-01-2018	123
2	Apple	03-01-2018	56
3	Orange	07-01-2018	987
4	Orange	09-01-2018	2365
5	Orange	10-01-2018	698
6	Banana	10-01-2018	1478
7	Banana	12-01-2018	3241
8	Banana	13-01-2018	236

<tbody>
</tbody>

 

[Legacy 421546]

Date is Double. Subtract two date fields in order to find min/max