Using Formulas to cross check between two data sheets
I have two data sheets for working hours, one that was complete for April, where i included additional data (project numbers and pay rate) and the second for May that was just provided by payroll that doesn't include this currently when extracted from the app.
Is there any formulas i can use to quickly identify which individual has a specific pay charge rate or project number without manually copy and pasting. The data that is in common between the two sheets are employee's first & last name and also the employee code.
Would using =IF + conditional formatting be a good idea to highlight which are which? or is there a more straight forward approach to directly "update" the current months data.
RE: Using Formulas to cross check between two data sheets
Hi Steven,
Thank you for your question to the forum.
Your best approach is to use XLOOKUP to “pull” data across sheets.
It works by looking up a unique ID, matching it in one list and returning relevant info from another list. You’ve got:
- Common key: Employee Code (best choice)
- Or fallback: First + Last Name
The formula to populate the pay rate starts in a cell in row 2 (for example) in the May sheet:
=XLOOKUP(A2, April!A:A, April!C:C) where:
A2 = Employee Code in May
April!A:A = Employee Code in April
April!C:C = Pay rates
Finally, copy this formula down to look up the other ID nos. in order to return their pay rates.
I hope this helps with your problem
Kind regards
Martin
(IT trainer)
Employee CodeNameProject NoPay Rate123John SmithP001£20
Sheet 2 (May – incomplete)
Employee CodeNameProject NoPay Rate123John Smith(blank)(blank)
🔹 Formula to auto-fill Project No
In May sheet:
Excel=XLOOKUP(A2, April!A:A, April!C:C, "")``Show more lines
Where:
A2 = Employee Code in May
April!A:A = Employee Code in April
April!C:C = Project Number column
🔹 Formula for Pay Rate
Excel=XLOOKUP(A2, April!A:A, April!D:D, "")``Show more lines
✅ Drag down — done
✅ No manual copy/paste
✅ Always consistent
