Combining index and hlookup?

I have created and named a table with salaries based on years of experience and education.

I am having trouble writing a function to search for a specific amount and result in the the cell below that specific amount.

So people are moving up by one year on the schedule I would like to use their current salary to lookup and display their salary for next year.

I am confused on how to incorporate the different functions I am assuming an index and a hlookup...

any help would be greatly appreciate

I have attached a sample worksheet

Thanks again

So where exactly is the number going to come from? What should populate in C5 and based on what?

Try:

Please Login or Register  to view this content.

copied down

I receive a #Value error....

That was my fault the table had spaces in each cell. After I converted all of the cells to numbers a value was given.

Unfortunately it gave the same value for each salary.

I have attached an updated file

Thanks again

You seem to have pasted the same formula in all the cells instead of just copying it down from the first cell.. the references will then get updated.

See attached.

My mistake... forgot to make 'Salary scale'!C5 absolute:

Try in C5, copied down:

Please Login or Register  to view this content.

but this will give a blank or REF error if the lookup value is the last value in the column... ie there is no "next largest" salary... what do you want returned in that case, the lookup salary?

If so, then try:

Please Login or Register  to view this content.

Another Alternate formula:

Maybe shorter and more efficient... but just as complex

Please Login or Register  to view this content.