Look Up with If Statement formula

[jevi]

[TABLE="width: 360"]
<colgroup><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD="align: right"] 05/11/2018[/TD]
[TD][/TD]
[TD]Ann[/TD]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"] -06/11/2018[/TD]
[/TR]
[TR]
[TD]Ann[/TD]
[TD][/TD]
[TD]Ann[/TD]
[TD="align: right"]01/12/2018[/TD]
[TD="align: right"] -02/12/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sam[/TD]
[TD="align: right"]24/12/2018[/TD]
[TD="align: right"] -24/12/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Sam[/TD]
[TD="align: right"]28/12/2018[/TD]
[TD="align: right"] -28/12/2018
[/TD]
[/TR]
</tbody>[/TABLE]

=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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

05/11/2018​

[/td][td][/td][td]

Ann​

[/td][td]

01/11/2018​

[/td][td]

06/11/2018​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Ann​

[/td][td][/td][td]

Ann​

[/td][td]

01/12/2018​

[/td][td]

02/12/2018​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td][/td][td][/td][td]

Sam​

[/td][td]

24/12/2018​

[/td][td]

24/12/2018​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td][/td][td][/td][td]

Sam​

[/td][td]

28/12/2018​

[/td][td]

28/12/2018​

[/td][/tr]
[/table]

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