Index Match - Multiple Criteria with a date range

[Shayhoe]

Hi All,

Hopefully the title is quite descriptive here, I'm essentially trying to do as I describe; I'm trying to do an Index Match with a few criteria, one of which is a date.

Here's an example below:

The formula will sit in an excel sheet which will be completed with the following information:

Brand - Brand A or Brand B
Product - Product A, Product B or Product C
Sale Date - Date in DD/MM/YYYY format

Using this information, I need to be able to return the document code that was valid during the specific product was sold. Every product for every brand has it's own document in roughly 1 month increments (Not always exactly a month, some run for a month and a bit or less than a month) there is only ever one document in place for each combination of brand/product/date at any one time.

My formula is it stands works but only if it's an exact date match - the formula is:


{=INDEX(M:M,MATCH(D3&E3&F3,J:J&K:K&L:L,0))}

Excuse the column references, these are aligned to my sheet but I don't have column headings on the data in thread(at the bottom of the question)

So in summary, is there a way to interrogate the below data with the data above and return a document code for the date range?

Hopefully this makes sense.

Thanks
[TABLE="width: 397"]
<tbody>[TR]
[TD]Brand[/TD]
[TD]Product[/TD]
[TD]Document used from[/TD]
[TD]Document Code[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD]Product A[/TD]
[TD]01/01/1999[/TD]
[TD="align: right"]12345[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD]Product B[/TD]
[TD]01/01/1999[/TD]
[TD="align: right"]12346[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD]Product C[/TD]
[TD]01/01/1999[/TD]
[TD="align: right"]12347[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD]Product A[/TD]
[TD]01/02/1999[/TD]
[TD="align: right"]12348[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD]Product B[/TD]
[TD]01/02/1999[/TD]
[TD="align: right"]12349[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD]Product C[/TD]
[TD]01/02/1999[/TD]
[TD="align: right"]12350[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD]Product A[/TD]
[TD]01/03/1999[/TD]
[TD="align: right"]12351[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD]Product B[/TD]
[TD]01/03/1999[/TD]
[TD="align: right"]12352[/TD]
[/TR]
[TR]
[TD]Brand A[/TD]
[TD]Product C[/TD]
[TD]01/03/1999[/TD]
[TD="align: right"]12353[/TD]
[/TR]
[TR]
[TD]Brand B[/TD]
[TD]Product A[/TD]
[TD]01/04/1999[/TD]
[TD="align: right"]12354[/TD]
[/TR]
[TR]
[TD]Brand B[/TD]
[TD]Product B[/TD]
[TD]01/04/1999[/TD]
[TD="align: right"]12355[/TD]
[/TR]
[TR]
[TD]Brand B[/TD]
[TD]Product C[/TD]
[TD]01/04/1999[/TD]
[TD="align: right"]12356[/TD]
[/TR]
[TR]
[TD]Brand B[/TD]
[TD]Product A[/TD]
[TD]01/05/1999[/TD]
[TD="align: right"]12357[/TD]
[/TR]
[TR]
[TD]Brand B[/TD]
[TD]Product B[/TD]
[TD]01/05/1999[/TD]
[TD="align: right"]12358[/TD]
[/TR]
[TR]
[TD]Brand B[/TD]
[TD]Product C[/TD]
[TD]01/05/1999[/TD]
[TD="align: right"]12359[/TD]
[/TR]
[TR]
[TD]Brand B[/TD]
[TD]Product A[/TD]
[TD]01/06/1999[/TD]
[TD="align: right"]12360[/TD]
[/TR]
[TR]
[TD]Brand B[/TD]
[TD]Product B[/TD]
[TD]01/06/1999[/TD]
[TD="align: right"]12361[/TD]
[/TR]
[TR]
[TD]Brand B[/TD]
[TD]Product C[/TD]
[TD]01/06/1999[/TD]
[TD="align: right"]12362[/TD]
[/TR]
</tbody><colgroup><col span="2"><col><col></colgroup>[/TABLE]

 

[wideboydixon]

If the dates are guaranteed ascending for a Brand/Product combination then:

Book1
	D	E	F	G	H	I	J	K	L	M
1							Brand	Product	Document used from	Document Code
2							Brand A	Product A	01/01/1999	12345
3	Brand A	Product A	15/02/1999	12348			Brand A	Product B	01/01/1999	12346
4							Brand A	Product C	01/01/1999	12347
5							Brand A	Product A	01/02/1999	12348
6							Brand A	Product B	01/02/1999	12349
7							Brand A	Product C	01/02/1999	12350
8							Brand A	Product A	01/03/1999	12351
9							Brand A	Product B	01/03/1999	12352
10							Brand A	Product C	01/03/1999	12353
11							Brand B	Product A	01/04/1999	12354
12							Brand B	Product B	01/04/1999	12355
13							Brand B	Product C	01/04/1999	12356
14							Brand B	Product A	01/05/1999	12357
15							Brand B	Product B	01/05/1999	12358
16							Brand B	Product C	01/05/1999	12359
17							Brand B	Product A	01/06/1999	12360
18							Brand B	Product B	01/06/1999	12361
19							Brand B	Product C	01/06/1999	12362
Sheet1

Cell Formulas
Range	Formula
G3	{=INDEX($M:$M,MAX(IF($J:$J=$D$3,IF($K:$K=$E$3,IF($L:$L<$F$3,ROW($M:$M))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Shayhoe]

Hi,

Thanks for the quick reply. Can I just confirm my understanding that the formula you provided will only work if the data is sorted in ascending date order? Does the order of the other columns make any difference?

Thanks

 

[wideboydixon]

The dates only need to be in order for each Brand/Product combination.

WBD

 

[Shayhoe]

That's spot on, thanks for the help - one final thing (Columbo style) - is there a way to return a sort of Iferror with an array like this? so if a product/date/brand doesn't exist it returns a message or N/A etc?

 

[wideboydixon]

Sure. Just wrap an IFERROR() around it:

{=IFERROR(INDEX($M:$M,MAX(IF($J:$J=$D$3,IF($K:$K=$E$3,IF($L:$L<$F$3,ROW($M:$M)))))),"Not Found")}

WBD