software

Hi,

I#8217;m trying to make some modifications to a salary matrix and
I#8217;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: www.excelforum.com/member.php...oamp;userid=26533
View this thread: www.excelforum.com/showthread...hreadid=500526Hi!

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

quot;Cybertechquot; gt; wrote
in message ...
gt;
gt; Hi,
gt;
gt; I#8217;m trying to make some modifications to a salary matrix and
gt; I#8217;m having trouble with a lookup.
gt;
gt; In column A I have a series of numbers from 0 to 30 representing pay
gt; grade steps where 0 represents a new employee and 30 represents an
gt; employee having completed 30 years of service. In column B I have an
gt; annual salary for each of the corresponding pay grade steps.
gt;
gt; I want to take a new salary and find the closest salary in column B and
gt; return the corresponding step number.
gt;
gt; For example if the new salary is $73,415.13 and the matrix shows step
gt; 15 is 73,205 and step 16 is 73,665, I want to return a 15.
gt;
gt; How could I accomplish this?
gt;
gt; Thanks!
gt;
gt;
gt; --
gt; Cybertech
gt; ------------------------------------------------------------------------
gt; Cybertech's Profile:
gt; www.excelforum.com/member.php...oamp;userid=26533
gt; View this thread: www.excelforum.com/showthread...hreadid=500526
gt;

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 quot;9quot; when the correct answer is quot;10quot;

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 amp; 70,482.00 is the New Matrix Step 9

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

Step OldMatrixNew MatrixComputed New StepCorrect Step
0
1
2
3
4
5
6
757,626.0057,626.0064,224.8788
859,955.0063,949.0066,820.5788
962,361.0070,482.0069,502.0889
1062,528.0070,935.0069,688.2089
1162,694.0071,386.0069,873.2189
1262,863.0071,845.0070,061.5789
1363,030.0072,298.0070,247.6989
1463,197.0072,751.0070,433.8189
1563,364.0073,205.0070,619.9499
1663,530.0073,655.0070,804.95910
1763,699.0074,114.0070,993.301010
1863,866.0074,568.0071,179.421011
1964,034.0075,024.0071,366.661011
2064,200.0075,474.0071,551.671111
2164,367.0075,928.0071,737.791112
2264,535.0076,384.0071,925.031212
2364,702.0076,837.0072,111.151213
2464,870.0077,294.0072,298.391313
2565,036.0077,744.0072,483.401313
2665,203.0078,198.0072,669.521314
2765,371.0078,654.0072,856.761414
2865,538.0079,107.0073,042.891415
2965,703.0079,555.0073,226.781515
3065,872.0080,014.0073,415.131515

What am I doing wrong?

Thanks!!--
Cybertech
------------------------------------------------------------------------
Cybertech's Profile: www.excelforum.com/member.php...oamp;userid=26533
View this thread: www.excelforum.com/showthread...hreadid=500526