ICAEW.com works better with JavaScript enabled.
Exclusive

Find match exceptions before they become lookup errors

Author: Simon Hurst

Published: 26 Oct 2022

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

Combining information from two or more separate tables or lists is a common Excel requirement. The various Excel lookup functions can help achieve this, as can the ability to merge tables in Power Query. However, whichever method you use to combine the separate data sources, items missing from either table, or mismatched items, could cause errors. For example, if you had a list of product sales and a list of product categories, and you needed to calculate total sales by category, were one or more products to be missing from the product category table, then the overall totals might omit the sales values of those missing products.

We will look at using Power Query to generate lists of the products that don’t appear in both tables.

Power Query join kinds

Here are our two example tables. We will click in each in turn and use Data Ribbon tab, Get &Transform Data group, From Table/Range to load each one into the Power Query editor. Initially, we will then just use The Close & Load dropdown in the Power Query editor Home Ribbon tab to choose Close & Load to… and set the ‘load to’ location as: Only Create Connection. This makes the Table available in the Power Query editor without loading it to a Table in the workbook:
Excel screenshot
With both Tables now available in the editor, we can use Home Ribbon tab, Combine group, Merge Queries dropdown, Merge Queries as New to create a new query that combines columns from both of our Tables.

The first stage of the merge process is to choose each of our Tables in turn using the dropdowns. We then need to tell Power Query how to match our Tables. In our case, we will use the ProductID to make the match so we click on the ProductID column heading in both Tables. Note that it is also possible to match using multiple columns.

Once we have nominated our matching columns, we can choose the Join Kind from the dropdown. Usually, this is likely to be one of the Outer or Inner kinds, but we are specifically looking for items that don’t match, so we will start off by using the Left Anti join to find rows that are missing from our second Table:
Excel screenshot
Here is our resulting Table which we have renamed from Merge to the more meaningful ‘Missing from InvCats’. Each row of our Table includes, in the InvCats column, the relevant columns and rows from the InvCats Table. The rows will be empty as, by definition, there is no match in InvCats for the rows included in our merged Table. We can see this by clicking in the white space for any row in the InvCats column:
Excel screenshot
Having created a Table listing all the rows in the Invoices Table for which no match exists in InvCats, we now need to perform the check the other way round to list items that exist in InvCats but not in Invoices. The quickest way to do this is to right-click on our new merged query in the Queries pane on the left-hand side of the screen, and choose Duplicate. This creates, unsurprisingly, a copy of our existing merge query. We have renamed this new, copied, query as ‘Missing from invoices’ and clicked on the ‘gear’ symbol at the right of the Source step to re-open the merge dialog. We can then just choose the other part of our anti-join coalition, Right Anti, from our Join kind dropdown:
Excel screenshot
The result might initially be puzzling as it shows a single row of nulls. However, again this is what we should expect, as we are looking at rows that don’t exist. We still have an InvCats column, but this time the single entry holds all the columns and rows in InvCats that don’t have a match in Invoices:
Excel screenshot
To make our new query useful, we will right-click on the InvCats column heading and choose Remove Other Columns. Then we will click on the Expand/Aggregate icon to the right-hand side of the column heading and choose to expand all of our columns:
Excel screenshot
All that remains is to use Close and Load to load our Tables to an Excel worksheet where our user can review the results. We have chosen initially to use Close and Load to… to choose Only Create Connection. We have then right-clicked on each of the queries in turn in the Queries and Connections pane and chosen to load them to two adjacent Tables on the same worksheet:
Excel screenshot

When our source Tables change, we can just refresh our exception Tables to see whether there are any unmatched items.

Related links

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250