How to merge sheets by a column manually in Excel
Bringing columns from one table into another based on a shared ID is one of the most common jobs in Excel. There are three main ways to do it, and each has habits that trip people up.
VLOOKUP
With orders on one sheet and customer details on a sheet called Customers, put this next to the first order:
=VLOOKUP(A2,Customers!$A:$D,2,FALSE)
It looks up the value in A2 in the first column of the Customers range and returns the second column, which might be the name. Some things to watch:
- The last argument must be FALSE for an exact match. Leaving it out means an approximate match, which quietly returns the wrong customer on unsorted data.
- The key must be the first column of the lookup range. If Customer ID is in column C, you need to rearrange the sheet or use another formula.
- You need one formula per column: change the 2 to 3 for City, 4 for Email, and fill each down.
- VLOOKUP ignores capital letters, but it does not forgive a trailing space or a number stored as text. “C-101 “ and “C-101” do not match, nor do 123 and “123”, so you get #N/A even though the IDs look the same.
XLOOKUP and INDEX/MATCH
Excel for Microsoft 365 and Excel 2021 offer =XLOOKUP(A2,Customers!A:A,Customers!B:B,"Not found"). The key can be in any column, the match is exact by default, and the fourth argument replaces #N/A with your own text. In older versions, =INDEX(Customers!B:B,MATCH(A2,Customers!A:A,0)) gives the same flexibility. Both still fail on stray spaces and on numbers stored as text.
Power Query
For larger or repeated jobs, load both tables into Power Query, then choose Home › Merge Queries. Pick the main table and the lookup table, click the matching column in each, and choose a Join Kind: Left Outer keeps every row of the main table, Inner keeps only rows that match. Click OK, then use the expand button on the new column header to pick the fields to bring in. Be aware that Power Query matches text case-sensitively by default, so “c-101” will not find “C-101”.
Whichever formula method you use, finish by selecting the new columns and choosing Paste Special › Values, otherwise the results break when the lookup sheet is moved or deleted.
When this tool helps
Orders and customers. Your shop exports orders with only a customer ID. Bring in the name, city and email from the customer list so the sheet is ready for a courier or a mailing.
Stock lists and prices. The warehouse sheet has SKUs and quantities; the price list sits elsewhere. Join on SKU to value your stock without copying prices by hand.
Attendance and departments. HR gives you an attendance export with employee codes. Pull in each person’s department and manager from the staff list to build a report per team.
Invoices and tax details. Add each customer’s GST number to an invoice register by matching on customer code, so the accountant gets one complete sheet.
What the tool does differently
- It forgives the usual mismatches. Extra spaces in keys are always ignored, capital letters are ignored by default, and numbers match their text versions. These are the reasons most lookups fail.
- It brings every column in one step. No column numbers to count and no formulas to fill down.
- It tells you what did not match. With the sample file the summary reads: “Matched 3 of 4 rows on Customer ID and added Name, City and Email from Customers. 1 row had no match and was left blank: C-109.” The order for “c-101 “ matched despite its lower-case letter and trailing space.
- It never overwrites your data. New columns go at the end, and a clashing name gets “ (2)” added.
- It gives plain values. The download holds values, not formulas, in the same format as your main file.
Before you merge
Check that the lookup sheet has one row per ID. If the same customer appears twice with different cities, only the first is used, so remove duplicates in the lookup sheet first if you are unsure which is correct. And if many rows come back unmatched, look at the listed keys: a different prefix or a missing leading zero often explains it.
Think about which sheet should be the main one. The main sheet decides how many rows you end up with and in what order, so it is usually the list you are working from, such as this month’s orders. The lookup sheet is the reference list, such as all customers. If you only want orders from known customers, choose “Leave them out” for rows without a match, then download the removed rows to see which orders need a customer record added.