SOLVED

Choose one approximate value between two dates

Copper Contributor

Hello everyone!

I am working in a startup company within the real state industry. I would like to know if you could help me figure out a solution for this situation:

I have potential clients who will stay for a certain period of time (from.. to...)

I have landlords who offers their appartments also for a certain period of time

I need to do a match between the client and the landlord. If a client ask me to stay from 15/07/17 to 30/12/17 i need to find the landlord who will fit with the requirement. 

So, i have in the first column the names of the landlords, in the second one the dates "from" and in the third one "until/to" they rent theirs appartments:

NameFromTo
AUGUSTINE01/06/201731/12/2017
Miguai19/06/2017-
x28/06/201728/07/2017
Laurent Bernasconi28/06/201723/08/2017
Mme Dramé01/07/201730/09/2017

Then, I took two cells in order to type the desired dates "from" and "to" from a potential tenant:

From To
15/08/17 10/10/17

I would like that the name or names of landlords who matchs within these dates appears in other cell(s). 

Landlord(s)
 
 
 
 
 

I was trying with lookv, with index, with match... with arrays and i don't get to the answer. This is one of my formules:

=ArrayFormula(index(A2:D500;EQUIV(F2&H2;C2:C500&D2:D500;1);2))

(equiv=match in french)

Please could you be so good as to help me with this problem? Please, please!

thank you and good vibes!

k

 

Capture d’écran 2017-06-21 à 14.58.46.png

5 Replies

Hi Katia,

 

To enter array formula you have to enter it with combination of Ctrl+Shift+Enter, not just Enter

Your formula will look like (English UK notation)

 

=INDEX(A2:D500,MATCH(1,(F2>=C2:C500)*(H2<=D2:D500),0),2)

Under the Match you find if both dates are in the range, if TRUE condition returns 1 for proper record and that's what you need for the INDEX.

 

When back to French change comma "," on semicolon ";" as separator

 

 

Thanks so much for your quick response!

Your answer has been very useful but now i have another problem, it just appears one name instead of  at least couple of them, even when i choose the area, and do the ctrl+shift+enter at the end of the formule. 

 

What am i doing wrong this time??

Cheers

KCapture d’écran 2017-06-21 à 16.59.39.png

 

Hi Katia,

 

For multiply records we may use the patter which is described in many places, for example here http://www.exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/

 

Result looks like

 

Landlords.JPG

If step by step

1) Let add to $H$2 total number of lanlords who meet dates criteria using

 

=SUMPRODUCT((C:C<=$F$2)*(D:D>=$G$2))

Above is equivalent of AND() on two columns (that's not an array formula)

 

2) If list of landords we are looking for starts from F4 let put into tyhis cell an array (Ctrl+Shift+Enter) formula

 

=IF(
ROWS(F$4:F4)<=$H$2,
INDEX(B:B,
SMALL(
IF(C:C<=$F$2,
IF(D:D>=$G$2,
ROW(D:D)
)
),
ROWS(F$4:F4)
)
),
""
)

In

ROWS(F$4:F4)

pay attention to absolute and relative references and what in F4 your list starts.

 

 After that just copy cell F4 down.

 

3) In addition (or alternatively) you may use Conditional formatting to highlight available landlords in main list.

- Stay on cell B2 where entire list of names starts

- in Ribbon select Home->Conditional formatting->Manage rules

- New Rule and at the bottom of rule types select rules with formula

- in the formula bar enter formula which returns TRUE if criteria for the record in row is met

 

=($C2<=$F$2)*($D2>=$G$2)

(again, take care about absolute and relative references)

- apply desired format

- in Apply to enter

 

=$B:$B

- when Ok

 

That's all. See attached file for more details. And be careful applying above to the file with your regional setting.

best response confirmed by Katia Laville (Copper Contributor)
Solution

Katia, forgot to check what's the logic with To field in column D. If empty cell means the landord available from "From" date till any date in future, when we have to add in criteria if in D is empty cell. Number of available landlords (H2) will be

 

=SUMPRODUCT((C:C>0)*(C:C<=$F$2)*((D:D>=$G$2)+(D:D=0)))

and the formula in F4 (and down after that) is

 

=IF(
 ROWS(F$4:F4)<=$H$2,
 INDEX(B:B,
  SMALL(
   IF(C:C<=$F$2,
    IF((D:D>=$G$2)+(D:D=0),
     ROW(D:D)
    )
   ),
   ROWS(F$4:F4)
  )
 ),
 ""
)

File is attached.

 

YES!

Thank you so much Sergei! I did it with your help! I'm astonished by your kindness helping people you don't even know! Sharing your knowledge, it's amazing! It's a real community! I am reading your formulas and getting into the logic. I will amuse myself for a couple of days i think. I've never used the C:C thing. 

 

Thanks so much again!

Hope to see you around!

Bye

Katia 

 

1 best response

Accepted Solutions
best response confirmed by Katia Laville (Copper Contributor)
Solution

Katia, forgot to check what's the logic with To field in column D. If empty cell means the landord available from "From" date till any date in future, when we have to add in criteria if in D is empty cell. Number of available landlords (H2) will be

 

=SUMPRODUCT((C:C>0)*(C:C<=$F$2)*((D:D>=$G$2)+(D:D=0)))

and the formula in F4 (and down after that) is

 

=IF(
 ROWS(F$4:F4)<=$H$2,
 INDEX(B:B,
  SMALL(
   IF(C:C<=$F$2,
    IF((D:D>=$G$2)+(D:D=0),
     ROW(D:D)
    )
   ),
   ROWS(F$4:F4)
  )
 ),
 ""
)

File is attached.

 

View solution in original post