How to Find Names in Sheet 1 Missing from Sheet 2 in Excel
Question details
Identify people listed in Sheet 1 but not in Sheet 2 by comparing last names and first names across the two worksheets.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two lists of names across different sheets to find discrepancies or missing entries.
- Observed behavior
- The user wants to output a TRUE/FALSE value where FALSE indicates a name present in Sheet 1 is missing or mismatched in Sheet 2.
Ensure both sheets have the names organized consistently, with last names in Column A and first names in Column B.
Use a Combined VLOOKUP and IF Formula
Use a nested IF and VLOOKUP formula to check both the first and last names across the two sheets and return TRUE if found, or FALSE if missing.
This formula checks if the last name (Column A) exists in Sheet 2. If it is missing, it returns FALSE. If the last name is found, it then checks if the corresponding first name (Column B) matches. It relies on exact matches, so formatting must be uniform across both lists.
Navigate to Sheet 1 where your initial list of names is located.
Click on an empty cell in row 2 (e.g., cell C2) right next to your first and last name columns.
Type the formula: =IF(ISNA(VLOOKUP($A2,Sheet2!$A:$A,1,FALSE)),FALSE,IF($B2=VLOOKUP($A2,Sheet2!$A:$B,2,FALSE),TRUE,FALSE))
Press Enter to see the result. Click the fill handle in the bottom-right corner of cell C2 and drag it down to apply this formula to the rest of the rows. A 'FALSE' result indicates the name in Sheet 1 is missing from Sheet 2.
Compare Lists Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced Excel formulas like VLOOKUP and IF, allowing you to instantly compare lists across multiple sheets. Manage your data analysis tasks efficiently with a free, professional tool.
- 1. Open your file: Launch WPS Spreadsheet and open your workbook containing the two sheets.
- 2. Insert the formula: Select the target cell in Sheet 1, and paste the VLOOKUP formula to compare the names.
- 3. Drag to fill: Press Enter and drag the formula down to instantly identify any missing names.
Frequently Asked Questions
Why does my lookup formula return #N/A instead of FALSE?
If you only use VLOOKUP without the ISNA or IFERROR wrapper, it will return an #N/A error when a match is not found. Wrapping it in IF(ISNA(...), FALSE) safely handles these errors and turns them into a readable 'FALSE' text.
Does this formula work if names have extra spaces?
No, VLOOKUP requires exact matches. If your names have trailing or leading spaces, the formula will return FALSE even if the names visually match. Wrap your cell references in the TRIM function, such as TRIM($A2), to remove extra spaces before comparing.
How can I compare only one column instead of both first and last names?
If you only need to check one single column (like an Employee ID or a full name combined in Column A), use a simpler formula: =IF(ISNA(VLOOKUP($A2,Sheet2!$A:$A,1,FALSE)), FALSE, TRUE).
Can I use conditional formatting to highlight the missing names instead of a formula column?
Yes, you can use the COUNTIFS function within the Conditional Formatting rules. Select your names in Sheet 1, choose 'New Rule', and use a formula like =COUNTIFS(Sheet2!$A:$A, $A2, Sheet2!$B:$B, $B2)=0 to highlight names missing from Sheet 2.
