Reconcile the timesheet against the master — bulletproof
The timesheet export has trailing spaces on some names from the legacy HRIS, AND one row is a brand-new hire who isn't in the master file yet. Build a single formula that cleans the lookup value, pulls the salary, and degrades to '—' when there's no match — so the payroll lead can read the column at a glance.
Reconcile timesheet against master payroll
Practice using VLOOKUP to solve a real hr & operations problem. By the end, you’ll know when to reach for it and how to structure the arguments correctly.
Payroll runs in 45 minutes. The timesheet export has trailing spaces on some names from the legacy HRIS, and one row is a brand-new hire who isn't in the master payroll file yet. Pull each timesheet row's salary from the master table; show "—" where there's no match so the dash is obvious to the payroll lead.
In E2, return the salary for the timesheet name in D2. Wrap D2 with TRIM so trailing spaces don't break exact-match, wrap the whole VLOOKUP in IFERROR with '—' as the fallback, and drag the formula down through E6.
VLOOKUP searches the first column of a table for a value, then returns a value from a column you specify to the right. It's the most common lookup pattern in Excel: 'find this ID, return the matching name'. The fourth argument controls the match type, and almost always wants to be FALSE for exact match.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Try the formula first. Hints cost CP for a reason.