Excel
 

creating an employee salary table. Several pieces of data to consider: the
step (number of years) and the level of education to date. The step is
listed vertically and the degree(s) are listed horizontally. How can I enter
a formula to find the intersection of the two?
Last_Name First_Name Salary_Column Old Step New Step Salary
Jones Sam BA+20 15 16 should be 36,541

Table of Data:
Years BA BA+10 BA+20 BA+30 MA MA+10 MA+20
16 35,136 35,839 36,541 37,244 38,123 39,001 39,879

Excel
 

This should do it. I set up my sheet to look almost exactly like the data you
posted.

=INDEX(OFFSET($A$5:$A$25, 0,MATCH(C2, $B$4:$H$4, FALSE)),MATCH(E2,
$A$5:$A$25, FALSE))

Years are in $A$5:$A$25
Education is in $B$4:$H$4
--
HTH...

Jim Thomlinson


"dizzynumber" wrote:

creating an employee salary table. Several pieces of data to consider: the
step (number of years) and the level of education to date. The step is
listed vertically and the degree(s) are listed horizontally. How can I enter
a formula to find the intersection of the two?
Last_Name First_Name Salary_Column Old Step New Step Salary
Jones Sam BA+20 15 16 should be 36,541

Table of Data:
Years BA BA+10 BA+20 BA+30 MA MA+10 MA+20
16 35,136 35,839 36,541 37,244 38,123 39,001 39,879

Excel
 

Or perhaps a bit simpler (and more efficient to boot)

=VLOOKUP(E2, $A$5:$H$25, MATCH(C2, $B$4:$H$4, FALSE)+1, FALSE)
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

This should do it. I set up my sheet to look almost exactly like the data you
posted.

=INDEX(OFFSET($A$5:$A$25, 0,MATCH(C2, $B$4:$H$4, FALSE)),MATCH(E2,
$A$5:$A$25, FALSE))

Years are in $A$5:$A$25
Education is in $B$4:$H$4
--
HTH...

Jim Thomlinson


"dizzynumber" wrote:

creating an employee salary table. Several pieces of data to consider: the
step (number of years) and the level of education to date. The step is
listed vertically and the degree(s) are listed horizontally. How can I enter
a formula to find the intersection of the two?
Last_Name First_Name Salary_Column Old Step New Step Salary
Jones Sam BA+20 15 16 should be 36,541

Table of Data:
Years BA BA+10 BA+20 BA+30 MA MA+10 MA+20
16 35,136 35,839 36,541 37,244 38,123 39,001 39,879