Admins can export various reports from your PraisePal workspace. You might want to add additional fields to those reports to support your internal company processes.
For example, adding employees' Staff ID or Department to the reward redemption report, for internal budget allocation purposes.
To do so, you can use VLOOKUP to merge this information from your employee list obtained via your HR system.
This guide provides step-by-step guide, including a video tutorial and example formulas to help you update your report using Excel.
Step-by-Step Guide
1. Prepare Your Files
Ensure you have the following files ready:
Reward Redemption Report (exported from PraisePal)
Employee List (downloaded from your HR system)
2. Open Both Files in Excel
Open the Reward Redemption Report and the Employee List side by side in Excel.
3. Add New Headers
In the Reward Redemption Report, create new columns for Staff ID, Department, and Entity (or any other additional details you want to include).
4. Use VLOOKUP to Retrieve Employee Info
The VLOOKUP formula follows this structure:
=VLOOKUP(Cell to reference, Table to lookup, Column to pull in, Exact or Approx Match)
Here's how to enter the formula for each additional data field:
Step 1: Apply the Formula for Staff ID
In the first row under the Staff ID column, type:
=VLOOKUP(
Click on the cell containing the Employee Name or ID in the Reward Redemption Report (e.g.,
F2).Type a comma ( , ).
Step 2: Select the Lookup Table
Click on the Employee List tab.
Select the table range containing employee data (e.g.,
$A$2:$F$7to cover all employees).Type a comma ( , ).
Step 3: Specify the Column Number
Count the columns in the Employee List from left to right until you reach the column containing the Staff ID.
Enter that column number (e.g., if Staff ID is in the third column, type
3).Type a comma ( , ).
Step 4: Choose Exact Match
Type FALSE to ensure the formula finds an exact match.
Close the formula with ) and press Enter.
Example:
=VLOOKUP(F2,'[Employee List.xlsx]Sheet1'!$A$2:$D$6,3,FALSE)
5. Autofill the Column
Click on the cell where you entered the formula.
Drag the bottom-right corner down to apply the formula to all rows.
6. Apply the Formula for Department and Entity
Repeat the steps for the Department column, changing the column number in the formula to match the Department's position in the Employee List.
Do the same for Entity.
Example for Department:
=VLOOKUP(F2,'[Employee List.xlsx]Sheet1'!$A$2:$D$6,4,FALSE)
Example for Entity:
=VLOOKUP(F2,'[Employee List.xlsx]Sheet1'!$A$2:$D$6,5,FALSE)
7. Verify and Save
Check for #N/A errors, which may indicate missing data.
Use Excel filters to review and correct any unmatched records.
Save the updated report for your records.
Video Tutorial