Merge Excel Sheets to Find Specific Names in Excel

How-to basics · Updated November 20, 2026

To merge sheets and return every row for a particular name in Excel for Microsoft 365, stack the sheets’ data and filter the combined rows. With names in column A, details in A:C on sheets named West and East, and the name to search in E2, use the formula below; for a reusable, refreshable merge, append the sheets with Power Query instead.

Assuming West!A2:C100 and East!A2:C100 contain the records, column A holds the names, and E2 contains the name to find, enter this formula in an empty cell:

=FILTER(VSTACK(West!A2:C100,East!A2:C100),VSTACK(West!A2:A100,East!A2:A100)=E2,"No matches")

VSTACK and FILTER are available in Microsoft 365 and Excel 2024. If Maya Chen appears in both source sheets, with employee numbers 104 and 118, searching for Maya Chen returns both records and their other columns. The formula combines the rows vertically, then keeps only rows whose name matches E2.

What merging sheets means

People use “merge” to describe a few different tasks. In this context, it usually means combining records from separate worksheets into one list and locating all records for one or more people. It does not necessarily mean joining tables side by side, nor does it mean deleting duplicate records.

For example, a workbook might have one customer list on a North sheet and another on a South sheet. Appending them places the North rows above the South rows in one longer table. You can then search the combined list for a customer and inspect their region, account number, or other details. Keeping those details and the source sheet can be important: two people can share a name, and the same person can have multiple legitimate records.

Before combining data, check that the sheets have the same kind of information in corresponding columns. A column called “Name” should not contain dates on one sheet and names on another. Matching headers and column order make both formula results and Power Query output easier to interpret.

Use Power Query for a repeatable merge

Power Query is usually the better choice when the lists are large, the source sheets change regularly, or you need to repeat the merge. It can append tables into one result that you can refresh when the source data changes. In Excel for Microsoft 365 on Windows, turn each source range into an Excel table, then create a query from each table.

  1. On each source sheet, select the data, including its header row, and choose Insert > Table. Confirm that “My table has headers” is selected if the first row contains column names. Give each table a useful name on the Table Design tab, such as NorthData and SouthData.
  2. Select a cell in the first table and choose Data > From Table/Range. In the Power Query Editor, check that the column headings and data types are sensible, then choose Home > Close & Load > Close & Load To. Create a connection for the query, or load it to a worksheet if that is more convenient.
  3. Repeat the process for the other source tables. In the Power Query Editor, choose Home > Append Queries > Append Queries as New. Select the option for two tables or for three or more tables, as appropriate, and add the queries to the append list.
  4. Review the appended result. Filter the Name column using its header dropdown to select the person you want. Choose Home > Close & Load to place the output in the workbook.

Appending matches columns by their names, not simply by their position. That is useful when tables have the same headers in a different order, but it also means inconsistent headers can create separate columns. Standardize headings such as “Customer Name” and “Name” before appending if they refer to the same field. When new records are added to the source tables, use Data > Refresh All to update the query output. A refresh updates the result; it does not edit the original source rows.

Choose a method that fits the task

A formula is convenient when you need an immediate answer, have a few known sheets, and want the matches to update as cell values change. Power Query is more manageable when the number of sheets grows, records need cleaning, or the merge is part of a recurring process. For a one-time job with a few short lists, copying the rows into a new sheet and using Excel’s filter may be the simplest approach.

  • Formula: Fast to set up for a small, fixed set of sheets; the output spills into nearby cells and recalculates when its source values change. It requires a compatible Excel version for VSTACK and FILTER.
  • Power Query: Suitable for repeatable imports and larger consolidations; it provides transformation tools and a refreshable output. It takes more setup and must be refreshed after source changes.
  • Copy and filter: Straightforward for a one-off task, but the copied list will not automatically reflect later edits to the original sheets.

Excel’s ordinary Find command is another option if you only need to locate a visible occurrence rather than make a combined results list. Press Ctrl+f on Windows and search the workbook. On a Mac, use Command+f. Finding a result this way does not consolidate the source data, and it may take more work to compare every occurrence across several sheets.

Check what counts as a name match

