if... then....

[Salehin31]

Hi dear,
This is Salehin from chattagram, Bangladesh. Every time when I face any problem in Excel, firstly in my mind come this forum. This time also....
In my office management give me new Salary increment sheet. Where they (management) divide 2 parts 1. Admin 2. Production. with salary range.

Suppose if any staffs Salary 41000 in admin section and he get A his increment will be 2300

here I give the Salary range chart and main increment sheet also.

Admin
SL	Range	A+	A	A-	B	C
1​	10000-15000	2000​	1750​	1500​	1250​	250​
2​	15001-20000	2250​	1925​	1750​	1400​	300​
3​	20001-25000	2500​	2250​	1800​	1125​	450​
4​	25001-30000	2700​	2500​	2000​	1200​	500​
5​	30001-35000	2400​	2200​	2000​	1250​	500​
6​	35001-40000	2300​	2100​	1800​	1200​	500​
7​	40001-50000	2500​	2300​	2000​	1500​	500​
8​	50001-60000	2950​	2500​	2200​	1500​	500​
Production
SL	Range	A+	A	A-	B	C
1​	10000-15000	3500​	3250​	2500​	1300​	250​
2​	15001-20000	3500​	3150​	2400​	1200​	350​
3​	20001-25000	2925​	2475​	1800​	1100​	450​
4​	25001-30000	2700​	2500​	2000​	1200​	500​
5​	30001-35000	2400​	2200​	2000​	1250​	500​
6​	35001-40000	2300​	2100​	1800​	1200​	500​
7​	40001-50000	2500​	2300​	2000​	1500​	500​
8​	50001-60000	2950​	2300​	2200​	1500​	500​

Main Increment sheet

Designation	Section	Grading	Present Salary(Gross)	New Increment
GM	Admin	A+	157438
Sr.Officer(Accounts)	Admin	A	26638
Executive (Accounts)	Admin	A-	22700
Manager Com & HRD	Admin	B	44638
Deputy Manager (HR & Com.)	Admin	C	33988
WELFARE OFFICER	Admin	A+	20038
Welfare Officer	Admin	A	13663
Deputy Manager (Cutting)	Production	C	35788
Asst.Manager(Cutting)	Production	A+	34850
In-Charge (Cutting)	Production	A	24650
Executive (Cutting)	Production	A-	16038
Jr.Executive	Production	B	15363
Executive	Production	C	13888

I hope it will be solved.

 

[Peter_SSs]

I suggest that you update your Account details (or 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’)

First thing I would do is to split the salary ranges into two columns like this.

22 01 22.xlsm
	A	B	C	D	E	F	G	H
1	Admin
2	SL	Range Start	Range End	A+	A	A-	B	C
3	1	10000	15000	2000	1750	1500	1250	250
4	2	15001	20000	2250	1925	1750	1400	300
5	3	20001	25000	2500	2250	1800	1125	450
6	4	25001	30000	2700	2500	2000	1200	500
7	5	30001	35000	2400	2200	2000	1250	500
8	6	35001	40000	2300	2100	1800	1200	500
9	7	40001	50000	2500	2300	2000	1500	500
10	8	50001	60000	2950	2500	2200	1500	500
11	Production
12	SL	Range Start	Range End	A+	A	A-	B	C
13	1	10000	15000	3500	3250	2500	1300	250
14	2	15001	20000	3500	3150	2400	1200	350
15	3	20001	25000	2925	2475	1800	1100	450
16	4	25001	30000	2700	2500	2000	1200	500
17	5	30001	35000	2400	2200	2000	1250	500
18	6	35001	40000	2300	2100	1800	1200	500
19	7	40001	50000	2500	2300	2000	1500	500
20	8	50001	60000	2950	2300	2200	1500	500
Salary

The following formulas are based on the fact that the salary ranges and letter grades are identical in each section. If that changes then the formulas will need updating.

22 01 22.xlsm
	A	B	C	D	E
