Find rows in Excel that do not have a corresponding in another worksheet

Question

I've got two Excel-worksheets with different structures. The content is being updated every day by a SQL-data-connection and every worksheet contains more than 50k rows.

Worksheet1 (Accounts payable)

Company_No | Company_Name  | Vendor   | Amount | InvoiceDate | InvoiceNumber 
UK123      | ExampleComp   | UK788    | 200,00 | 01.02.2016  | 444555
UK123      | ExampleComp   | UK788    | 350,00 | 05.02.2016  | 111222
UK789      | TestComp      | US222    | 1000,00| 09.02.2016  | 456456

Worksheet2 (Accounts receivable)

Company_No | Company_Name  | Customer | Amount | InvoiceDate | InvoiceNumber 
UK788      | RandomComp    | 123      | 200,00 | 01.02.2016  | 444555
US222      | RandomComp    | US789    | 1000,00| 09.02.2016  | 456456

I need to check these two worksheets for missing postings of intercompany accounts. In this example I would like to return the following (because there is no corresponding post in Worksheet2):

Company_No | Company_Name  | Vendor   | Amount | InvoiceDate | InvoiceNumber 
UK123      | ExampleComp   | UK788    | 350,00 | 05.02.2016  | 111222

The amount and order of rows may vary - I need a result table that shows ONLY the entries WITHOUT a "partner". Thanks for your help in advance!

Answer 1

In a new column in Worksheet1 write the following formula:

=IFERROR(INDEX('Worksheet2'!F:F,MATCH(F2,'Worksheet2'!F:F,0)),"No match")

Copy it down across all rows. Where it will say "No match" is where there was no match found in worksheet2

edit: if you want to use multiple criteria just add the cells in the MATCH() part of the formula, like so:

=IFERROR(INDEX('Worksheet2'!F:F,MATCH(F2&E2&C2,'Worksheet2'!F:F&'Worksheet2'!E:E&'Worksheet2'!C:C,0)),"No match")

You can add as many as you want