Compare Employee Records and PTO Values in Excel Databases
Question details
Identify employees shared by two databases, find employees missing from either list, and compare their PTO values for differences.

- Product
- Microsoft Excel databases
- Device & OS
- not provided
- Scenario
- An employee audit requires matching records from two worksheet databases by employee identifier and checking PTO balances.
- Observed behavior
- A lookup may return the #N/A error when an employee is not present in the other database, and PTO values need a separate comparison.
Make sure both databases use the same unique employee identifier, such as an employee ID or consistent email address. Confirm that the lookup table starts with the identifier column and that the PTO field is in a known return column.
Use ISNA with VLOOKUP to flag employees missing from the other database
Use this method when one employee list needs to be checked against a second table and you want to distinguish a real #N/A lookup error from text.
The #N/A result returned by VLOOKUP is an Excel error value, not the text string "#NA." Test it with ISNA, or use IFNA to replace it with a readable result.
Place the employee ID to check in cell D2, and make sure the comparison database has employee IDs in column A and the required returned field, such as PTO, within columns A:C.
In an empty result column, enter =IF(ISNA(VLOOKUP(D2,$A:$C,3,FALSE)),"not found","found") and fill the formula down. It returns "not found" when the employee ID in D2 does not exist in the comparison database.
To display the matched PTO value or a missing-record message, enter =IFNA(VLOOKUP(D2,$A:$C,3,FALSE),"not found") in a separate column and fill down.
Select the header row, then use Data > Filter and filter the status column for "not found" to review employees absent from the comparison database.

Compare returned PTO values with the PTO values in the current database
Use this after matching employee IDs to highlight records whose PTO balances differ between the two databases.
Merge employee databases with Power Query for repeatable audits
Use Power Query when the same two databases are compared regularly or when you need a refreshable matched, unmatched, and PTO-difference report.
Compare Employee Lists and PTO Balances in WPS Spreadsheet
WPS Spreadsheet lets you match employee IDs across two tables, return PTO values with lookup formulas, and filter or highlight missing records and balance discrepancies in one audit worksheet.
- 1. Open both employee databases: Open the Excel workbook in WPS Spreadsheet and place the source lists on separate worksheets or in separate table ranges.
- 2. Check employee IDs with a lookup: In a result column, enter =IFNA(VLOOKUP(D2,$A:$C,3,FALSE),"not found") to return a PTO value or flag an employee who is absent.
- 3. Compare PTO amounts: Add a status formula such as =IF(F2="","not found",IF(E2<>F2,"PTO discrepancy","PTO matches")) and copy it down the list.
- 4. Review exceptions with filters: Select the headers and choose Data > Filter, then filter the status column for "not found" or "PTO discrepancy."

Frequently Asked Questions
Why does comparing a VLOOKUP result to "#NA" not work?
VLOOKUP returns #N/A as an error value, not as text. Use ISNA(VLOOKUP(...)) to test for it, or wrap the lookup in IFNA(VLOOKUP(...),"not found").
How can I find employees that are in the second database but not the first?
Run the same lookup in the opposite direction: use each employee ID from the second database as the lookup value against the first database, then filter the "not found" results.
What should I use as the VLOOKUP key for employee databases?
Use a unique, stable field such as employee ID or company email address. Avoid employee names unless naming is standardized and duplicates cannot occur.
How do I flag PTO records when one database has a blank value?
First return the other PTO value with IFNA. Then use a formula such as =IF(F2="","not found",IF(E2<>F2,"PTO discrepancy","PTO matches")) so missing records are not treated as normal PTO differences.
When is Power Query better than formulas for employee comparisons?
Use Power Query when the databases are refreshed frequently, contain many columns, or require a repeatable full audit. A Full Outer merge can retain matched employees and employees missing from either source.




