How would I go about creating a formula in Excel that would calculate a new wage based on employee status and the years employed?

Answer 1

It sounds like you are trying to do something like this: Select a value for cell A1 if certain conditions are met in cells B1 and C1. Use the formula:

=IF(ISERROR(INDEX(B3:C5, MATCH(G2,A3:A5,0), MATCH(G3,B2:D2,0))),0,INDEX(B3:C5, MATCH(G2,A3:A5,0), MATCH(G3,B2:D2,0)))

Create a table to hold the salaries for years and status. The formula will find the wage that matches both the years and status. Enter the values in the following cells to see an example of how you can do this:

A3 = 1

A4 = 2

A5 = 3

B1 = Wage Table

B2 = A

B3 = 12.50

C2 = B

C4 = 16.75

D2 = C

F1 = Employee

F2 = years

F3 = status

F4 = wage

G2 = 2

G3 = B

G4 = =IF(ISERROR(INDEX(B3:C5, MATCH(G2,A3:A5,0), MATCH(G3,B2:D2,0))),0,INDEX(B3:C5, MATCH(G2,A3:A5,0), MATCH(G3,B2:D2,0)))

NOTE: Change G2 to 1 and G3 to A to verify the formula works.

If you want to get even more professional, assign range names where appropriate and replace the cell references with range names. For example, you can call A3:A5 "years" and change MATCH(G2,A3:A5,0) to MATCH(G2,years,0), etc.