Append orders, merge customer names, and check the row count

01Create the three sources

Paste the sample at January!A1:C3. On February, enter the table below at A1:C3. On Customers, enter CustomerID and CustomerName in A1:B1, then C01/Ana in row 2 and C02/Bo in row 3. Create an Excel table from each exact range and name them JanOrders, FebOrders and CustomerMap respectively.

For each table, click inside it and choose Data > From Table/Range. Set identifiers and names to Text, amounts to Whole Number. Name the queries JanQuery, FebQuery and CustomerQuery. For each, Home > Close & Load To > Only Create Connection keeps the source sheets separate from the later result.

02Append creates more records

Open JanQuery from Data > Queries & Connections. In the editor choose Home > Append Queries > Append Queries as New. Select JanQuery followed by FebQuery and name the new query AllOrders. Four order records should total 140: January contributes 100 and February 40.

Append aligns fields by header names, not worksheet positions. Renaming Amount to Amount_USD in just one source creates a different field with missing values elsewhere; it does not translate the schema. Keep the contract identical. Appending also does not remove repeated orders, so accidentally adding January twice would require investigation.

03Merge adds matching attributes

With AllOrders selected, choose Home > Merge Queries > Merge Queries as New. Put AllOrders on the left and CustomerQuery on the right. Select CustomerID in each, choose Left Outer and confirm. Name the result EnrichedOrders. Expand only CustomerName from the nested table column, then sort OrderID ascending and Close & Load.

There must still be four rows totaling 140. O101 gets Ana; O102 and O103 get Bo. O104 has no matching customer, so its expanded name is null in the query, usually a blank cell after loading. That missing name is an exception to resolve, not a reason to discard its 15 of sales.

04Check the lookup's uniqueness

If CustomerMap contains two C02 records, expanding the merge produces two copies of each matching order. The result becomes six rows totaling 225, even though the original orders still total 140. Reconcile counts and amounts after expansion, and inspect key uniqueness before trusting the output. Do not blindly deduplicate orders to conceal an ambiguous customer mapping.