How to Create a Three-Way Match in Excel or Power Query
Question details
The user needs to construct a three-way match comparing purchase requisitions, purchase orders, receipts, and invoices.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reconciling accounts payable by matching data across multiple financial tables to ensure purchasing, receiving, and billing records align.
- Observed behavior
- Building a unified reconciliation table that accurately reflects matched and unmatched records from separate source documents.
Ensure all your source tables are formatted as standard Excel Tables (Ctrl + T) and contain a reliable shared identifier, such as a Purchase Order Number or Receipt Number.
Build a Three-Way Match using Power Query
Power Query is the most robust tool for merging multiple tables and standardizing data without writing complex spreadsheet formulas.
By importing your separate tables into Power Query, you can link requisitions, purchase orders, receipts, and invoices into a single comprehensive reconciliation table.
Navigate to the Data tab and select 'From Table/Range' to import your Requisitions, Purchase Orders, Receipts, and Invoices tables into Power Query individually as connections.
In the Power Query Editor, verify that your shared keys (like PO Number) have the exact same column names and data types (e.g., both set to Text or Whole Number) across all tables.
On the Home tab, click 'Merge Queries'. Select your primary table (e.g., Purchase Orders), then select the secondary table (e.g., Receipts), and click on the shared key column in both previews to link them.
Click the small expand icon on the newly created merged column header to reveal and select the specific receipt or invoice details you want to add to your main table.
Once all necessary tables are merged and expanded, click 'Close & Load' to drop the finalized reconciliation table into a new worksheet.
Create a Three-Way Match Using Lookup Formulas
If you prefer not to use Power Query, you can use built-in lookup formulas to pull data into a master sheet.
Perform Advanced Data Matching with WPS Spreadsheet
WPS Office provides powerful built-in functions like XLOOKUP, VLOOKUP, and Pivot Tables that allow you to quickly perform three-way matches. It handles large datasets efficiently and serves as an excellent environment for financial reconciliation.
- 1. Organize Your Data: Open your workbook in WPS Spreadsheet and organize your Requisitions, POs, and Invoices into separate dedicated sheets.
- 2. Apply Lookup Functions: Use the XLOOKUP or VLOOKUP function to seamlessly pull receipt and invoice data into your main Purchase Order table using your shared keys.
- 3. Highlight Discrepancies: Use the Conditional Formatting tool under the Home tab to automatically highlight rows where the invoiced totals do not match the expected PO totals.
Frequently Asked Questions
What is a three-way match in accounting?
A three-way match is an internal accounting control process that compares the purchase order, the receiving report (receipts), and the supplier invoice to ensure all details and quantities match before a payment is issued.
What common keys should I use for merging tables in Power Query?
Reliable shared keys include Purchase Order Number, Receipt Number, Item Number, or Supplier ID. Ensure these keys are unique and present across the tables you are attempting to merge.
Why are my Power Query merges returning blank or null values?
This usually happens if the shared keys have mismatched data types (e.g., one is formatted as text and the other as a number) or if there are hidden trailing spaces. Use the Trim tool and Data Type settings in Power Query to clean your key columns first.
Can I automate the three-way match process for future months?
Yes. Once you set up your connections and merges in Power Query, you can simply update your source tables with new data and click 'Refresh All' on the Data tab to automatically generate the new three-way match.
