Hi,

I’m trying to make some modifications to a salary matrix and
I’m having trouble with a lookup.

In column A I have a series of numbers from 0 to 30 representing pay
grade steps where 0 represents a new employee and 30 represents an
employee having completed 30 years of service. In column B I have an
annual salary for each of the corresponding pay grade steps.

I want to take a new salary and find the closest salary in column B and
return the corresponding step number.

For example if the new salary is $73,415.13 and the matrix shows step
15 is 73,205 and step 16 is 73,665, I want to return a 15.

How could I accomplish this?

Thanks!


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=500526

Hi!

Try this:

A1:A31 = series from 0 to 30

B1:B31 = salaries in ascending order

C1 = $73,415.13

=INDEX(A1:A31,MATCH(C1,B1:B31))

Biff

"Cybertech" wrote
in message ...

Hi,

I’m trying to make some modifications to a salary matrix and
I’m having trouble with a lookup.

In column A I have a series of numbers from 0 to 30 representing pay
grade steps where 0 represents a new employee and 30 represents an
employee having completed 30 years of service. In column B I have an
annual salary for each of the corresponding pay grade steps.

I want to take a new salary and find the closest salary in column B and
return the corresponding step number.

For example if the new salary is $73,415.13 and the matrix shows step
15 is 73,205 and step 16 is 73,665, I want to return a 15.

How could I accomplish this?

Thanks!


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile:
http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=500526

Thanks for the reply *Biff*, but I have an error somewhere. This is the
formula I have in cell E17,:

=INDEX($A$1:$A$31,MATCH(D17,$C$1:$C$31))

Excel is returning a "9" when the correct answer is "10"

70,804.95 is closer to 70,935.00 (Step 10) than 70,482.00 (Step 9)

Where 70,804.95 is the Computed New Salary, 70,935.00 is the New Matrix
Step 10 & 70,482.00 is the New Matrix Step 9

Here is an actual table, sorry for the formatting problems:

Step OldMatrix New Matrix Computed New Step Correct Step
0
1
2
3
4
5
6
7 57,626.00 57,626.00 64,224.87 8 8
8 59,955.00 63,949.00 66,820.57 8 8
9 62,361.00 70,482.00 69,502.08 8 9
10 62,528.00 70,935.00 69,688.20 8 9
11 62,694.00 71,386.00 69,873.21 8 9
12 62,863.00 71,845.00 70,061.57 8 9
13 63,030.00 72,298.00 70,247.69 8 9
14 63,197.00 72,751.00 70,433.81 8 9
15 63,364.00 73,205.00 70,619.94 9 9
16 63,530.00 73,655.00 70,804.95 9 10
17 63,699.00 74,114.00 70,993.30 10 10
18 63,866.00 74,568.00 71,179.42 10 11
19 64,034.00 75,024.00 71,366.66 10 11
20 64,200.00 75,474.00 71,551.67 11 11
21 64,367.00 75,928.00 71,737.79 11 12
22 64,535.00 76,384.00 71,925.03 12 12
23 64,702.00 76,837.00 72,111.15 12 13
24 64,870.00 77,294.00 72,298.39 13 13
25 65,036.00 77,744.00 72,483.40 13 13
26 65,203.00 78,198.00 72,669.52 13 14
27 65,371.00 78,654.00 72,856.76 14 14
28 65,538.00 79,107.00 73,042.89 14 15
29 65,703.00 79,555.00 73,226.78 15 15
30 65,872.00 80,014.00 73,415.13 15 15

What am I doing wrong?

Thanks!!


--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
View this thread: http://www.excelforum.com/showthread...hreadid=500526