1	Designation	Section	Grading	Present Salary(Gross)	New Increment
2	GM	Admin	A+	157438	2950
3	Sr.Officer(Accounts)	Admin	A	26638	2500
4	Executive (Accounts)	Admin	A-	22700	1800
5	Manager Com & HRD	Admin	B	44638	1500
6	Deputy Manager (HR & Com.)	Admin	C	33988	500
7	WELFARE OFFICER	Admin	A+	20038	2500
8	Welfare Officer	Admin	A	13663	1750
9	Deputy Manager (Cutting)	Production	C	35788	500
10	Asst.Manager(Cutting)	Production	A+	34850	2400
11	In-Charge (Cutting)	Production	A	24650	2475
12	Executive (Cutting)	Production	A-	16038	2400
13	Jr.Executive	Production	B	15363	1200
14	Executive	Production	C	13888	250
Increment

Cell Formulas
Range	Formula
E2:E14	E2	=INDEX(IF(B2="Admin",Salary!$D$3:$H$10,Salary!$D$13:$H$20),MATCH(D2,Salary!B$3:B$10,1),MATCH(C2,Salary!D$2:H$2,0))

 

[Salehin31]

I suggest that you update your Account details (or 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’)

First thing I would do is to split the salary ranges into two columns like this.

22 01 22.xlsm
	A	B	C	D	E	F	G	H
1	Admin
2	SL	Range Start	Range End	A+	A	A-	B	C
3	1	10000	15000	2000	1750	1500	1250	250
4	2	15001	20000	2250	1925	1750	1400	300
5	3	20001	25000	2500	2250	1800	1125	450
6	4	25001	30000	2700	2500	2000	1200	500
7	5	30001	35000	2400	2200	2000	1250	500
8	6	35001	40000	2300	2100	1800	1200	500
9	7	40001	50000	2500	2300	2000	1500	500
10	8	50001	60000	2950	2500	2200	1500	500
11	Production
12	SL	Range Start	Range End	A+	A	A-	B	C
13	1	10000	15000	3500	3250	2500	1300	250
14	2	15001	20000	3500	3150	2400	1200	350
15	3	20001	25000	2925	2475	1800	1100	450
16	4	25001	30000	2700	2500	2000	1200	500
17	5	30001	35000	2400	2200	2000	1250	500
18	6	35001	40000	2300	2100	1800	1200	500
19	7	40001	50000	2500	2300	2000	1500	500
20	8	50001	60000	2950	2300	2200	1500	500
Salary

The following formulas are based on the fact that the salary ranges and letter grades are identical in each section. If that changes then the formulas will need updating.

22 01 22.xlsm
	A	B	C	D	E
1	Designation	Section	Grading	Present Salary(Gross)	New Increment
2	GM	Admin	A+	157438	2950
3	Sr.Officer(Accounts)	Admin	A	26638	2500
4	Executive (Accounts)	Admin	A-	22700	1800
5	Manager Com & HRD	Admin	B	44638	1500
6	Deputy Manager (HR & Com.)	Admin	C	33988	500
7	WELFARE OFFICER	Admin	A+	20038	2500
8	Welfare Officer	Admin	A	13663	1750
9	Deputy Manager (Cutting)	Production	C	35788	500
10	Asst.Manager(Cutting)	Production	A+	34850	2400
11	In-Charge (Cutting)	Production	A	24650	2475
12	Executive (Cutting)	Production	A-	16038	2400
13	Jr.Executive	Production	B	15363	1200
14	Executive	Production	C	13888	250
Increment

Cell Formulas
Range	Formula
E2:E14	E2	=INDEX(IF(B2="Admin",Salary!$D$3:$H$10,Salary!$D$13:$H$20),MATCH(D2,Salary!B$3:B$10,1),MATCH(C2,Salary!D$2:H$2,0))

Thank's a lot sir...
But I am confused that should I create a new sheet named salary because I see in your formula which I bold

=INDEX(IF(B2="Admin",Salary!$D$3:$H$10,Salary!$D$13:$H$20),MATCH(D2,Salary!B$3:B$10,1),MATCH(C2,Salary!D$2:H$2,0))

 

[Peter_SSs]

You do not have to create a new sheet. However, you did not tell us the actual name of the sheet that the first table in your post is on. Instead you said "In my office management give me new Salary increment sheet." so I just called my sheet Salary. Change that to whatever the name of your sheet is. If the sheet name contains any space characters then you need single quote marks around the sheet name. For example, if the sheet name was 'Increment Data' then my formula would be

=INDEX(IF(B2="Admin",'Increment Data'!$D$3:$H$10,'Increment Data'!$D$13:$H$20),MATCH(D2,'Increment Data'!B$3:B$10,1),MATCH(C2,'Increment Data'!D$2:H$2,0))