![]() |
Newbe help with a lookup
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 |
Newbe help with a lookup
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 |
Newbe help with a lookup
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 |
All times are GMT +1. The time now is 03:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com