How to Calculate Pay with VLOOKUP and a PivotTable in Excel

Step-by-step

The example: staff are sent to different locations and paid an hourly rate, plus a fixed stipend per location to cover parking and tolls. You need each person’s total pay. For instance, Amelia works 7 hours at Harbor Center at $40 an hour, plus Harbor Center’s $25 stipend.

The data has columns for employee, hours, location and rate, and a small separate table (I2:J5 in the example) listing each location and its stipend.

1. Calculate the basic pay

Insert three columns for the new calculations. Type Pay in E1, then in E2 multiply the rate by the hours: =D2*B2.

2. Turn the data into a table

Go to Home > Format as Table, check the range (A1:G22 in the example) and make sure My table has headers is ticked. Then double-click the fill handle in E2 so the formula fills the whole column.

3. Look up each location’s stipend

Type Stipend in F1, and in F2:

=VLOOKUP(C2,$I$2:$J$5,2,FALSE)

This takes the location in C2, finds it in the stipend table, and returns the value from the table’s second column. The dollar signs keep the table reference fixed as the formula fills down, and FALSE asks for an exact match.

4. Add the final pay

Type Final Pay in G1, and in G2 add the pay and the stipend together. The whole column fills automatically.

5. Total each person’s pay with a PivotTable

People appear on several rows, so summarise them: go to Insert > PivotTable and place it on a new worksheet. Drag the employee names into Rows and Final Pay into Values, then format the totals as currency. You now have exactly what each person should be paid, ready to send to payroll.

Go further

My Excel Dashboard & Data Analysis Masterclass course goes much deeper into lookups, PivotTables and reporting.