Vlookup - Return value finding the value between the dates

[lionsclaw]

Hi,

There are 2 excel data.
The Data 1 excel has SKU code and has the sold amount.
Data 2 excel has sku code and discount to be given during that period.
So I want to search SKU and return the sku discount to be given from "Data 2" by looking the date of the data 1 between the discount period of start date and end date mentioned in "Data 2".

Data 1
Sale Date	SKU	Amount
13-Aug-15	A	600.00
15-Aug-15	A	625.00
19-Aug-15	A	645.00

<tbody>
</tbody>

Data 2
SKU	Discount To be Given	Start Date	End Date
A	25	08/08/2015	09/08/2015
A	95	10/08/2015	10/08/2015
A	95	11/08/2015	11/08/2015
A	75	12/08/2015	14/08/2015

<tbody>
</tbody>

 

[gaz_chops]

Welcome to the forum.

Try this entered as an array with ctrl, shift & enter

=INDEX($B$11:$B$14,MATCH(1,IF($A$11:$A$14=B4,IF($C$11:$C$14<=A4,IF($D$11:$D$14>=A4,1)))))

 

[lionsclaw]

Welcome to the forum.

Try this entered as an array with ctrl, shift & enter

=INDEX($B$11:$B$14,MATCH(1,IF($A$11:$A$14=B4,IF($C$11:$C$14<=A4,IF($D$11:$D$14>=A4,1)))))

Thank you so much for your help.
It was of great help.
Really appreciate it.

 

[gaz_chops]

You're welcome.