Newbe help with a lookup


C

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

Advertisements

B

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
 
Ad

Advertisements

C

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top