Using results of lookup in RANK formula

[Brian_Badongi]

Hello everyone,

Long time reader, first time poster here.

I'm trying to do a piece of work that involves incremental drift for salaries and I'm having an issue I could use some help with.

We have 12 pay bands running 1 - 7, 8a, 8b, 8c, 8d and 9. Each pay band has a number of salaries associated with it that vary in number.

As excel prohibits the use of numbers as the name for ranges (for obvious reasons), I have assigned a letter to each of the payscales, a - m (excluding c).

I've created a validation range for the 12 pay bands and an indirect validation range for the salaries associated with each pay band (which works beautifully for the purpose).

What I am trying to do is determine where in the list of available salaries the one selected falls to see what the next salary will be following pay progression, or if they have reched the top.

My method was to use rank(M36,a,1) (where M36 contains the indirect validation range and "a" corresponds to the named range for pay band 1), which works great, except a is a fixed reference and I need to to vary depending on the pay band selected in cell M35.

I have tried using a lookup in the rank forumla, excel does not like this. I have used a lookup in a different cell, excel returns a #N/A in the rank formula as it does not recognise it as a letter.

I have tried using a series of nested ifs to refurn the letter corresponding to the band, same result.

Is there anything I can do to use a dynamic output that is not user selected in the rank fomula corresponding to the named range?

Thanks,

BB

 

[Brian_Badongi]

Hello,

Solved my own problem (well, a chap from work solved it anyway), it just needed INDIRECT() adding around the lookup output and Robert became related to one of my parents.

BB