Index-Match Closest Value

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

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,

Please do not apologize! You have solved my problem!!! THANK YOU!! You ---> :humble:

Excellent. Thanks and you're very welcome