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

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: C(Job Number) D (Property Code) E (Where I want the Customer Number to be) V (Job Order Date)

On Sheet 2:

Column No. and Name: D (Property Code) E (Tenant Number) AA (Customer Start Date) AC (Customer Termination Date)


I want column E 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.

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 thisusually 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 .

I would really appreciate a forumla which would help me with this.

Thanks.

Put this formula in E2 and I think you should be good to go:

=INDEX(Sheet2!$E:$E,MATCH(1,IF(Sheet2!$D:$D=D2,IF(Sheet2!$AA:$AA<=V2,IF(Sheet2!$AC:$AC>=V2,1))),0))

It's an array formula, so you'll need to hit control+shift+enter after pasting it into E2, otherwise it'll give you a #N/A. But then you can just pull the formula down as many rows as you need and won't need to hit ctrl+shift+enter anymore unless you click on a cell with the formula in it. You could always hide the formula column and put a column reading from it in it's place, or something like that.

Hope this works!

Example.csv

Hi all,

Thanks for those formulas but they don't work as they don't return the tenant number to the property when the job order date was completed.

I have attached an example which hopefully make it clearer to you - on sheet 2 column D I would like the tenant number to be returned there - this tenant number will show the correct tenant when the job date was ordered for the specific proerpty code.

Also in customer termination date - is there anyother way around pulling the correct tenant number through without putting TODAY in the formula, as I need to have the correct termination date even if there isnt one as I use this data in a pivot table - I have TODAYs date for a termination date and there is no termination date it puts everything else out of sorts. Have you got a solution where the formula still shows the tenant number even if there isnt a termination date and that field is blank for those tenants that haven't terminated/ left their property yet.

Thanks for your help all.

Hi ChemistB,

This is brilliant. There is only one problem with your second formula you have used in the example. When there is no date in the 'Tenant End Date' column (column d on work sheet 2 - example), it brings through the wrong tenant number.

Is there away of not using 'TODAY's date' as a formula, to still pick up the correct Tenant Number in the excel file even if there isn't a date with in the 'Tenant End Date' column.

If not could you please add the 'TODAYS date to the formula as it is so complicated (but yet still brilliant) I wouldn't know where to put it.

Thank you so much ChemistB.

Hi ChemistB,

I must have dragged the formula down wrong but it was really strange - when the termination date was there it worked when it wasn't it didnt work.

Once I put the formula in my real spreadsheet Ill let you know how I got on but until Chemist B thank you so much enough respect and reputation to you my friend.

Hi ChemistB,

Example 2.xlsx

I have put the formula in my spreadsheet a couple of problems though, which is causing the incorrect tenant number to be selected.

I think one of the issues is due to the following:

a. When a job date is carried out of tenants start and end date it picks a random tenant number. I have enclosed the excel file as an example. For example:



For example Job No.5 which took place on 25/09/2014 for property code 1000035. There was no tenant at that time in the property but it returns tenant no. 19. when it should return a blank or "No Tenant Present"

I think thats one reason why its not selecting the right tenant.

I am pressing ctrl shift and enter once I enter the formula so Im trying to think of any other reasons why the tenant number might be off.

Thanks again ChemistB and everyone who has contributed.

Example 3 - My Spreadsheet.xlsb

Hi ChemistB and Rajeshturaha, and everybody else,
Really appreciate both your help and everybody elses.
I have put both ChemistBs formula in as well as Rajeshturaha and is still isn’t working.

I have attached my real spreadsheet to demonstrate whats failing.
Before I go through the problems the only things I have changed in the formula are the columns it corresponds to and increased the number of cells in the column from 2500 to 10000 as there is a lot of data I need to analyse:
a. When I have customised the formula to my spreadsheet, enter it into the cell where I want it and then press ctrl +shift +enter it gives me the same one tenant number or ‘No Tenant Present’ (if I use Rajesh’s formula) across the entire column. When I don’t press ctrl+shift+enter and just press enter it shows me different tenant numbers against each property code but they are all wrong and do not correctly correspond to the property code.
b. If there is no property code, the tenant number cell should then be empty but instead it provides a tenant number which is incorrect. Instead it should show a blank in the tenant number column when there is no property code in the same row.
c. Like I mentioned before, when a tenant has moved and a new tenant moves in there is usually a gap; if a repair is carried out in that period where there is no tenant it is still returning a tenant number instead of showing a blank or saying “No tenant” is there away to amend the formula to do this.

Could you look at this spreadsheet and see if there are any problems it which is why the formula isn’t working correctly and input the working formula in my spreadsheet, because for the life of me Ive spent several hours on it and the issues still remain the same.

I really appreciate this all.

My Spreadsheets.zip

Hi ChemistB,

Here is the zip file which has one excel sheet - the example sheet which you created, which has the updated formula which puts a 'No tenant' when there is no property number or a tenant doesnt live in the property. This formula works in the excel sheet but it does not work in my excel sheet which I also attached.

Could you help me understand why the formula doesnt work in my spreadsheet even though I have customised it to my column cells and sheet names, etc, is it formatting of cells or something else because even when I press ctrl+shift+Enter or just press enter it just gives me the wrong answer in my own spreadsheet.

Thanks.