VLOOKUP Formula help?

[Sunshine8790]

Bit of a complex question this time.
I want a formula in column E that will return a value from column K, based on whether Column B corresponds with Column J - using cell K2 as a guide.
I've been trying to play around with the VLOOKUP formula, but it's clearly not working. Help?

PTO COPY.xlsx
	A	B			E	F	G	H	I	J	K
1	Name	Hire Date			Allotted Time					Fiscal Year Begin:	February
2	PERSON A	7/10/2023			#N/A					Current Date:	9/7/2023
3	PERSON B	5/31/2023								Less than 1 Year	0
4	PERSON C	5/15/2023								1 Year	10
5	PERSON D	4/26/2023								2 Years	15
6	PERSON E	2/13/2023								3 Years	15
7	PERSON F	9/26/2022								4 Years	15
8	PERSON G	9/19/2022								5 Years	20
9	PERSON H	9/12/2022
10	PERSON I	9/6/2022
11	PERSON J	8/22/2022
12	PERSON K	7/13/2022
13	PERSON L	6/20/2022
14	PERSON M	4/19/2022
15	PERSON N	3/14/2022
16	PERSON O	1/3/2022
17	PERSON P	10/25/2021
18	PERSON Q	10/18/2021
19	PERSON R	9/7/2021
20	PERSON S	8/23/2021
21	PERSON T	10/19/2020
22	PERSON U	6/28/2020
23	PERSON V	10/7/2019
24	PERSON W	9/16/2019
25	PERSON X	2/21/2019
26	PERSON Y	8/24/2018
27	PERSON Z	8/6/2018
Employee Summary

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(MOD(K2,1),J3:K7,2)
K2	K2	=TODAY()

 

[Sunshine8790]

For example:

Person A has a Hire date less than one year prior to the current date but not prior to February, so it should return a value of 0.

Person P has a hire date of 2 years prior to the current date that has a month that is after February (October), so even though we're in September - it still counts as 2 years instead of 1 and they would get 15 since this is their 2nd time working past February.

 

[dreid1011]

For example:

Person A has a Hire date less than one year prior to the current date but not prior to February, so it should return a value of 0.

Person P has a hire date of 2 years prior to the current date that has a month that is after February (October), so even though we're in September - it still counts as 2 years instead of 1 and they would get 15 since this is their 2nd time working past February.

What about Person F and Person I?

 

[Sunshine8790]

What about Person F and Person I?

Persons F through I all have after February hire dates.
They hit February once so far, so they would all be 10.
You made me re-think the value date though.... instead of the current date, I should be basing it off of Feb 1st of the current year.

So now my chart looks like this but I still need formula help to return the values based on the criteria I mentioned:

PTO COPY.xlsx
	A	B			E	F	G	H	I	J	K	L	M	N	O
1	Name	Hire Date			Allotted Time							2	1	2023	2/1/2023
2	PERSON A	7/10/2023			#N/A					Fiscal Year Start:	2/1/2023	2/1		9/7/2023
3	PERSON B	5/31/2023								Less than 1 Year	0
4	PERSON C	5/15/2023								1 Year	10
5	PERSON D	4/26/2023								2 Years	15
6	PERSON E	2/13/2023								3 Years	15
7	PERSON F	9/26/2022								4 Years	15
8	PERSON G	9/19/2022								5 Years	20
9	PERSON H	9/12/2022
10	PERSON I	9/6/2022
11	PERSON J	8/22/2022
12	PERSON K	7/13/2022
13	PERSON L	6/20/2022
14	PERSON M	4/19/2022
15	PERSON N	3/14/2022
16	PERSON O	1/3/2022
17	PERSON P	10/25/2021
18	PERSON Q	10/18/2021
19	PERSON R	9/7/2021
20	PERSON S	8/23/2021
21	PERSON T	10/19/2020
22	PERSON U	6/28/2020
23	PERSON V	10/7/2019
24	PERSON W	9/16/2019
25	PERSON X	2/21/2019
26	PERSON Y	8/24/2018
27	PERSON Z	8/6/2018
Employee Summary

Cell Formulas
Range	Formula
L1	L1	=MONTH(L2)
M1	M1	=DAY(L2)
O1	O1	=L1&"/"&M1&"/"&N1
E2	E2	=VLOOKUP(MOD(K2,1),J3:K8,2)
K2	K2	=O1
N1	N1	=YEAR(N2)
N2	N2	=TODAY()

 

[dreid1011]

Do these results look like what you expect?

 

[Sunshine8790]

@dreid1011 I'm going to say yes for the purpose of finding out the formula you used (in case I need it for future reference), but I'm gonna call it quits otherwise and just get a manual list done without formulas.
What formula did you use?

 

[dreid1011]

@dreid1011 I'm going to say yes for the purpose of finding out the formula you used (in case I need it for future reference), but I'm gonna call it quits otherwise and just get a manual list done without formulas.
What formula did you use?

I used this:

=LOOKUP($K$2-$B2,{-365,0,365,730,1095,1460},$K$3:$K$8)

You can expand the K3:K8 range at need if you add more years, and would add another element to the array for each additional year added. The array values could be tweaked depending on how you want it to react on exactly Feb 1 of any given year though.