Variable start point triggering a row of info

[sgarbett364]

Hi

I need to create a table of income that is dependant on an employee start date.

Essentially I have a table of employees that need to have variable start dates and from this "trigger point" the row of income an employee will generate will be put in the Income table from that date onwards.

I've attached a screenshot that hopefully explains it better. I can trigger the first month using if's but cant figure out how to then have the following months info following on.

Many thanks

 

[Fluff]

Hi, what version of Office are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[sgarbett364]

Office 365. I'll update

 

[Fluff]

Ok, in that case do you have the new LET function?

 

[sgarbett364]

Yes I do

 

[Fluff]

Ok, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	EE 1	01/05/2021
3	EE 2	01/02/2021
4	EE 3	01/01/2020
5	EE 4
6
7
8	0	0	0	2000	3456	4567	5678	6789
9
10
11		01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021
12
13	EE 1					0	0	0	2000	3456
14	EE 2		0	0	0	2000	3456	4567	5678	6789
15	EE 3	0	0	0	2000	3456	4567	5678	6789	0
16	EE 4
17	EE 5
Master

Cell Formulas
Range	Formula
B13:J17	B13	=LET(Indx,INDEX($B$2:$B$5,MATCH($A13,$A$2:$A$5,0)),IF(ISNA(Indx),"",IF(OR(Indx="",Indx>B$11),"",INDEX($A$8:$N$8,MONTH(B$11)-MONTH(Indx)+1))))

 

[sgarbett364]

Not that I understand exactly whats going on here but I think once you get to a value greater than the year end it falls over. I.e Jan 22 should return month 9 of the income plan. The income plan is set rolling outwards ie M1 to M60 assuming 5 years of revenue that increases to a point over 2 years then flattens out. Row 11 represents that income gear up the employee can achieve

 

[sgarbett364]

Thank you FYI. Your helps very much appreciated

 

[jdellasala]

Hi

I need to create a table of income that is dependant on an employee start date.

Essentially I have a table of employees that need to have variable start dates and from this "trigger point" the row of income an employee will generate will be put in the Income table from that date onwards.

I've attached a screenshot that hopefully explains it better. I can trigger the first month using if's but cant figure out how to then have the following months info following on.

Many thanks

I hope I'm understanding the problem correctly. If I am, I think the easiest solution is to do either a VLOOKUP or an HLOOKUP, but do an APROXIMATE MATCH rather than the more common EXACT MATCH. With an approximate match, the lookup will search the table for the first value larger than the lookup value and return the previous value. NOTE: THE LEFT COLUMN / TOP ROW MUST BE SORTED SMALLEST TO LARGEST FOR THIS TO WORK. It wasn't clear exactly what your setup is, but hopefully the attached is somewhat self explanatory. In the attached, the formula columns are the formulas of the cells to the left.

 

[Fluff]

Ok, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2	EE 1	01/05/2021
3	EE 2	01/02/2021
4	EE 3	01/01/2021
5	EE 4
6
7
8	0	0	0	2000	3456	4567	5678	6789	1234	2345	23456	4567	7890	1235	456	789	235	123	567
9
10
11		01/01/2021	01/02/2021	01/03/2021	01/04/2021	01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021	01/12/2021	01/01/2022	01/02/2022	01/03/2022	########	########	########
12
13	EE 1					0	0	0	2000	3456	4567	5678	6789	1234	2345	23456	4567	7890	1235
14	EE 2		0	0	0	2000	3456	4567	5678	6789	1234	2345	23456	4567	7890	1235	456	789	235
15	EE 3	0	0	0	2000	3456	4567	5678	6789	1234	2345	23456	4567	7890	1235	456	789	235	123
16	EE 4
17	EE 5
18
19
Master

Cell Formulas
Range	Formula
B13:S17	B13	=LET(Indx,INDEX($B$2:$B$5,MATCH($A13,$A$2:$A$5,0)),IF(ISNA(Indx),"",IF(OR(Indx="",Indx>B$11),"",INDEX($A$8:$Z$8,COLUMN()-MATCH(Indx,$A$11:$Z$11,0)+1))))