HELP: Return a value with a date that falls between two other dates

[hionman]

Hi all,

I have been trying to find a resolution to this without getting any where here is my problem.

On Sheet 1:



Column No and Name: A(Job Number) B (Property Code) C (Where I want the Customer Number to be) D (Job Order Date)

On Sheet 2:

Column No. and Name: A (Property Code) B (Tenant Number) C (Customer Start Date) D (Customer Termination Date)


a. I would like column C in Sheet 1 to return the tenant number in (sheet 2, column e) who was the tenant of the property at the time the job number and job order date was carried out.
b. If there is no property number – for it to return no tenant number
c. If a job order was made when there was no tenant in the property (the gap between the termination date and start of another tenants start date) to show no tenant number.
_______________________

As there are a lot of tenants who lived in the same property, we can't do a straight vlookup with property code and tenant number as this usually brings the most recent tenant who is currently living in the property.

Instead since there were alot of tenants living in the same house at various times, we want to know who the correct tenant was at the time when the job order date was made .

_________
The formula I have used is the following:
=IFERROR(INDEX(Details!$B$2:$B$25,IF(SUMPRODUCT(--(Details!$A$2:$A$25=B3),--(Details!$C$2:$C$25<=D3),--(IF(Details!$D$2:$D$25<>"",Details!$D$2:$D$25,TODA Y())>=D3),ROW(Details!$A$2:$A$25)-ROW(Details!$A$2)+1)=0,"",SUMPRODUCT(--(Details!$A$2:$A$25=B3),--(Details!$C$2:$C$25<=D3),--(IF(Details!$D$2:$D$25<>"",Details!$D$2:$D$25,TODA Y())>=D3),ROW(Details!$A$2:$A$25)-ROW(Details!$A$2)+1))),"NO TENANT PRESENT")
This formula is working fine in the example worksheet where this formula was tested. Please see zip file attached.

_____________________________

But when I transfer this formula to my intended spreadsheet it doesn’t work, Instead, it just says NO TENANT PRESENT along the entire column, even though with the data that is there it should be pulling tenant numbers through.
Please see zip file attached for my spreadsheet where formula doesn’t work.
Have I got the formula wrong, do I need to make a slight amend to it, if so can you help or is it a formatting problem with the column.

I have pressed ctrl+shift+enter at the end of the formula for array and pressed enter as well and both give the same result.

Please help I have been working on this for days and I know I am nearly there.

Thanks everyone for taking their time reading this.