Employee Move to Next Level

[ChetanPuri]

Hi Excel Community,

I am trying to Build a Formula in excel for employees who are on different levels each year they get promoted on the next level, until they reach the last level within their classification on Column E . Is there a way to automate that in column C , if they are other than salaried staff they get an increase to the next level and then when CPI Increases they get a new rate whereas salaried employees they get CPI, any help would be greatly appreciated.

Increase

Book11
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Current Level	Default Role		Start Date	Anniversary date	New Rate	CPI Increase 01 July 2024	New Level
2	Pay Rate											CPI	2%
3	SALARY	Manager						20,400.00	GREENTREE Contract Listing Report
4	SALARY	Director							Classification	Current	Future	Old Rate	New Rate
5	SALARY	Director							L11	SCHADS L1.1	SCHADS L1.2	$2	$2.04
6	SALARY	Manager							L12	SCHADS L1.2	SCHADS L1.3	3.00	$3.06
7	SALARY	Director							L13	SCHADS L1.3	SCHADS L1.3	4.00	$4.08
8	SALARY	Manager							L21	SCHADS L2.1	SCHADS L2.2	5	$5.10
9	SCHADS L5.3	Admin							L22	SCHADS L2.2	SCHADS L2.3
10	SCHADS L6.3	Admin							L23	SCHADS L2.3	SCHADS L2.3
11									L24	SCHADS L2.4	SCHADS L3.1
12	SALARY	Director							L31	SCHADS L3.1	SCHADS L3.2
13	SALARY	Director							L32	SCHADS L3.2	SCHADS L3.3
14	SCHADS L6.2	IL Coordinator	SCHADS L6.3	7/03/2023	7/04/2024	53	$54.06		L33	SCHADS L3.3	SCHADS L3.4
15	SCHADS L5.2	Coordinator							L34	SCHADS L3.4	SCHADS L3.4
16	SCHADS L6.2	IL Coordinator							L41	SCHADS L4.1	SCHADS L4.2
17	SCHADS L6.2	IL Coordinator							L42	SCHADS L4.2	SCHADS L4.3
18	SCHADS L6.2	IL Coordinator							L43	SCHADS L4.3	SCHADS L4.4
19	SCHADS L6.1	IL Coordinator							L44	SCHADS L4.4	SCHADS L5.1
20	SCHADS L6.2	IL Coordinator							L51	SCHADS L5.1	SCHADS L5.2
21	SCHADS L6.2	IL Coordinator							L52	SCHADS L5.2	SCHADS L5.3
22	SCHADS L6.2	Admin							L53	SCHADS L5.3	SCHADS L5.3+$20
23	SCHADS L5.2	Admin							L53+20	SCHADS L5.3+$20	SCHADS L6.1
24	SALARY	Manager							L61	SCHADS L6.1	SCHADS L6.2
25	SALARY	Manager							L62	SCHADS L6.2	SCHADS L6.3
26	SCHADS L5.3+$20	Admin							L63	SCHADS L6.3	SCHADS L7.1	53	$54.06
27	SCHADS L5.3	Admin							L71	SCHADS L7.1	SCHADS L7.2
28	SCHADS L6.3	Admin							L72	SCHADS L7.2	SCHADS L7.3
29	SCHADS L4.1	Admin							L73	SCHADS L7.3	SCHADS L8.1
30	SALARY	Admin							L81	SCHADS L8.1	SCHADS L8.2
31	SCHADS L4.1	Admin							L82	SCHADS L8.2	SCHADS L8.3
32	SALARY	Admin							L83	SCHADS L8.3
33	SCHADS L4.1	Admin								SALARY	CPI
34	SCHADS L4.4	Admin							L22CAS	SCHADS L2.2 CASUAL	CPI
35	SCHADS L4.1	Admin							L23CAS	SCHADS L2.3 CASUAL	CPI
36	SCHADS L2.1	Admin							L41CAS	SCHADS L4.1 CASUAL	CPI
37	SCHADS L5.1	Admin
Sheet1

Cell Formulas
Range	Formula
H3	H3	=20000*(1+M2)
M5:M8,M26	M5	=L5*(1+$M$2)
C14	C14	=J26
F14:G14	F14	=L26
K5:K6,K8:K9,K11:K14,K16:K31	K5	=J6
K7,K10,K15	K7	=J7

 

[Rijnsent]

You probably are looking for XLOOKUP (if you have the latest Excel version) or VLOOKUP. See e.g. VLOOKUP Function