[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)
1707897523044.png
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:
1707897736712.png
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
 

Attachments

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

Online statistics

Members online
0
Guests online
19
Total visitors
19

Forum statistics

Threads
311
Messages
1,378
Members
568
Latest member
WilliamHon
Top