Hi Joris ,
The formula is :
=INDEX(Tabel1[Reference],MATCH(1,(Tabel1[Begin]<=B14)*(B14<=Tabel1[End]),0))
If we start with the outermost function , we have :
=INDEX(Tabel1[Reference], somevalue)
Now , if somevalue has the value 1 , we have =INDEX(Tabel1[Reference],1)
If somevalue has the value 2 , we have =INDEX(Tabel1[Reference],2)
If somevalue has the value 3 , we have =INDEX(Tabel1[Reference],3)
If somevalue has the value 4 , we have =INDEX(Tabel1[Reference],4)
Thus depending on the value of somevalue , we will get Sprint 1 , Sprint 2 , Sprint 3 or Sprint 4.
All we need to do is understand how somevalue is derived.
This comes from the part :
MATCH(1,(Tabel1[Begin]<=B14)*(B14<=Tabel1[End]),0)
What this is doing is looking up a value of 1 in an array of values ; if this is found in the first position , the MATCH function will return 1 ; if this is found in the second position , the MATCH function will return 2 ; if this is found in the third position , the MATCH function will return 3 ; if this is found in the fourth position , the MATCH function will return 4.
So now let us look at what this array of values contains , and how is it derived.
The logic that has been implemented is :
Look at each of the values in the Begin column and see whether it is less than the value of the date in B14 ; this check will return an array of values which will consist of TRUE or FALSE values. Second , look at each of the values in the End column and see whether the date in B14 is less than the value in the End column ; this check will also return an array of values which will consist of TRUE or FALSE values.
Multiplying two arrays which consist of TRUE / FALSE values will result in a single array of 1s and 0s , which will follow the logic given below :
FALSE * FALSE = 0 * 0 = 0
FALSE * TRUE = 0 * 1 = 0
TRUE * FALSE = 1 * 0 = 0
TRUE * TRUE = 1 * 1 = 1
Thus , where the date in B14 is between a date in the Begin column and a date in the End column , we will have the last calculation of TRUE * TRUE , which will give us 1 in the array.
Thus the 4 possibilities in the workbook will be :
{1 ; 0 ; 0 ; 0} where the match is in the first row itself
{0 ; 1 ; 0 ; 0} where the match is in the second row
{0 ; 0 ; 1 ; 0} where the match is in the third row
{0 ; 0 ; 0 ; 1} where the match is in the fourth row
Of course , if there is more than one set of dates which matches , there can be more than one 1 in the array ; however , this does not matter , since you have said that you are interested only in the first match , which is what the MATCH function will return.
Narayan
