Index-Match Closest Value

CzechCzar

New member
Joined
Sep 26, 2013
Messages
2
Reaction score
0
Points
0
Hi everybody.


I am dealing with a rather complex equation, and could use some help. Most of the cells in the attached spreadsheet are blank, because the data are confidential. The relevant columns are highlighted.


Basically, I am trying to use index match to find geographic salary differentials. Depending upon what city an employee is based in, and what his salary is nearest to, he will have a different geographic salary differential. Based upon the city and salary data in the first tab, I want to pull in the percentage salary adjustment in the second tab. I want to use straight rounding, so a salary of $34,999.99 will go to $30,000, and a salary of $35,000 will go to $40,000.


I have an index match formula, that seems to work, in cell AG1 of Employee data tab. It won't copy down for some reason. Can anybody help me out? I don't know why this would be.


Thanks!

View attachment Compensation Analysis - Forum.xlsx
 

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good afternoon,

i apologize for the length of the formula, but I opted for length since I'm short on time. Basically the core of this formula indexes the value based on an mround of the salary to the nearest 10,000. There are two iferrors that will return the high or the low if the salary is outside of the spectrum.

=IFERROR(IFERROR(INDEX('Geographic Adjustments'!E:L,MATCH(G3,'Geographic Adjustments'!B:B,0),MATCH(MROUND(V3,10000),'Geographic Adjustments'!$E$1:$L$1,0)),INDEX('Geographic Adjustments'!E:L,MATCH(G3,'Geographic Adjustments'!B:B,0),MATCH(MROUND(V3,10000),'Geographic Adjustments'!$E$1:$L$1,-1))),INDEX('Geographic Adjustments'!E:L,MATCH(G3,'Geographic Adjustments'!B:B,0),MATCH(MROUND(V3,10000),'Geographic Adjustments'!$E$1:$L$1,1)))


Hope this helps,
 

CzechCzar

New member
Joined
Sep 26, 2013
Messages
2
Reaction score
0
Points
0
Please do not apologize! You have solved my problem!!! THANK YOU!! You ---> :humble:
 
Top