[Solved] Formula to read dataset for subsequent year salary increases for multiple employees

[jdb751012]

Hello,

I'm trying to find the best formula to accurately input salaries for employees based on years of service now and going forward. I have the pay scale for future years but need to know how to link employees' increasing years of service to the proper future pay scale information. Thanks for the help!

 

[Yousuf Shovon]

I'm trying to find the best formula to accurately input salaries for employees based on years of service now and going forward. I have the pay scale for future years but need to know how to link employees' increasing years of service to the proper future pay scale information.

Hello JDB751012,
Welcome to ExcelDemy forum! Thanks for sharing your experience with us.
Here are some formulas you can use to read dataset for subsequent year salary increases for multiple employees:
INDEX-MATCH function:
Use this formula in E2:

=INDEX('Future Payscale'!$A$1:$F$34, MATCH(E$1-2024+$B2, 'Future Payscale'!$A$1:$A$34, 0), COLUMN()-3)

This formula will dynamically find the matching value in the 'Future Payscale' range based on the lookup value in E$1-2024+$B2, and it will work even if the data is moved to another spreadsheet. COLUMN()-3 is used to specify which column to return the value from in the data array ('Future Payscale'!$A$1:$F$34). Adjusting by -2 because we are starting from the second column of the data array, and the INDEX function uses a 1-based index.

LET function:
For more flexibility, use this function in E2:

=LET(a,FILTER(B2:B100,B2:B100<>""),b,E1:I1,c,'Future Payscale'!B2:F100,d,'Future Payscale'!A2:A100,e,'Future Payscale'!B1:F1,MAKEARRAY(ROWS(a),COLUMNS(b),LAMBDA(x,y,INDEX(c,MATCH(INDEX(a,x)-2024+INDEX(b,y),d,0),MATCH(INDEX(b,y)&"*",e,0)))))

Same output:

Note: The LET function is only applicable for the Microsoft 365 version.
You can also use VLOOKUP function for this task. Read more about the related function here:
Most Useful and Advanced Excel Functions List
I am attaching the result file here. Thank you.

Regards,
Yousuf Shovon