Sumproduct//index match to return text

[Alex_H]

Hi guys,

I'm trying to find an index/match/sumproduct formula with multiple criteria within a list, but the kicker is, it should also look for a date between a date range which is in 2 different columns and the formula should return a text and/or number from the dataset if it matches exactly with the criteria & the date is within a certain range.

Please check out the attached file; which looks like this

FL EQ SALES DOC BEGINDATE ENDDATE CONTRACT
5501014 Instr Instr 1 450000153 1/01/2016 31/12/2016 POR1
5501015 Instr Instr 1 450000154 1/01/2016 31/12/2016 FULL1
5501014 Instr Instr 1 450000153 1/01/2017 31/12/2017 7
5501015 Instr Instr 1 450000154 1/01/2017 31/12/2017 FULL3
5501014 Instr Instr 2 450000153 1/01/2016 31/12/2016 FULL4
5501015 Instr Instr 2 450000154 1/01/2016 31/12/2016 POR5
5501014 Instr Instr 2 450000153 1/01/2017 31/12/2017 FULL6
5501015 Instr Instr 2 450000154 1/01/2017 31/12/2017 POR7


5501015 Instr 1 10/04/2017 FULL1


How to check if these 3 parameters (550105, Instr 1 and on the date of 10/04/2017) there
was a valid contract in the system ? Then return the contract tekst back

Formula should also work for when the formula must return a value
In this case, the requested formula should return FULL3 as answer because the date of 10/04/2017 falls within the range between 01/01/2017 and 31/12/2017
but if the date changes to 15/07/2016, the formula should return FULL1 as answer
and if the FL changes to 5501014, the answer should be 7

 

[Alex_H]

I found the answer, assuming data set starts in A1 and the 3 criteria are C13:E13 then answer is array =INDEX($F$1:$F$8;MIN(IF(($E$13>=$D$1:$D$8)*($E$13<=$E$1:$E$8)*($D$13=$B$1:$B$8)*($C$13=$A$1:$A$8);MATCH(ROW($F$1:$F$8);ROW($F$1:$F$8)))))

 

[Haz]

=LOOKUP(1, 1/((E15>=BEGINDATE)*(E15<=ENDDATE)*(B15=FL)*(C15=EQ)), CONTRACT)

 

[Peter Bartholomew]

The key to the formula is the definition of the named formula 'selected.record' which returns the record number for any match and FALSE otherwise.

= IF( (FL=selected.FL) * (EQ=selected.EQ) * (BEGINDATE<=selected.date) * (ENDDATE>=selected.date), recordNumber )

LOOKUP then uses the array to return the contract detail.

 

[Peter Bartholomew]

Modified to use the pre-defined names. I was in two minds whether to use 1 for the flag to indicate the matched records (like Haz) or to use the record number with FALSE as the failed match.