Newbe help with a lookup

[Cybertech]

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!

[Biff]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Cybertech]

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!!