LOOKUP IF value in range between two columns - return value in third column

[londonpublish]

Hello all,

First post on here so apologies if I miss any protocol. I'd greatly appreciate help with the following, which will save a lot manual checking!

So I have two data sets - an extract of the first as follows, demonstrating the periods for which products were promoted and the applicable discount in that promotional period. Note that any one product does not have overlapping promotional periods, although different product codes may have overlapping dates:

Set A:

Product Code	Promotion Start Date	Promotion End Date	Discount
101	01/01/2012	31/03/2012	5%
101	01/05/2012	30/06/2012	20%
110	15/01/2012	31/03/2012	8%
110	10/05/2012	30/06/2012	10%
110	01/09/2012	31/10/2012	12%
150	01/01/2012	31/03/2012	11%
150	01/06/2012	30/06/2012	15%

<tbody>
</tbody>

I then have a listing of product codes and invoice dates, as follows:

Set B:

Product Code	Invoice Date
101	28/02/2012
101	25/03/2012
101	06/05/2012
110	25/01/2012
110	01/10/2012
150	10/01/2012

<tbody>
</tbody>

I would like to bring back the appropriate discount for the invoice date, so results as follows:

Set B:

Product Code	Invoice Date	Discount
101	28/02/2012	5%
101	25/03/2012	5%
101	06/05/2012	20%
110	25/01/2012	8%
110	01/10/2012	12%
150	10/01/2012	11%

<tbody>
</tbody>

I'm guessing that the first step to resolve this is to concatenate the codes with the VALUE of the DATES, so I have a range, as follows:

Set A:

Code-Start Date	Code-End Date	Discount
10140909	10140999	5%
10141030	10141090	20%
11040923	11040999	8%
11041039	11041090	10%
11041153	11041213	12%
15040909	15040999	11%
15041061	15041090	15%

<tbody>
</tbody>

And then do the same with my other set:

Set B:

Code-Inv Date	Invoice Date
10140967	28/02/2012
10140993	25/03/2012
10141035	06/05/2012
11040933	25/01/2012
11041183	01/10/2012
15040918	10/01/2012

<tbody>
</tbody>

But how do I now look back from Set B to Set A to say, IF CODE-INV DATE is greater than a value in CODE-START DATE column and less than a value in CODE-END DATE column, return the discount value against that range?

Many thanks in advance.

 

[Andrew Poulsom]

Welcome to MrExcel.

What version of Excel do you have? If it's Excel 2007 or above you can use a formula like this with your original data:

=IFERROR(INDEX(D$2:D$8,MATCH(1,INDEX((A$2:A$8=F2)*(B$2:B$8<=G2)*(C$2:C$8>=G2),),FALSE)),0)

Otherwise you can use:

=IF(ISERROR(INDEX(D$2:D$8,MATCH(1,INDEX((A$2:A$8=F2)*(B$2:B$8<=G2)*(C$2:C$8>=G2),),FALSE))),0,INDEX(D$2:D$8,MATCH(1,INDEX((A$2:A$8=F2)*(B$2:B$8<=G2)*(C$2:C$8>=G2),),FALSE)))

where Set A is in the range A1:D8 and Set B is in the range F1:G7.

 

[Gwinn]

What you need can be done using an array formula, such as this:

=MAX(IF((invoice data cell>=promotion start range)*(invoice date cell<=promotion end range);1;0)*discounts range)

The formula assumes that if there are 2 promotions at the same time, the better one is applied.

Make sure you enter the formula with ctr+shift+enter for it to work.

Hope this is clear enough and helps.

 

[caleb708]

Hi Andrew,

Could you explain why the MATCH argument has "1" in the "LookUp Value"?

Also, why is the nested INDEX function incomplete? There is a , before the bracket ends.

Regards,
Caleb

 

[Andrew Poulsom]

Welcome to MrExcel.

The INDEX function returns an array of 1's and 0's from the boolean multiplication in its first argument. The second argument is effectively zero. MATCH returns the position of the first 1 in the array.

 

[caleb708]

Ah okay - Thanks for clarifying. I figured the 1 and 0's but was unsure of what the second argument being incomplete meant.