• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Formula to Match Records on Different Worksheets

Formula to Match Records on Different Worksheets

I was presented with a problem yesterday where somebody had two sheets of records. They were the same records but one list contained 16000 entries and the other had 14000. The person needed to know what records were missing from the sheet with fewer records.

This required matching records from different worksheets using multiple conditions. To check if they were a duplicate, the first name, last name and company fields needed to match.

To achieve this I wrote an array formula using the IF and SUMPRODUCT functions. This can be seen below testing the data from columns A, B and C across both sheets.

{=IF(SUMPRODUCT(((Sheet1!$A$2:$A$16000)=A2)*((Sheet1!$B$2:$B$16000)=B2)*((Sheet1!$C$2:$C$16000)=C2))=1,”Yes”,””)}

An array formula has curly braces at the ends which are added when you press Ctrl + Shift and Enter to run the formula.

The SUMPRODUCT formula returns true if you all three conditions match. THE IF function then displays the word Yes if there is a match on a record, and nothing if there is no match.

The column than then be filtered by blank cells to return all the missing records.
Find out more about the SUMPRODUCT function.

How would you have solved this problem? Drop me a comment with other formulas that could have achieved the same result.

Related Posts:

  • Two Way Lookup using INDEX and MATCH
    Two Way Lookup using INDEX and MATCH
  • Excel Formula to Find the Cell Address of a Value
    Excel Formula to Find the Cell Address of a Value
  • Multiple Condition Lookup Formula in Excel
    Multiple Condition Lookup Formula in Excel
  • Create a Case Sensitive Lookup Formula in Excel
    Create a Case Sensitive Lookup Formula in Excel

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·