Data Import & Export

Compare Employee Records and PTO Values in Excel Databases

Ayan MasoodAyan Masood Nov 12, 2026 869 views

Question details

Identify employees shared by two databases, find employees missing from either list, and compare their PTO values for differences.

Compare Employee Records and PTO Values in Excel Databases
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the lookup columns

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.

2
Flag a missing or matching employee

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.

3
Return the PTO value without an error

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.

4
Filter the audit results

Select the header row, then use Data > Filter and filter the status column for "not found" to review employees absent from the comparison database.

Use ISNA with VLOOKUP to flag employees missing from the other database
Use an exact match: Keep FALSE as the fourth VLOOKUP argument so employee IDs are matched exactly.
WPS Spreadsheet comparison tools

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. 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. 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. 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. 4. Review exceptions with filters: Select the headers and choose Data > Filter, then filter the status column for "not found" or "PTO discrepancy."
Use VLOOKUP, IFNA, and ISNA to identify missing employee records.Apply conditional formatting to make PTO discrepancies easy to review.Open and edit Microsoft Excel .xlsx and .xls employee databases.Use familiar spreadsheet menus for sorting, filtering, and formula-based audits.
microsoft office alternative - wps office

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.