To merge information from two Excel sheets by a shared ID, add a column to the sheet you want to keep and use an exact-match VLOOKUP to bring in the matching value. For example, if the ID is in A2 and the other sheet has IDs in column A and the value you need in column C, enter =IFNA(VLOOKUP(A2,Sheet2!$A$2:$C$100,3,FALSE),"No match") and fill the formula down. VLOOKUP combines data by matching records; it does not physically join worksheets into one table by itself.
What merging two sheets means
In everyday Excel use, “merge two sheets” usually means matching rows in one worksheet with rows in another, then adding one or more fields to a main list. For instance, a sales sheet may contain order IDs and customer IDs, while a customer sheet contains those IDs, names, and regions. A lookup can add the region beside each order without manually searching through the customer list.
This is different from combining every row from both sheets into one longer list. VLOOKUP is designed to find a matching item in a table and return a value from that table. It is useful when one sheet is the base list and the second sheet supplies details. Decide which sheet should remain the main list before starting; that determines where you put the formula and which column contains the lookup key.
Prepare the sheets and choose a key
Choose a field that identifies the same record in both sheets, such as an employee number, product code, invoice number, or customer ID. A name can work if it is unique and written consistently, but names are often a poor key: two people may share a name, or one sheet may include a middle initial while the other does not. A reliable identifier reduces the chance of attaching the wrong information.
Before writing the formula, check that the key columns use the same values and data types. An ID stored as the number 104 may not match an ID stored as text “104.” Leading or trailing spaces, inconsistent punctuation, and different zero padding can also prevent a match. Keep the source table’s header row out of the lookup range, and make sure the column you are returning is included in that range.
- Give each sheet a clear name, such as Orders and Customers, and note the key column on each.
- Check for blanks and duplicate keys in the lookup sheet. VLOOKUP returns the first matching row when keys repeat.
- Confirm that the value you want to return is to the right of the key column in the lookup range.
- Save a copy before making substantial changes, especially if the workbook is important or shared.
Use VLOOKUP to add a matching column
Suppose Sheet1 contains customer IDs in A2:A20, and you want to add each customer’s territory. Sheet2 contains IDs in column A, customer names in B, and territories in C, with records in rows 2 through 100. On Sheet1, put a heading such as Territory in the next empty column. In the first data row beneath that heading, enter the formula below. It looks for the ID in A2, searches the first column of the Sheet2 range, and returns the third column of that range.
With the lookup ID in Sheet1!A2 and the source table in Sheet2!A2:C100, enter this formula in the new column on Sheet1, such as D2: =IFNA(VLOOKUP(A2,Sheet2!$A$2:$C$100,3,FALSE),"No match"). If A2 contains 104 and the row for ID 104 has West in Sheet2 column C, the result is West. If there is no matching ID, the formula displays No match instead of an #N/A error.
The parts of the formula matter:
A2is the value to search for. It changes to A3, A4, and so on when you fill the formula down.Sheet2!$A$2:$C$100is the search table. The dollar signs keep that range fixed as the formula is copied.3tells VLOOKUP to return the third column of the selected range. In this example, that is column C.FALSErequests an exact match. This is important for IDs and other keys.IFNAreplaces the not-found error with a readable message. It does not fix other formula errors.
After entering the formula, press Enter, check the result, then fill it down through the remaining rows. In Excel for Microsoft 365 on Windows, select the formula cell and drag its fill handle down, or select the formula cell and the cells below it and press Ctrl+D. On a Mac, the formula works the same way; the fill-down shortcut is Command+D. You can also copy and paste the formula into the target cells.
Return a different field or use a named sheet
The column number is counted from the left edge of the lookup range, not from the worksheet’s column A. If the range starts at B and the desired answer is in D, the return column number is 3. This detail is a common source of incorrect results. If the sheet name has spaces, put single quotation marks around it in the reference, as in Customers 2026 written as 'Customers 2026'!$A$2:$C$100. Excel will often add the quotation marks when you select the range while building the formula.
If you need to bring in more than one field, create a separate VLOOKUP formula for each returned column and change the column number. For example, the customer name might be in column 2 of the lookup range and the territory in column 3. Keep the lookup range fixed with dollar signs, and check each returned column against a known record before filling the formulas throughout the list.
Check for missing, duplicate, or mismatched records
A “No match” result is useful because it makes missing records visible, but it does not tell you why the record was missed. Compare the key on both sheets. Look for leading or trailing spaces, different punctuation, numbers saved as text, and IDs with different numbers of leading zeros. If an ID is meant to be a fixed-length code, preserve it as text consistently rather than converting one version to a number.
Duplicate keys in the lookup table need special attention. VLOOKUP returns the first match from top to bottom; it will not warn you that another row has the same ID. If those duplicate rows contain different values, the result may be ambiguous. Clean or resolve the duplicates before relying on the merged output. Also review blank keys: several blank entries can lead to confusing matches, so avoid treating an empty cell as a meaningful identifier.
For a quick quality check, compare a sample of results with the source sheet, including records near the top and bottom of the range, a key you know is missing, and any IDs that have appeared more than once. A formula can be syntactically correct and still return the wrong field if its column number or lookup range is off.
Keep the results current or make them permanent
VLOOKUP results are formulas, so they can update when the source values change, provided calculation is enabled and the changed data remains inside the referenced range. The example range ending at row 100 will not include a new source record entered in row 101. One way to make the lookup range expand more reliably is to format the source data as an Excel table: select a cell in the data and choose Insert > Table, checking that the table has headers. A table is easier to maintain as rows are added, though you should still verify the formula references after setting it up.
If you need a one-time snapshot rather than a live link, copy the completed result column and use Home > Paste > Paste Values. This replaces the formulas with their current displayed results. Keep the original workbook or a separate copy if you may need to refresh the data later, because pasted values no longer respond to changes in the source sheet.
VLOOKUP limitations and alternatives
VLOOKUP has two important constraints: its lookup key must be in the leftmost column of the selected range, and its return column must be to the right of that key. It also uses a numeric column position, which can become inaccurate if someone inserts or rearranges columns inside the range. For a straightforward table with a key on the left, an exact-match VLOOKUP remains a practical option.
In Microsoft 365 and Excel 2021 or later, XLOOKUP is another choice. It lets you specify the lookup and return columns separately, so the return column does not have to be to the right of the key. For larger repeatable data-import and transformation tasks, Power Query can merge queries using a selected matching column and load the result as a table. Use the option that fits the task: VLOOKUP for a simple formula-based lookup, XLOOKUP for more flexible formula lookups, or Power Query when you want a refreshable data-preparation process.
Frequently Asked Questions
Does VLOOKUP merge two Excel sheets?
It matches a value from one sheet to a row in another and returns a selected field. The result appears in the formula’s cells; VLOOKUP does not combine worksheets into one physical table or append all rows together.
Why does VLOOKUP return #N/A even though the ID looks the same?
The values may differ in a way that is not obvious, such as a number stored as text, extra spaces, punctuation, or different leading zeros. Check the underlying key values and confirm that the lookup range includes the source row.
Why is VLOOKUP returning the wrong column?
The column index is counted from the first column of the selected lookup range. If the range begins in column B, column D is the third column of that range, not the fourth.
Can VLOOKUP return more than one column?
A basic VLOOKUP returns one column for each formula. Add another formula for each additional field, using the same lookup key and range but the appropriate return-column number.
What happens when the lookup sheet has duplicate IDs?
VLOOKUP returns the first matching row in the range. It does not combine duplicate records or flag them, so resolve duplicate keys if they could produce different returned values.
How do I keep new rows included in the lookup?
Expand the referenced range when the source data grows, or structure the source list as an Excel table and check that the lookup formula uses the intended table data. A fixed range such as rows 2 through 100 will not automatically include data placed below row 100.
Related
- Machine ROI Calculator in Excel: Build and Use One
- Mac Numbers vs Excel: Which Spreadsheet Should You Use?
- Machine Checklist Format in Excel: A Practical Template
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Search Excel Cells for Specific Text: Methods and FormulasTo search Excel for specific text, press Ctrl+F on Windows (Command+F on Mac), type the text, and choose…
- Excel Formula to Calculate Age From a BirthdayIn Excel for Microsoft 365, use =DATEDIF(A2,TODAY(),"Y") to calculate someone’s age in completed years, assuming the birthday is…
- Excel Formula for Budget Amount RemainingIf your total budget is in B2 and the amount spent is in C2, enter =B2-C2 in another…
- Excel Yield Analysis: Formulas for Bonds and ProductionExcel yield analysis depends on what you mean by yield: for a bond’s yield to maturity, use Excel’s…
- XLOOKUP in Excel: Find a Value and Return Matching DataTo find a value in Excel and return its matching result, use XLOOKUP: with product IDs in A2:A6,…
- How to Filter Dates in Excel by Day, Month, or RangeTo filter dates in Excel, select a cell in your data, choose Data > Filter, open the arrow…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.