Hi! Sorry if this is the wrong forum but I've been trying to get this question answered for a while and haven't had any luck so far. I work in payroll for a school district. For substitute teachers we receive a file each pay period that list the days they worked I then download another file that contains their rate of pay (depends on number of years, etc). Currently I just manually input each subs pay rate. What formula can I use to get the original file to read the pay rate file (RetRate to RetRate)? Both files contain the employees ID number. I've tried just a basic IF formula but it only ever works on the first name. Below is a sample of the 2 worksheets. Any help is greatly appreciated!
Sub File:
| Paycd | RetRate | SubID | Name | Code | Days | Date | Org_Acct | New Account |
| 201 | 10035 | 10035 - BILODEAU, PHYLLIS FOR: 10628 - MAHER, THERESA | 100 - 001 | 0.5 | 4/4/2018 | 2111101107 61330 | ||
| 201 | 10035 | 10035 - BILODEAU, PHYLLIS FOR: 10628 - MAHER, THERESA | 100 - 001 | 0.5 | 4/5/2018 | 2111101107 61330 | ||
| 201 | 10035 | 10035 - BILODEAU, PHYLLIS FOR: 10628 - MAHER, THERESA | 100 - 001 | 0.5 | 4/6/2018 | 2111101107 61330 | ||
| 201 | 10035 | 10035 - BILODEAU, PHYLLIS FOR: 10628 - MAHER, THERESA | 800 - 011 | 0.5 | 4/9/2018 | 2111101107 61330 | ||
| 201 | 10035 | 10035 - BILODEAU, PHYLLIS FOR: 10628 - MAHER, THERESA | 800 - 011 | 0.5 | 4/10/2018 | 2111101107 61330 | ||
| 201 | 10035 | 10035 - BILODEAU, PHYLLIS FOR: 10628 - MAHER, THERESA | 800 - 011 | 0.5 | 4/11/2018 | 2111101107 61330 | ||
| 201 | 10035 | 10035 - BILODEAU, PHYLLIS FOR: 10628 - MAHER, THERESA | 800 - 011 | 0.5 | 4/12/2018 | 2111101107 61330 | ||
| 201 | 10035 | 10035 - BILODEAU, PHYLLIS FOR: 10628 - MAHER, THERESA | 800 - 011 | 0.5 | 4/13/2018 | 2111101107 61330 | ||
| 201 | 10035 | 10035 - BILODEAU, PHYLLIS FOR: 10628 - MAHER, THERESA | 800 - 011 | 0.5 | 4/16/2018 | 2111101107 61330 | ||
| 10039 | 10039 - HANNAN, ROBIN FOR: - Open Teacher Position | 900 - 001 | 1 | 4/4/2018 | 2111101107 61320 | |||
| 10039 | 10039 - HANNAN, ROBIN FOR: - Open Teacher Position | 900 - 002 | 1 | 4/9/2018 | 2221330000 61220 | |||
| 10039 | 10039 - HANNAN, ROBIN FOR: - Open Teacher Position | 900 - 001 | 1 | 4/11/2018 | 2111101107 61320 | |||
| 10039 | 10039 - HANNAN, ROBIN FOR: 11861 - LUEDKE, KARLA | 700 - 062 | 0.5 | 4/12/2018 | 2111153000 61220 | |||
| 10039 | 10039 - HANNAN, ROBIN FOR: 16542 - EKSTROM, CHRISTINA | 100 - 001 | 1 | 4/5/2018 | 2111101107 61330 | |||
| 10039 | 10039 - HANNAN, ROBIN FOR: 19975 - HUFFMAN, COURTNEY | 700 - 062 | 0.5 | 4/12/2018 | 2111153000 61220 | |||
| 201 | 10100 | 10100 - DICARLO, DEBRA FOR: 18824 - JOHNSON, RYAN | 700 - 015 | 1 | 4/11/2018 | 2221301107 61220 | ||
| 201 | 10100 | 10100 - DICARLO, DEBRA FOR: 22482 - SIDES, SARAH | 700 - 059 | 0.5 | 4/5/2018 | 2127101322 61220 |
Pay Rate File:
ID Name RetRate # Position
| 10035 | BILODEAU, PHYLLIS D | 105 | 100510080 | SUB TEACHER |
| 10039 | HANNAN, ROBIN RENEE | 90 | 100510080 | SUB TEACHER |
| 10045 | COCHRAN, SUSAN F | 90 | 100510080 | SUB TEACHER |
| 10094 | GANG, JOYCE D | 105 | 100510080 | SUB TEACHER |
| 10100 | DICARLO, DEBRA J | 105 | 100510080 | SUB TEACHER |
| 10105 | STUERTZ, DANAE D | 100 | 100510080 | SUB TEACHER |
| 10126 | KELLER, DAVID WALTER | 90 | 100510080 | SUB TEACHER |
| 10141 | THIELBAR, JANET ANN | 100 | 100510080 | SUB TEACHER |
| 10150 | LAMARCHE, LINDSAY H | 105 | 100510080 | SUB TEACHER |
| 10152 | LANDUYT, NANCY T | 105 | 100510080 | SUB TEACHER |
| 10157 | GRABBE, EDITH M | 100 | 100510080 | SUB TEACHER |
| 10183 | WHITE, MICHELE CHRISTINE | 90 | 100510080 | SUB TEACHER |
| 10187 | LAMUNION, BEVERLY J | 105 | 100510080 | SUB TEACHER |
| 10193 | SMITH, PENNY | 105 | 100510080 | SUB TEACHER |
Hello Samantha,
It is not a difficult question, but how well things works depends on which version of Excel you are running. Best option would of course to have the data in same file, in different sheets.
With older versions of Excel you need to have both files opened, with Excel 2016 I notice it works fine either way, opened or closed.
I have copied your sample data, the sub file data into Book1.xlsx and the pay rate data into Book2.xlsx.
You can see what I did in the attached files.
The formula is following:
=VLOOKUP(C2;[Book2.xlsx]Sheet1!$A$2:$E$15;3;FALSE)
Let's see if the sample files gives the push to right direction.
Br,
Anders
Thank you so much that did the trick! I knew it was something semi-simple I just couldn't narrow it down to the right formula.
Thanks again!!
Hello Samantha,
I am just glad I could be of help.
Have a great evening now.
/Anders
