I have a rate (i.e., a salary rate) in cell P8 in sheet A, and I want to find the nearest midpoint (another salary rate - column F shown below) from a salary structure in sheet B. Ultimately, I want the formula to return a grade level from the salary structure.
The challenge is that I have two separate salary structures, and depending on a specific criteria (i.e., location) I need the formula to look from the correct structure. I have been trying the INDEX & MATCH function, but am spinning my wheels. Location is in column J of sheet A and is listed as structure "A" or "B"
So for example, if job 1 has a market value of $47,650, the formula would return a value of 2 if the job was from structure 1 or some other value of it look at structure 2.
Any thoughts on how to accomplish this?
The challenge is that I have two separate salary structures, and depending on a specific criteria (i.e., location) I need the formula to look from the correct structure. I have been trying the INDEX & MATCH function, but am spinning my wheels. Location is in column J of sheet A and is listed as structure "A" or "B"
So for example, if job 1 has a market value of $47,650, the formula would return a value of 2 if the job was from structure 1 or some other value of it look at structure 2.
Any thoughts on how to accomplish this?