Compensation / Salary Data Lookup

[stewstew03]

Hi,

I'd like to pull in the appropriate salary data based on a person's current salary. The goal is to pull that person's salary up to the next step in the salary structure. So if they are currently making $50,000, then they should be making $50,980 (Step 7 in the appropriate salary structure). Any assistance would be appreciated.

Job Title	Structure	Grade	Concatenate	Current Salary	New Salary
Accountant	Primary	F	Primary-F	$50,000	?	<---$50,980
Nurse	Medical	A	Medical-A	$67,800	?	<---$69,301
Business Analyst	Tech	C	Tech-C	$76,500	?	<---Max

<tbody>
</tbody><colgroup><col><col span="2"><col><col><col><col></colgroup>

<tbody>
</tbody>

Here are the lookup data:

Pay Grade	Step 1	Step 2	Step 3	Step 4	Step 5	Step 6	Step 7	Step 8	Step 9	Step 10	Step 11	Step 12
Primary-F	$43,812	$45,006	$46,201	$47,396	$48,590	$49,785	$50,980	$52,174	$53,369	$54,564	$55,759	$56,953
Medical-A	$65,717	$67,509	$69,301	$71,093	$72,886	$74,678	$76,470	$78,262	$80,054	$81,846	$83,638	$85,430
Tech-C	$56,955	$58,508	$60,061	$61,614	$63,167	$64,721	$66,274	$67,827	$69,380	$70,933	$72,486	$74,039

<tbody>
</tbody>

 

[stewstew03]

Any traction on this...? I think I need an Index/Match formula, but I'm not exactly sure how to write it...

 

[cornflakegirl]

Welcome to the board.

You can use the MATCH function with a final argument of 1 to pull the position of the step that is below the current salary - then add 1 to get the position of the step above. Combine that with INDEX to pull the actual value. The best way to pull the lookup range for INDEX to work on will depend on how your real data looks, but probably use INDEX and MATCH there too.

 

[BrianMH]

Here is an example. I've had to reverse the order of your lookup table to work with match.

Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Accountant

Primary

F

Primary-F

$50,000

$50,980

*

*

*

*

*

*

*

2

Nurse

Medical

A

Medical-A

$67,800

$69,301

*

*

*

*

*

*

*

3

Business Analyst

Tech

C

Tech-C

$76,500

Max

*

*

*

*

*

*

*

4

*

*

*

*

*

*

*

*

*

*

*

*

*

5

*

*

*

*

*

*

*

*

*

*

*

*

*

6

Pay Grade

Step 12

Step 11

Step 10

Step 9

Step 8

Step 7

Step 6

Step 5

Step 4

Step 3

Step 2

Step 1

7

Primary-F

$56,953

$55,759

$54,564

$53,369

$52,174

$50,980

$49,785

$48,590

$47,396

$46,201

$45,006

$43,812

8

Medical-A

$85,430

$83,638

$81,846

$80,054

$78,262

$76,470

$74,678

$72,886

$71,093

$69,301

$67,509

$65,717

9

Tech-C

$74,039

$72,486

$70,933

$69,380

$67,827

$66,274

$64,721

$63,167

$61,614

$60,061

$58,508

$56,955

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:132px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F1 =IFERROR(INDEX(OFFSET($B$6:$M$6,MATCH(D1,$A$7:$A$9,0),0),MATCH(E1,OFFSET($B$6:$M$6,MATCH(D1,$A$7:$A$9,0),0),-1)),0) F2 =IFERROR(INDEX(OFFSET($B$6:$M$6,MATCH(D2,$A$7:$A$9,0),0),MATCH(E2,OFFSET($B$6:$M$6,MATCH(D2,$A$7:$A$9,0),0),-1)),0) F3 =IFERROR(INDEX(OFFSET($B$6:$M$6,MATCH(D3,$A$7:$A$9,0),0),MATCH(E3,OFFSET($B$6:$M$6,MATCH(D3,$A$7:$A$9,0),0),-1)),"Max") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[stewstew03]

Thanks -- this should be helpful. Curious as to why the salary steps need to be reversed...?

 

[BrianMH]

Because to use match to find the value that is higher than the value you are searching it has to be in descending order.

Actually it can be done by adding an offset of 1. Keep in mind if the current salary is under what your minimum in the table is it will also error and return max.

Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Job Title

Structure

Grade

Concatenate

Current Salary

New Salary

*

*

*

*

*

*

*

2

Accountant

Primary

F

Primary-F

$50,000

$50,980

<---$50,980

*

*

*

*

*

*

3

Nurse

Medical

A

Medical-A

$67,800

$69,301

<---$69,301

*

*

*

*

*

*

4

Business Analyst

Tech

C

Tech-C

$76,500

Max

<---Max

*

*

*

*

*

*

5

*

*

*

*

*

*

*

*

*

*

*

*

*

6

Pay Grade

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Step 8

Step 9

Step 10

Step 11

Step 12

7

Primary-F

$43,812

$45,006

$46,201

$47,396

$48,590

$49,785

$50,980

$52,174

$53,369

$54,564

$55,759

$56,953

8

Medical-A

$65,717

$67,509

$69,301

$71,093

$72,886

$74,678

$76,470

$78,262

$80,054

$81,846

$83,638

$85,430

9

Tech-C

$56,955

$58,508

$60,061

$61,614

$63,167

$64,721

$66,274

$67,827

$69,380

$70,933

$72,486

$74,039

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula F2 =IFERROR(INDEX(OFFSET($B$6:$M$6,MATCH(D2,$A$7:$A$9,0),0),MATCH(E2,OFFSET($B$6:$M$6,MATCH(D2,$A$7:$A$9,0),0),1)+1),"Max") F3 =IFERROR(INDEX(OFFSET($B$6:$M$6,MATCH(D3,$A$7:$A$9,0),0),MATCH(E3,OFFSET($B$6:$M$6,MATCH(D3,$A$7:$A$9,0),0),1)+1),"Max") F4 =IFERROR(INDEX(OFFSET($B$6:$M$6,MATCH(D4,$A$7:$A$9,0),0),MATCH(E4,OFFSET($B$6:$M$6,MATCH(D4,$A$7:$A$9,0),0),1)+1),"Max") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[stewstew03]

This is brilliant, Brian. Thank you.

How might I fix the issue if there is a current salary that is below the minimum of the range? For example, if the Business Analyst in the above example is making $55,000 and needs to be brought up to Step 1 to earn $56,955...?

 

[BrianMH]

I'd actually do that seperately. Do a vlookup returning the first column value and test that against the salary they are currently on. Then you know if they need an increase. You could put this all into 1 formula but it gets prohibitively long and processor costly.

 

[stewstew03]

As a follow up to this... (as I continue to work through the data). What if I want to return the Minimum or Maximum of the range if the actual salary rate is not in the range? For example, again using the data above... for a Business Analyst who earns $55,000, the formula would return $56,955, while for a Business Analyst who earns $80,000, the formula would return $74,039. As always, I appreciate your help!

 

[BrianMH]

If you put in 80000 it would return Max since it is above the top threshold. The problem is that currently if it is under the minimum it will also return max. That's where you do a vlookup and return the min column for that structure and test if the current salary is below that. You could do that separately or you build it into this formula with an if statement but thats going to be a long formula.