How to Use VLOOKUP to Compare Two Lists

Welcome to this tutorial on leveraging Excel’s VLOOKUP function to effectively compare two lists – a skill crucial for anyone navigating extensive datasets. In this comprehensive guide, we’ll delve into the intricacies of VLOOKUP through two practical examples: comparing employee lists and scrutinizing invoice numbers.

In today’s data-driven world, professionals often encounter scenarios where aligning and reconciling datasets is paramount. Whether you’re an HR professional reconciling employee records or an accountant ensuring invoice accuracy, this tutorial equips you with a powerful method to identify discrepancies efficiently. Named ranges, error handling with the ISNA function, and meaningful result enhancements using the IF function are all part of the toolkit you’ll acquire.

Furthermore, we’ll explore the aesthetics of data presentation through conditional formatting, providing a visually intuitive way to spot differences. Whether you’re new to Excel or seeking to enhance your data analysis skills, this tutorial offers a step-by-step approach, making the intricate process of comparing two lists more accessible and empowering.

Example 1: Employee Lists

Objective: Identify missing records in a colleague’s employee list.

1. Name Ranges: Optional but beneficial. Name the range of data in the colleague’s list for easier referencing.
2. Construct VLOOKUP Formula:
   • Lookup Value: Choose a unique, common value (e.g., Employee ID).
   • Table Array: Specify the range of data in the colleague’s list.
   • Column Index Number: Any column since we’re interested in presence, not specific data.
   • Exact Match: Set to FALSE for an exact match.
3. Handle N/A Errors:
   • Use IF(ISNA()) to convert errors to TRUE for missing records and FALSE for matches.
4. Enhance Meaningfulness:
   • Employ the IF function to assign text to TRUE and FALSE results (e.g., “Missing Record” or blank).
5. Conditional Formatting:
   • Optionally, use conditional formatting to highlight missing records for visual clarity.

Example 2: Invoice Numbers

Objective: Compare invoices received from a client with the accountant’s records

1. Name Ranges: Name the range of data in the client’s invoice report for ease of reference.
2. Construct VLOOKUP Formula:
   • Lookup Value: Use a common value (e.g., Invoice Number).
   • Table Array: Specify the range of data in the client’s invoice report.
   • Column Index Number: Not critical for this comparison; choose any.
   • Exact Match: Set to FALSE for an exact match.
3. Handle N/A Errors:
   • Use IF(ISNA()) to convert errors to TRUE for missing invoices and FALSE for matches.
4. Enhance Meaningfulness:
   • Use the IF function to label TRUE as “Missing” and FALSE as “OK” or leave it blank.
5. Conditional Formatting:
   • Highlight missing records for easy identification, creating a clear visual distinction.

Conclusion

Mastering the VLOOKUP function is a valuable skill for efficiently comparing two lists in Excel. By combining it with additional functions and formatting tools, you can streamline the identification of discrepancies, making your data analysis more meaningful and visually accessible. Practice these techniques to enhance your proficiency in data comparison tasks.

Also read:

Best Excel Formulas: Part 1(1-10): Essential Excel Formulas to change how you work FOREVER!

How to Link Excel to PowerPoint

How to Add Slide Numbers in PowerPoint

Please visit our free resources center for more high-quality guides and training.

Ready to dive deep into Excel? Click here for basic to advanced Excel courses with in-depth training modules.

Simon Sez IT has been teaching Excel and other business software for over ten years. You can access 160+ IT training courses for a low monthly fee.

Simon