vlookup with 2 criteria and date range

[Blabla2a]

Hey everyone,

I'm new here and I have a question.
I have 2 tables in excel

1. sales table
2. promotion table

in the sales table I have : Serial number , date , price , discount
in the promotion table I have: Serial number , start date, end date, discount

now I want to check if the discount in the sales table is like in promotion table!
remember you can find the same serial number in the promotion/sales date but in different dates.

I have to check if the
date(sales table) is between start date(promotion table) and end date(promotion table)
AND
serial number = serial number

if yes I want to put the discount(promotion table) in the sales table

I hope you understand me!

I try to do it in excel but it's stuck with more 750k lines

do you have any sql or via solution?

 

[CyrusTheVirus]

Could you post a sample of your work showing what you're trying to achieve?

 

[Blabla2a]

Could you post a sample of your work showing what you're trying to achieve?

 

[CyrusTheVirus]

Try the below formula (you can copy/paste the below data into excel).

Please keep in mind that the parameters are greater than or equal to and less than or equal to. Therefore if a start date is 1/1/2009 from the promotional table, and the date from the sales table is 1/1/2009 then it will populate percentages, same goes for end dates.

E2:

=IFERROR(IF(AND($C2>=VLOOKUP($A2,$G$2:$H$5,2,0),$C2<=VLOOKUP($A2,$G$2:$I$5,3,0)),VLOOKUP($A2,$G$2:$J$5,4,0),"0 / None"),"S/N Not Found")

S/N	PRICE	DATE	DISCOUNT	What I need		S/N	START DATE	END DATE	DISCOUNT
423234	422	1/1/2010	40.00%	30.00%		423234	1/1/2009	1/2/2010	30.00%
123123	147	5/23/2012	20.00%	0 / None		123123	4/2/2012	5/22/2012	20.00%
124124	547	7/15/2012	70.00%	0 / None		124124	10/1/2008	6/5/2009	10.00%
89345	120	6/5/2010	20.00%	20.00%		89345	1/1/2010	7/1/2010	20.00%

<tbody>
</tbody>

 

[Blabla2a]

Try the below formula (you can copy/paste the below data into excel):

E2:

=IFERROR(IF(AND($C2>=VLOOKUP($A2,$G$2:$H$5,2,0),$C2<=VLOOKUP($A2,$G$2:$I$5,3,0)),VLOOKUP($A2,$G$2:$J$5,4,0),"0 / None"),"S/N Not Found")

S/N	PRICE	DATE	DISCOUNT	What I need		S/N	START DATE	END DATE	DISCOUNT
423234	422	1/1/2010	40.00%	30.00%		423234	1/1/2009	1/2/2010	30.00%
123123	147	5/23/2012	20.00%	0 / None		123123	4/2/2012	5/22/2012	20.00%
124124	547	7/15/2012	70.00%	0 / None		124124	10/1/2008	6/5/2009	10.00%
89345	120	6/5/2010	20.00%	20.00%		89345	1/1/2010	7/1/2010	20.00%

<tbody>
</tbody>

hey,
I have 750,000 lines in sales table and 7500 lines in promotion.
Its too much for excel , what can I do?

 

[CyrusTheVirus]

If the below doesn't work, then I'm not sure. Maybe try Power Query, Access, or VBA?

Try bringing over the dates to the SALES TABLE (by the E2 formula below) and see if that helps with calculation times.

E2 (copied over to F2 and down):

=IFERROR(INDEX(J$2:J$5,MATCH($A2,$I$2:$I$5,0)),"S/N Not Found")

G2:

=IF(AND($C2>=$E2,$C2<=$F2),INDEX($L$2:$L$5,MATCH($A2,$I$2:$I$5,0)),"0 / None")

S/N	PRICE	DATE	DISCOUNT	Start Date	End Date	What I need		S/N	START DATE	END DATE	DISCOUNT
423234	422	1/1/2010	40.00%	1/1/2009	1/2/2010	30.00%		423234	1/1/2009	1/2/2010	30.00%
123123	147	5/23/2012	20.00%	4/2/2012	5/22/2012	0 / None		123123	4/2/2012	5/22/2012	20.00%
124124	547	7/15/2012	70.00%	10/1/2008	6/5/2009	0 / None		124124	10/1/2008	6/5/2009	10.00%
89345	120	6/5/2010	20.00%	1/1/2010	7/1/2010	20.00%		89345	1/1/2010	7/1/2010	20.00%

<tbody>
</tbody>

 

[Blabla2a]

its not working well with 7500 promotion lines.
I put this code:

=IFERROR(IF(AND($C2>=VLOOKUP($A2,$G$2:$H$7371,2,0),$C2<=VLOOKUP($A2,$G$2:$I$7371,3,0)),VLOOKUP($A2,$G$2:$J$7371,4,0),"None"),"S/N Not Found")

 

[CyrusTheVirus]

Hm, not sure what to tell you, sorry. Perhaps some other users could suggest a solution. :-\

You might need Power Query, Access, or <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); font-family: Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif; background-color: rgb(250, 250, 250);">VBA...</acronym>

 

[misterxo]

Hi, this is solved with a nested IF.
Assuming your sale table is in the array A1 to D5 (S/N, PRICE, DATE, DISCOUNT columns) and your promotion table in array G1:J6 (S/N, STARTDATE, END DATE, DISCOUNT this is the formula you want to apply:
=IF(C2>=H2,IF(C2<=I2,IF(A2=G2,D2,"None"),"None"),"None")
This will return the DISCOUNT value only if the date in sales table is within the range start/end date in promotion table AND if S/N is the same. Else it will return NONE