Hi All

I have been trying to solve this but nothing seems to work.

I need to do salary cost forecast for the next five years with different payrise options

I have a formula that finds the value (salary) based on payscale table.

=INDEX(P1.0,MATCH(D57,INDEX(P1.0,,1),0),MATCH($D$55,INDEX(P1.0,1,),0))

where P1.0 is a name range referring to a table in another sheet.


Is there a way to change the name range in formula based on the value in another cell (dropdown box would be perfect but it's not essential).

I tried Indirect function but didn't get far. I have tried something like this:

=INDEX((IF(S37=P1.0,P1.0,0)),MATCH(D57,INDEX(P1.0,,1),0),MATCH($D$55,INDEX(P1.0,1,),0))

but get the REF

Any ideas?

Hopefully you will understand what I mean.

Mike