Look Up with If Statement formula

[jevi]

05/11/2018		Ann	01/11/2018	-06/11/2018
Ann		Ann	01/12/2018	-02/12/2018
		Sam	24/12/2018	-24/12/2018
		Sam	28/12/2018	-28/12/2018

<colgroup><col><col span="2"><col span="2"></colgroup><tbody>
</tbody>

=LOOKUP(3;2/($D$1:$D$4<=A1)/($E$1:$E$4>=A1);$C$1:$C$4)

Hi all,

I am looking for a formula that needs to check if the date 05/11/2018 is in the whole range starting from 01/11/2018 to 28/12/2018 and if the name below the date 05/11/2018 matches that in the column name.

I tried the above formula but is not working.

Thank you for your help.

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​
1​	05/11/2018​		Ann​	01/11/2018​	06/11/2018​
2​	Ann​		Ann​	01/12/2018​	02/12/2018​
3​			Sam​	24/12/2018​	24/12/2018​
4​			Sam​	28/12/2018​	28/12/2018​

Formula in A2
=INDEX(C$1:C$4,MATCH(1,INDEX((E$1:E$4>=A1)*(A1>=D$1:D$4),),0))

Hope this helps

M.

 

[jevi]

Thank you Marcello, but your formula gives the same result as mine...but I would like the formula to be in A3 and give the result only if A2 is in column C and also the date A1 is in the range of dates. So need to have two conditions matched. Like put a Yes maybe in A3 if A2 is in column C1:C4 and A1 is in the range D1:E4

 

[Marcelo Branco]

Try

A3
=IF(COUNTIFS(C1:C4,A2,D1:D4,"<="&A1,E1:E4,">="&A1),"Yes","No")

M.

 

[jevi]

It works great. Just the result I was hoping for. It is a pitty though that Countifs doesn't work with closed workbook but I will copy the data to the same book where I need to use the formula.

Thank you so much for your help