Xlookup on two identifiers – Payment type and employee rate combined

I recently had a question from a customer about a payroll situation. Basically she had a list of names/payment types and had to assign the correct total hours to each person and category. The file I am using is fake data (hobbits of course) but the payment types are quite standard.

it’s a bit longer than usual but hopefully if you have this situation you will find it helpful

This is what we covered.

Started with the timesheet

1. Helper Cell – The first thing I did was create a helper cell that combined the full name and payment type. They also had Departments. Because of the position of the helper cell, I need to use an Xlookup with this – not a vlookup
2. List of Unique names – In the Calculations sheet, I used the Unique() function to extract a list of unique names.
3. Pull in the Payment types – I then used the Unique and Transpose function to pull across the list of payment types from the Timesheet – starting in C2 as I wanted to leave space for the total column
4. Create the Xlookup – Starting in C3 I used an Xlookup()  which combined the Employee name and Payment type and used that to match it against the helper cell and entries in the Timesheet. I then copied that across and down because I used F4 to lock the entries.
5. Get the Total – In the Calculations sheet, starting in B3 I then added the Total
6. Colour code entries – I also applied conditional formatting to highlight all the cells that have entries.
7. Check the numbers – As a final check, I entered a cell in B1 which checked that the total for column B in the Calculations sheet was the same as the total for Column D in the Timesheet. The answer should be 0 which I applied conditional formatting to. Green for 0 and red for anything else.*
8. Freeze Panes – I have set up Freeze Panes (Click in C3, click on View | Freeze Panes | Freeze panes) to make it easier to navigate

*Note: if you find the answer isn’t 0 – you can use Conditional formatting (Home | Highlight cells Rules | Equal To) the amount of the difference to help you find it.

You can download the starting file here

You can download the finished file here

You can view the video here