vlookup sorting issue with Column Index Number

[blevanto]

I'm using a vlookup function for one of my excel tools at work. I built it to find a relevant salary value in a salary table based upon references from within the worksheet. The salary table is composed of Grades, which are in the left hand column, and Steps which are in the top row. An employee's salary is determined by which Step they've achieved within their specific Grade.

The function works like this: Suppose that an employee's basic information is in row 10. Their Grade is in Cell C10 and their Step is in cell D10 (both numeric). The salary table is in Sheet2, A1:G16. The function for this employee looks like this:

=vlookup(C10, "Sheet2"!A1:G16, (D10+1), False)

This function works properly, referencing the Grade level and Step value (which I had to add 1 to in order to make coincide with the column reference number). The problem is that when I try to sort the data, which is a useful functionality of the tool, the references within the function don't sort properly. The reference value (C10) sorts correctly, but the column reference number (D10) remains static. For example, if I resort the data and this same employee ends up in column 4, the new function would be:

=vlookup(C4, "Sheet2"!A1:G16, (D10+1), False)

Any suggestions?

 

[Aladin Akyurek]

Care to post a small sample along with the lookup values and desired results?

 

[blevanto]

I actually think I figured it out this morning:

Last night I wrote that post from home, so I was creating the formula from memory. When I looked at it this morning, I saw that I had inserted the cell references by clicking in the required cells and ranges. Since the lookup range was in Sheet2, the worksheet denoted that I'd returned to Sheet1 for the column index number. The forumla actually looked like this:

=VLOOKUP(D15,Sheet2!$A$2:$K$16,Sheet1!E15+1,FALSE)

For lack of anything else to try, I eliminated "Sheet1" from the forumla and it became:

=VLOOKUP(D15,Sheet1!$A$2:$K$16,E15+1,FALSE)

When I sort it now, the references appear to sort correctly. I will certainly return here if I have more problems with this formula.

Thanks.