Index-Match Closest Value

Hello all,

I have a spreadsheet with two worksheets. The first is a list of employee data, with a unique employee in each row, city, and salary. There is a blank column to the right, for geographic differential (cities can be more or less expensive to live in.) The cities are standardized across the company, so they can work as a lookup value.

The second is a list of cities, and the different geographic salary multipliers in each. The geographic multipliers are different for different salaries. In Atlanta, for instance, people making around $20k see pay increased by 4.3%. and people making $90k see a lower differential of 1.0%. I should note that differentials can be negative as well. In the attached spreadsheet, the differentials should be 4.6% and -4.0%, respectively.

What I want to do is find the bracket that each employee's salary is closest to, using a standard mathematical round, and return the percentage by which the salary should be increased or decreased. I know that I need arrays, index, and match, but this is the most complicated formula I have ever built and it's making my head spin. Any help is, as always, much appreciated!

Excel Help.xlsx

When I try to open your attachment Excel tells me it is protected. Unprotect it and then re-attach.

Pete

Very sorry about that. Try now.Excel Help2.xlsx

Put this formula in G3 of the Employee data sheet:

=INDEX('Geographic Adjustments'!$B$2:$I$3,MATCH(E3,'Geographic Adjustments'!A$2:A$3,0),MATCH(F3,'Geographic Adjustments'!B$1:I$1)+IF(F3-INDEX('Geographic Adjustments'!$B$1:$I$1,MATCH(F3,'Geographic Adjustments'!B$1:I$1))>INDEX('Geographic Adjustments'!$B$1:$I$1,MATCH(F3,'Geographic Adjustments'!B$1:I$1)+1)-F3,1,0))

then copy down. You might need to adjust the ranges which are emboldened in your real data file.

Hope this helps.

Pete

You, sir, are a genius. My sincerest gratitude. Now I just have to figure out how you did it!

Well, the adjustment is made in the second half of the formula, from the IF function - it checks to see which salary is closest to the To-be Salary, and then adds one to the second MATCH value if required.

Hope this helps.

Pete