The sample formula uses an equality comparison, so it looks for the complete cell value rather than a name contained inside a longer string. In standard Excel comparisons, text matching is not case-sensitive: “Maya Chen” and “MAYA CHEN” match. A trailing space or an extra middle initial can make two entries different, even if they look similar at a glance.

Decide whether you want an exact full-name match or a broader search before choosing a method. A broad search for “Maya” could return Maya Chen, Maya Patel, and entries where Maya appears in a different part of the text. In Power Query, filter the name column by the appropriate text condition. For exact identification, consider using another field such as an email address or employee ID as well as the name; names alone are not guaranteed to identify one person.

Spacing and hidden characters are common causes of apparent misses. Inspect suspicious values, or use Power Query’s Transform > Format > Trim and Transform > Format > Clean commands to address leading or trailing spaces and certain nonprinting characters. Check the results afterward: cleaning can make entries match, but it cannot decide whether two similar spellings represent the same person.

Handle duplicates without losing useful records

A search can return more than one row for a good reason. One person might have several transactions, appear in two regional lists, or have separate records for different dates. The formula returns all matching rows; it does not remove duplicates. Similarly, appending with Power Query stacks records but does not automatically establish which ones are redundant.

Do not delete repeated names just because they look identical. Compare the other columns first. If the goal is to create a list of unique people, decide which fields define a duplicate, such as matching email addresses or a combination of name and date of birth. In Power Query, select the relevant column or columns and use Home > Remove Rows > Remove Duplicates. Removing duplicates based on Name alone can discard distinct people who share a name, so retain a copy of the original data and verify the intended rule.

It can also help to include a Source column before appending, so every result records which sheet or table it came from. That makes it easier to trace a match back to its original list and investigate conflicts.

Common problems and practical fixes

  • The formula returns “No matches.” Confirm that E2 is spelled and spaced like the source value, that the sheet names and ranges in the formula are correct, and that the name is actually in column A. Test a copied source value in E2 to rule out a typing difference.
  • The formula shows an error or does not spill. VSTACK and FILTER require Microsoft 365 or Excel 2024 for this formula. Also make sure the cells below and beside the formula are clear; another value in the spill area can block the result.
  • Power Query creates extra columns. Check for inconsistent header names, such as “Last Name” on one table and “Surname” on another. Rename equivalent columns consistently before appending.
  • New rows do not appear in the query result. Confirm that the rows were added inside the source Excel table, then choose Data > Refresh All. Rows typed below a table may not be included in its source.
  • The result looks incomplete. Check whether a filter is still active, and confirm that every source sheet or table was added to the append operation. Also review whether the source ranges or tables include all intended rows.

For formula-based work, use ranges that cover the expected records but avoid unnecessarily enormous ranges if the workbook becomes slow. Excel tables can make formulas and Power Query sources easier to maintain as lists grow. Whatever method you choose, keep the original sheets unchanged until you have checked that the consolidated output includes the expected names and details.

Frequently Asked Questions

Can I merge several Excel sheets and search a name at the same time?
Yes. In Microsoft 365 or Excel 2024, extend the VSTACK portions of the sample formula to include each sheet, including its name range in the matching condition. For a changing collection of source tables, append them with Power Query and filter the combined Name column.

Does the formula return every row for a repeated name?
Yes. FILTER returns every row that meets its condition, so repeated entries remain visible. Review the other columns to decide whether those records are separate transactions or unwanted duplicates.

Can I search for part of a name instead of the full name?
Yes, but the sample formula is an exact whole-cell comparison. For partial searches, use a suitable text filter in Power Query or build a formula designed for substring matching. Check the results carefully because a short fragment can match several different people.

Will Power Query update when I edit the source sheets?
It can update from its source tables when you refresh the query, for example with Data > Refresh All. It is not a live formula link: edits to the source do not appear in the loaded result until a refresh runs.

Can I use the VSTACK formula in older Excel versions?
VSTACK requires Microsoft 365 or Excel 2024. If your Excel version does not support it, use Power Query if available, or copy the source rows into one list and filter that list. The exact tools available can depend on the Excel edition and update channel.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.