index and match for the date range

[mhghg]

Hi guys,
I am having the problem to create the index/match formula for my named range
My named range tblData is exporting from sql server from A6: D120.
I have sheet set up like this
J K L
Term Start Date Finish Date
1 27/01/2016 24/03/2016
2 11/04/2016 24/06/2016
3 11/07/2016 16/09/2016
4 03/10/2016 22/12/2016



In my named range column 3 is the finish date I create the formula to find if the finish date is between the range from above then add the correct term number to the last column of my named range tblData, but i got the error #N/A
Please help me to correct where I am wrong in the formula

=INDEX(J$1:J$4,MATCH(1,IF(INDEX(tblData,1,3)>=K$1:K$4,IF(INDEX(tblData,1,3)<L$1:L$4,1)),0))

Thanks
Macy

 

[Nebu]

Hi:

Try the following formula.

=INDEX(J$1:J$4,MATCH(1,MMULT((INDEX(tblData,1,3)>=K$1:K$4)*(INDEX(tblData,1,3)<L$1:L$4),1),0))

If not working, upload a sample file

Thanks

 

[mhghg]

Hi Nebu,
I try your formula, but it shows error when comparing cell value with second data range, not sure why?


and here is the file

 

[Nebu]

Hi:

Find the attached. Your date fields were dot actually dates hence you go the error.

Thanks

 

[mhghg]

Hi Nebu,
Not sure what you mean can you elaborate it please. Where is the dot location?
Macy

 

[Nebu]

Sorry
Typo Its not Dot its *not

Thanks