Two Dimensional Lookup

Hi

I am using Excel 2003. In my attached workbook I have two worksheets titled “Parameter” which are Basic Salary (numbers vertically under each grades) and “Grade” (M-1 to S-6 horizontally).

In other worksheet “Calculation” I have staff database. My formula should look at “Propose Number of Increment” in column E, locate the present basic salary under Present grade in worksheet “Parameter” and pickup proposed basic based on the number of increments.

In column F, I have given expected result “Proposed Basic SHOUL BE”.

I have been trying to built up of formula in column G which may please be ignored.


Swastik

Or even this horrible looking formula which seems to work

=INDEX(Parameter!$B$2:$O$117,MATCH(D3,INDIRECT("Parameter!"&ADDRESS(2,MATCH(C3,Parameter!$A$1:$O$1,0))&":"&ADDRESS(117,MATCH(C3,Parameter!$A$1:$O$1,0))),0)+3,MATCH(C3,Parameter!$B$1:$O$1,0))

Another alternative, and a non-volatile approach (ie no OFFSET/INDIRECT) - for more info. on Volatile functions and effects thereof see the link in my sig to Charles Williams' site.

=INDEX(Parameter!$B$1:$O$117,MATCH($D3,INDEX(Parameter!$B$1:$O$117,0,MATCH($C3,Parameter!$B$1:$O$1,0)))+$E3,MATCH($C3,Parameter!$B$1:$O$1,0))

There will be other ways (as already illustrated)