I'm working with a table, column D is populated with a date (cell D2 is 15/04/2020)

Column E is entitled Year.

I want the data in column E to equal the financial year (ie dates between 01/04/2020 and 31/03/2021 should return the result 2020 and so on)

On a separate worksheet (named Validation) I have the parameters for the calculation

A1 has Date From

B1 has Date To

C1 has Year

A2- A11 has the first of April for each year from 2020 to 2029

B2- B11 has the 31st March for each year from 2021 to 2030

C2- C11 has 2020 to 2029

I have tried the following formula: =LOOKUP(2,1/($A$2:$A$11<=[@[Date of Application]])/$B$2:$B11>=[@[Date of Application]]),$C$2:$C$11)

NB: [@[Date of Application]] is what appears in the formula when I reference D2 (15/04/2020 above)

The formula should return 2020 for cell E2 but it returns 1905

I don't understand why it isn't working, is it because the date I'm trying to use to define the year is within a table?

Or is it because my parameters are on a different sheet? because when I enter the formula in the Validation worksheet where the date parameters are, it works perfectly

Apologies for not posting screenshots or the file, but as it is for work and contains GDPR sensitive and personal data

Thanks in advance for any help