Even with advanced technology for managing workforce planning and enterprise resources, many employers still rely on spreadsheets to complete their day-to-day tasks. According to CX Today, a recent study shows that 51% of employers state that they still use spreadsheets for necessary enterprise and workforce management tasks. In many cases, this may be unavoidable as reports may come from multiple systems and need to be cross-referenced for accuracy.
Whether you need to reconcile payroll, organize your employee records, or verify your financial information, spreadsheet solutions, such as Microsoft’s Excel, can be useful alongside Human Capital Management (HCM) Software or ERP Software systems. However, comparing data from different systems can become complicated when each report contains different information or is organized differently. Excel functions such as VLOOKUP and XLOOKUP can help match related information between your datasheets.
So, which function makes the most sense when managing your payroll and ERP data?
This article will help you master data management in Excel by explaining the basics of Excel’s VLOOKUP and XLOOKUP functions and how each can be used when managing data from HCM and ERP systems.
VLOOKUP is one of Excel’s most widely used functions for finding and matching information between datasets. The “V” stands for vertical, as the function searches for a value in the first column of a selected range and returns related information from another column in the same row.
Suppose you’re migrating payroll data via an export from your HCM system and want to compare each employee’s pay rates against a separate employee data spreadsheet. Both files contain employee ID’s, but the payroll export does not include the employee’s department.
Using VLOOKUP, you can search for each employee ID in the existing employee dataset and return the corresponding department into the payroll spreadsheet. This allows you to bring the related information into one report and quickly identify records that may need further review.
A basic formula could look like this:
=VLOOKUP(A2,EmployeeData!A:D,4,FALSE)
In the formula above, Excel searches for the employee ID in column A of the “EmployeeData” sheet and returns the corresponding value from column D.
XLOOKUP is a newer, more flexible lookup function that can perform many of the same tasks as VLOOKUP while directly addressing some of its limitations. Unlike VLOOKUP, XLOOKUP allows you to specify the exact lookup range and return range separately, meaning the information you want to return does not need to be positioned exactly to the right of your lookup column.
XLOOKUP also uses exact matching by default and can return custom results when a match is not found. These features make it particularly useful when working with HCM and ERP export data that vary radically in structure or contain records that may not match between systems.
Currently, the XLOOKUP function is available in Microsoft 365 and Excel version 2021 or later.
Let’s go back to the original example with the payroll comparison. However, this time the employee ID is located in a different column, column D, while the department information you need to match is in column B.
With VLOOKUP, this setup would create a problem because VLOOKUP requires the lookup column to be the first column in the selected range and can only return information from the columns to the right of it.
Using XLOOKUP, you won’t have to worry about this limitation, as you can tell Excel exactly where to find the employee ID and exactly where to retrieve the department:
=XLOOKUP(A2,EmployeeData!D:D,EmployeeData!B:B,”Not Found”)
In the formula above, Excel searches for the employee ID in column D and returns the corresponding department from column B. If an employee ID does not appear in the employee dataset, the function will return “Not Found” instead of displaying an error.
With XLOOKUP, the flexibility between datasets can be especially useful when combining reports from HCM and ERP systems, where exported data may not always use the same column or structure.
While XLOOKUP is a more flexible option for many data-management tasks, VLOOKUP may still be worth using depending on certain situations.
Here are reasons why an employer would still want to use VLOOKUP instead:
Ultimately, outside of these reasons, XLOOKUP comes out on top as the better function to use for a more thorough and simple process. Its ability to look up data in either direction of a dataset, use exact matches by default, and handle missing records more clearly can make it easier to maintain as you manage and update data.
That said, many organizations still widely use and support the VLOOKUP function. If an existing process works reliably, there may not be a reason to change your method.
When the time comes each period to reconcile payroll, general ledger, and employee time data, you’ll want to be fully prepared with the right skills to ensure your organization doesn’t make an unnecessary mistake. With many organizations still maintaining core workforce and enterprise planning data in spreadsheets despite software capabilities available, it’s important to know not only how to use the lookup functions, but also which one makes the most sense for your task at hand.
With the advent of XLOOKUP on newer Microsoft platforms, employers may want to consider upgrading their skills and learning additional Excel tips to make data imports and reconciliation much easier when working with sensitive employee and financial data. Or, if your organization is well equipped to handle projects with VLOOKUP or you're still using older Excel versions, it might be okay to stick with the original function.
Considering skills like the lookup functions is one of many kinds of topics we touch on in the 2026 edition of the Excel Masterclass SmartTalks webinar.
The latest session will provide a walkthrough of these essential Excel tips to help you clean up out-of-control spreadsheets while improving your experience using the platform:
Regardless of your knowledge level, establishing an understanding of Excel’s latest and core features can help push for more efficiency across your organization, especially for those who utilize Excel nearly every day.
Save Your Seat For The Webinar ⇒