Cell to calculate based on matrix from other sheet

[rwiessner]

I have an Annual Salary_2 column or"K" that I want to calculate based on the matrix from another sheet (seen below). COLUMN "Score" or "L"has the overall rating where the matrix can find the number for calculating. Not sure the best formula to use in this example?

The Matrix is:
COLUMN A: Overall Performance Rating (decimal rating)
COLUMN B: Merit Increase Percentage (% based on rating)
1.0 - 2.9= 0.00%
3 =0.50%
3.1 =0.75%
3.2 =1.00%
3.3 =1.25%
3.4 =1.50%
3.5 =1.75%
3.6 =2.00%
3.7 =2.25%
3.8 =2.50%
3.9 =2.75%
4 =3.00%
4.1 =3.25%
4.2 =3.50%
4.3 =3.75%
4.4 =4.00%
4.5 =4.25%
4.6 =4.50%
4.7 =4.75%
4.8 =5.00%
4.9 =5.25%
5 =5.50%

[jtakw]

Hi,

Do you mean something like this?

Excel 2016 (Windows) 64 bit

	J	K	L	M
1	Score	Annual Salary	Matrix
2	3.9	50000	1	0.00%
3			3	0.50%
4	New Salary	51375	3.1	0.75%
5			3.2	1.00%
6			3.3	1.25%
7			3.4	1.50%
8			3.5	1.75%
9			3.6	2.00%
10			3.7	2.25%
11			3.8	2.50%
12			3.9	2.75%
13			4	3.00%
14			4.1	3.25%
15			4.2	3.50%
16			4.3	3.75%
17			4.4	4.00%
18			4.5	4.25%
19			4.6	4.50%
20			4.7	4.75%
21			4.8	5.00%
22			4.9	5.25%
23			5	5.50%

Sheet: Sheet20

Excel 2016 (Windows) 64 bit

	K
4	=K2*(1+LOOKUP(J2,L2:L23,M2:M23))

Sheet: Sheet20

[rwiessner]

Close, but I need it to refer to the score being 3.50 and then calculating the 1.75% increase onto "J" or "Annual Salary" into "K" or "Annual Salary_2


First Sheet:
J K L
Annual Salary Annual Salary_2 Score
35.33 3.50
57.89 3.00
59.33 4.60

Second Sheet:
A B
Overall Performance Rating Merit Increase Percentage
1.0 - 2.9 0.00%
3 0.50%
3.1 0.75%
3.2 1.00%
3.3 1.25%
3.4 1.50%
3.5 1.75%
3.6 2.00%
3.7 2.25%
3.8 2.50%
3.9 2.75%
4 3.00%
4.1 3.25%
4.2 3.50%
4.3 3.75%
4.4 4.00%
4.5 4.25%
4.6 4.50%
4.7 4.75%
4.8 5.00%
4.9 5.25%
5 5.50%

[jtakw]

That's what my formula is doing, just change the cell references to match your real data.

[jtakw]

Here, changed cell references to match your post #3, you Need to add your "Second" sheet name in the formula, and your "Matrix" needs to look like my sample.

Excel 2016 (Windows) 64 bit

	A	B	C	J	K	L
1	Matrix			Annual Salary	Annual Salary_2	Score
2	1	0.00%		35.33	35.948275	3.5
3	3	0.50%		57.89	58.17945	3
4	3.1	0.75%		59.33	61.99985	4.6
5	3.2	1.00%
6	3.3	1.25%
7	3.4	1.50%
8	3.5	1.75%
9	3.6	2.00%
10	3.7	2.25%
11	3.8	2.50%
12	3.9	2.75%
13	4	3.00%
14	4.1	3.25%
15	4.2	3.50%
16	4.3	3.75%
17	4.4	4.00%
18	4.5	4.25%
19	4.6	4.50%
20	4.7	4.75%
21	4.8	5.00%
22	4.9	5.25%
23	5	5.50%

Sheet: Sheet20

Excel 2016 (Windows) 64 bit

	K
2	=J2*(1+LOOKUP(L2,A$2:A$23,B$2:B$23))

Sheet: Sheet20

K2 formula copied down to K4.