Pick the next "Step" when matching two variables

[bema2001]

I'm having issues with a formula that needs to validate two variables (pay group and pay grade) and then return the value (next pay grade) in the row.

The formula works perfectly when the variables are actually in the cell, but not when the variable is a result of another formula that returns a value.

Formula: =INDEX($AG$3:$BD$8,MATCH(W25,$AF$3:$AF$8,0),MATCH(X25,$AG$2:$BD$2,0))

The INDEX is all the rows/columns of data that have the pay grades

The first MATCH is looking for the Pay Group the employee is in in the column to the left of the INDEX

The second MATCH is looking for the current Pay Grade in the row above the INDEX

The result is supposed to return the next Pay Grade.

E.G.  Employee is in Pay Group "Admin" and is currently at Pay Grade 2, the next step would be Pay Grade 3.

As I said above, the test formula works perfectly on my mock up data and the Pay Grade is the actual number.

When I put the formula in the actual data set tab and the Pay Grade is the result/value of another INDEX/MATCH formula it won't work unless I overwrite the formula with the result - which defeats the purpose of setting up the workbook automation.

I considered an IF/THEN but there are 6 Pay Groups and 26 Pay Grades, some of which are just numbers and some of which are numbers with letter(s).

I also thought about CHOOSE but can't figure out how to make that happen either :(

any insight on how to change the formula or create a CHOOSE formula would be appreciated!

cathy

[#2]

@bema2001 

in the infamous words of Emily Litella....Never Mind....

I know, kinda sad answering my own question not 5 minutes after I posted it :).

I spent the better part of 2 hours on it yesterday, beating my head on the desk when I couldn't get it to work.

Right after I sent the question I had a light bulb moment and tried flipping the INDEX layout to have the Pay Group in the column headers and the current Pay Grade in the column to the left of the INDEX and the next step Pay Grades in columns rather than rows.

and I'll be darned if the formula didn't work as expected...oy vey.... sorry to have bothered you but if someone else stumbles across this and it helps you then YEAH!