Return Value between two dates on another sheet

[mikey141]

Evening,
I've tested the lookup formula within the same sheet to enter a date and it returns a period date (For Work), but when i want to search from another sheet it doesn't work

Below is on the same sheet
=LOOKUP(2,1/(C5:C17<=L5)/(E5:E17>=L5),F5:F17)

Below is from another sheet that doesnt work
=LOOKUP(2,1/('Period Dates'!C5:C17<=A3)/('Period Dates'!E5:E17>=A3),'Period Dates'!F5:F17)

 

[Aladin Akyurek]

It should work...

What result are you getting? Is A3 = L5?

 

[mikey141]

Just returns 0 or in the workbook I'm trying to use the formula in it just displays the formula instead.

It should work...

What result are you getting? Is A3 = L5?

 

[Aladin Akyurek]

Select the formula cells

Run Find&Select | Replace

Set Find What to: =
Set Replace With to: =

Activate the Replace All button.