Formula Taking A Date & Returning A Year

[SteveP29]

Hi,
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

 

[Fluff]

In that case try

=LOOKUP(2,1/(Validation!$A$2:$A$11<=[@[Date of Application]])/Validation!$B$2:$B11>=[@[Date of Application]]),Validation!$C$2:$C$11)

 

[SteveP29]

In that case try

=LOOKUP(2,1/(Validation!$A$2:$A$11<=[@[Date of Application]])/Validation!$B$2:$B11>=[@[Date of Application]]),Validation!$C$2:$C$11)

That's just returning 2029

 

[Fluff]

In that case can you please post some sample data.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[SteveP29]

Apologies for the delay in getting back to you.
Here's the extract of the data etc

Applications Log (Fiddle Copy 2).xlsx
	A	B	C	D
1	Week	Int/ext	Date of Application	Year
2	3	Int	15/04/2020	2029
3	3	Ext	16/04/2020	2029
4	3	Ext	16/04/2020	2029
5	3	Int	15/04/2020	2029
6	4	Int	20/04/2020	2029
7	4	Int	20/04/2020	2029
8	4	Int	20/04/2020	2029
9	4	Int	20/04/2020	2029
10	4	Int	20/04/2020	2029
11	3	Int	17/04/2020	2029
12	4	Int	22/04/2020	2029
13	4	Int	22/04/2020	2029
14	4	Int	20/04/2020	2029
15	4	Int	22/04/2020	2029
Data

Cell Formulas
Range	Formula
D2:D15	D2	=LOOKUP(2,1/(Validations!$A$2:$A$11>=[@[Date of Application]])/(Validations!$B$2:$B$11<=[@[Date of Application]]),(Validations!$C$2:$C$11))

Applications Log (Fiddle Copy 2).xlsx
	A	B	C	D
1	Date From	Date To	Year
2	01/04/2020	01/04/2021	01/04/2020
3	01/04/2021	01/04/2022	01/04/2021
4	01/04/2022	01/04/2023	01/04/2022
5	01/04/2023	01/04/2024	01/04/2023
6	01/04/2024	30/03/2025	01/04/2024
7	01/04/2025	01/04/2026	01/04/2025
8	01/04/2026	01/04/2027	01/04/2026
9	01/04/2027	01/04/2028	01/04/2027
10	01/04/2028	01/04/2029	01/04/2028
11	01/04/2029	01/04/1930	01/04/2029
12
13
14		2025		14/07/2025
15		2027		03/05/2027
16		2026		30/11/2026
17		2028		27/04/2028
18		2023		31/03/2024
19		2027		01/04/2027
20		2023		19/08/2023
Validations

Cell Formulas
Range	Formula
B14:B20	B14	=LOOKUP(2,1/($A$2:$A$10<=D14)/($B$2:$B$10>=D14),$C$2:$C$10)

 

[Fluff]

You've got the > & < the wrong way round, it should be

=LOOKUP(2,1/(Validations!$A$2:$A$11<=[@[Date of Application]])/(Validations!$B$2:$B$11>=[@[Date of Application]]),(Validations!$C$2:$C$11))

 

[SteveP29]

You've got the > & < the wrong way round, it should be

=LOOKUP(2,1/(Validations!$A$2:$A$11<=[@[Date of Application]])/(Validations!$B$2:$B$11>=[@[Date of Application]]),(Validations!$C$2:$C$11))

And as soon as I changed it, it works perfectly, Thank you!!

 

[Fluff]

You're welcome & thanks for the feedback.