Help required for Salary Grade calculation and information look up

[ajnabidst]

Dear Gents,

I have attached grading sheet and have used index formula that i have been using before.

Here, i have two situations and am stuck.

Situation 1: In the worksheet there is technical and support column, while i execute the formula -- i wish to read the job family and give right job category accordingly.
I think i am missing some logic, which i need your assistance.

Situation 2: Looking for right salary band considering years of exp., step and job category.

Kindly guide on how to add value for the sheet with the right formula.

Thank you very much for your support and cooperation.

Regards,
ajnabidst
[MOD EDIT] : Use a thread title which will clarify the objective.

 

[ajnabidst]

[Dear Excel Experts,

I have taken unmerged the cells in worksheet, for helping in calculating with the right formula.

Hope with this you can guide me how to do it from here.