Retrieve nearest value and titles from sheet data

I need to retrieve nearest Value from Sheet to based on matching grades and then need to pull column titles of nearest values which are different as per value pulled.

query mentioned in attached file.
I have tried using Index, match, if and vlookup but not able to pull required result. help required.

Thanks for reply
Q1 to Q3 just stands for question, sorry i didn't mentioned that, the values that are put under titles Basic, Stage and Year are the required values and formula is required their, its like a HR sheet to check which staff is having what stage of payroll

Yes Data for Stage XX need to be included as it is actually Year 5
The Data Sheet is basically a Year wise calculation of payroll that is increasing each year with a specific percentage, so the Stage Column is basically Y5 or Year5, and on each Y5 the incremental value % changes for next Y1 or Year1 as per current value of Stage or Y5
Yes Grades will remain constant as these are Departmental wise, They are not going to change

Thanks for the formula dkhanknu

it worked perfectly as required, one thing more i need to confirm as it came up when Glenn Kennedy asked the question, if the grade sorting order is changed then will this formula still work as i was locking the range to match and you made it simple with not locking the range for each row.
INDEX(d,MATCH(A4,Sheet2!$A$3:$A$11,0),MATCH(MIN(ABS(Sheet2!B3:AZ3-C4)),ABS(Sheet2!B3:AZ3-C4),0))
where d = Sheet2!$B$3:$AZ$11.


and i just changed the formula to convert Stage to Y5
IF(LEFT(INDEX(Sheet2!$B$2:$AZ$2,MATCH(MIN(ABS(Sheet2!B3:AZ3-C4)),ABS(Sheet2!B3:AZ3-C4),0)),1)="S","Y5",INDEX(Sheet2!$B$2:$AZ$2,MATCH(MIN(ABS(Sheet2!B3:AZ3-C4)),ABS(Sheet2!B3:AZ3-C4),0)))

and Ctrl+Shift+Enter is required for each formula for array

I understand that AliGW, i'll try to keep that in mind in my future posts, thanks for the reminder.

@dkhanknu question of @Glenn Kennedy was valid as your formula is not valid when Grade value order is changed.

results with different grades same order
Without Sorting Order.JPG

results with same grades
Same Grade.JPG

results with different grades different order
With different order.JPG

so the results are not correct as we are using ABS on same row where grades are matched as a sorted order
need to update match string to get correct results.

I am sorry if I was not clear,
Stage is actually Y5 and at the same time begining of new percentage, so Everytime on Y5 it restarts from Y1 as new pay scale.
So if a staff is reached on stage7 that means that person actually worked for about 35 years that is a separate calculation not included in this post as not require

Thanks for the help, really appreciate it, it is working fine now, it must be the OFFSET that made it work correctly.

for values
=IFERROR(INDEX(Sheet2!$B$3:$AZ$11,MATCH(A4,Sheet2!$A$3:$A$11,0),MATCH(MIN(ABS(OFFSET(INDEX(Sheet2!$A$3:$A$11,MATCH(A4,Sheet2!$A$3:$A$11,0)),,1,,51)-C4)),ABS(OFFSET(INDEX(Sheet2!$A$3:$A$11,MATCH(A4,Sheet2!$A$3:$A$11,0)),,1,,51)-C4),0)),"")

for Year Title
=IFERROR(IF(LEFT(INDEX(Sheet2!$B$2:$AZ$2,MATCH(MIN(ABS(OFFSET(INDEX(Sheet2!$A$3:$A$11,MATCH(A4,Sheet2!$A$3:$A$11,0)),,1,,51)-C4)),ABS(OFFSET(INDEX(Sheet2!$A$3:$A$11,MATCH(A4,Sheet2!$A$3:$A$11,0)),,1,,51)-C4),0)),5)="Stage","Y5",INDEX(Sheet2!$B$2:$AZ$2,MATCH(MIN(ABS(OFFSET(INDEX(Sheet2!$A$3:$A$11,MATCH(A4,Sheet2!$A$3:$A$11,0)),,1,,51)-C4)),ABS(OFFSET(INDEX(Sheet2!$A$3:$A$11,MATCH(A4,Sheet2!$A$3:$A$11,0)),,1,,51)-C4),0))),"")

for Stage Title
=IFERROR("Stage"&INT(MATCH(MIN(ABS(OFFSET(INDEX(Sheet2!$A$3:$A$11,MATCH(A4,Sheet2!$A$3:$A$11,0)),,1,,51)-C4)),ABS(OFFSET(INDEX(Sheet2!$A$3:$A$11,MATCH(A4,Sheet2!$A$3:$A$11,0)),,1,,51)-C4),0)/5)+1,"")