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".


[TABLE="class: grid, width: 167, align: left"]
<tbody>[TR]
[TD="colspan: 3, align: center"]Data 1[/TD]
[/TR]
[TR]
[TD]Sale Date[/TD]
[TD]SKU[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD="align: right"]13-Aug-15[/TD]
[TD]A[/TD]
[TD="align: right"]600.00[/TD]
[/TR]
[TR]
[TD="align: right"]15-Aug-15[/TD]
[TD]A[/TD]
[TD="align: right"]625.00[/TD]
[/TR]
[TR]
[TD="align: right"]19-Aug-15[/TD]
[TD]A[/TD]
[TD="align: right"]645.00[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











[TABLE="class: grid, width: 282"]
<tbody>[TR]
[TD="colspan: 4, align: center"]Data 2[/TD]
[/TR]
[TR]
[TD]SKU[/TD]
[TD]Discount To be Given[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]25[/TD]
[TD="align: right"]08/08/2015[/TD]
[TD="align: right"]09/08/2015[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]95[/TD]
[TD="align: right"]10/08/2015[/TD]
[TD="align: right"]10/08/2015[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]95[/TD]
[TD="align: right"]11/08/2015[/TD]
[TD="align: right"]11/08/2015[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]75[/TD]
[TD="align: right"]12/08/2015[/TD]
[TD="align: right"]14/08/2015[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.