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

#### jdb751012

##### New member
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!

#### Attachments

• Excel Help.xlsx
12.8 KB · Views: 4
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:
Code:
``=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:
Code:
``=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.
Most Useful and Advanced Excel Functions List
I am attaching the result file here. Thank you.

Regards,
Yousuf Shovon

#### Attachments

• Excel Help.xlsx
14.4 KB · Views: 1
Last edited:

Members online
0
Guests online
16
Total visitors
16