Match Names on Two Excel Sheets with VLOOKUP

Formulas & functions · Updated November 20, 2026

Yes. VLOOKUP can find a name on one Excel sheet in a second sheet and return a related value from that row. With names in Orders!A2:A100 and Directory!A2:A100, and the value to return in Directory!B2:B100, enter this in Orders!B2: =IFERROR(VLOOKUP(A2,Directory!$A$2:$B$100,2,FALSE),"Not found"). If A2 contains Mina Patel and the matching Directory row has Finance in column B, the result is Finance.

What VLOOKUP does when matching names

VLOOKUP searches down the first column of a selected range for a lookup value. When it finds a match, it returns a value from another column on the same row. In this example, the name in Orders!A2 is the lookup value; Directory!A2:A100 is the search column; and Directory!B2:B100 contains the department to retrieve.

The function name refers to vertical lookup: Excel checks entries down a column rather than across a row. Its arguments specify what to search for, where to search, which column’s value to return, and whether to use an exact or approximate match. The final argument FALSE requests an exact match, which is generally what you want when matching people’s names. Without it, VLOOKUP can use approximate matching, which depends on the search column being sorted and may return an unintended result.

VLOOKUP does not combine or reconcile records. It finds a matching value and returns a value from the same row. If the two sheets use names that are spelled differently, refer to the same person under different names, or contain duplicates, a basic lookup cannot determine the correct identity on its own.

Prepare both sheets before writing the formula

Arrange the reference sheet so the names to search are in its leftmost table column and the information to return is to the right. In the example, Directory column A contains names and column B contains departments. The Orders sheet has names in column A, with the result column available in B. The sheets can be in either order in the workbook; the important point is the column order inside the range supplied to VLOOKUP.

Before matching, check that the columns contain comparable data. A name may appear to be identical but have an extra space at the end, a different apostrophe, or a middle initial on only one sheet. If the data comes from different systems, compare a few known records manually and check for blank cells. For a reliable match, a unique employee number, customer ID, or other stable identifier is usually better than a person’s name.

  • Keep the lookup names in the first column of the selected reference range.
  • Put the value you want returned in a column to the right of those names.
  • Use a bounded range that covers all records, and include new rows when the list grows.
  • Make sure the workbook contains the sheet names used in the formula; sheet names are not case-sensitive, but spelling and spaces matter.

Enter the exact-match formula and copy it down

In the example formula, A2 means “look up the name on this row.” Directory!$A$2:$B$100 is the reference table, and the dollar signs keep that range fixed when the formula is copied. The number 2 tells Excel to return the second column of that selected range—in this case, Directory column B. FALSE tells VLOOKUP to look for an exact match.

Click Orders!B2, type the formula, and press Enter. If the result is correct, copy the formula down the rest of column B using the fill handle at the lower-right corner of the selected cell. You can drag it down, or double-click the fill handle when the neighboring column has a continuous set of records. Excel adjusts A2 to A3, A4, and so on, while the dollar signs preserve the reference range.

The example uses a fixed endpoint at row 100. If Directory later grows beyond that row, the added records are outside the lookup range and will not be found. Update the range when the list grows, or convert the reference data to an Excel Table so that its range expands as records are added. For a small, stable list, a fixed range is straightforward; for a frequently updated list, a table can reduce maintenance.

Understand missing results and other errors

When an exact lookup cannot find the name, VLOOKUP ordinarily returns #N/A. The example wraps the lookup in IFERROR, so the cell displays Not found instead. That is easier to read in a report, but IFERROR catches other formula errors too. For instance, an invalid column number can also be hidden by the same message. While checking or building a workbook, leaving the error visible can make formula problems easier to diagnose; add IFERROR after confirming the lookup works.

A missing result does not necessarily mean the person is absent from the second sheet. Check whether the name is spelled differently, contains an extra space, includes a middle name or initial, or uses a different punctuation mark. Excel’s exact text lookup is not case-sensitive, so capitalization alone generally will not prevent a match. But exact match does not mean Excel understands that two different strings refer to the same person.

Also check the range and return-column number. The return number counts columns from the left edge of the table range, not from column A of the worksheet. If the selected range begins in Directory column A, column B is number 2. If the range began in column B, then the second column in that range would be worksheet column C. A return number greater than the width of the selected range causes an error.

Handle duplicates, spacing, and name variations

If the same name appears more than once in the reference range, VLOOKUP returns the value from the first matching row it encounters, reading from top to bottom. It does not warn that later duplicates exist, nor does it choose the newest, most complete, or otherwise “best” record. Sort order does not make duplicates unique. Review repeated names and decide what the correct record should be before using the results for decisions or reports.

Spaces copied from websites or databases are a common source of failed lookups. Excel’s TRIM function can remove ordinary leading and trailing spaces and reduce repeated ordinary spaces between words, while CLEAN can remove certain nonprinting characters. These tools do not resolve spelling differences or decide whether “Sam Lee” and “Samuel Lee” are the same person. Some copied text also contains nonbreaking spaces, which may need separate cleanup. When the source data is important, make cleaned helper columns and inspect the results rather than changing the original names without a record of the change.

For people with common names, match on a second field as well, such as an employee ID or email address, when one is available. Names can change, be entered in different orders, or belong to more than one person. A lookup based on a unique ID is less likely to attach a department, balance, or other value to the wrong record. If names are the only available identifier, treat ambiguous and unmatched results as items for review instead of assuming every result is conclusive.

VLOOKUP compared with XLOOKUP and other methods

VLOOKUP remains useful for a simple left-to-right lookup and works in many older Excel workbooks. Its main constraints are that the lookup column must be the first column in the chosen range, and the return column is specified with a number. If someone inserts a column into that range, the intended return-column position can become less obvious or change.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It separates the lookup column from the return column, can return values from either side, and uses exact matching by default. It can be a clearer choice in newer workbooks, especially when the result is to the left of the names. However, a workbook using XLOOKUP may not calculate correctly in older Excel versions that do not support it. If you need compatibility with older installations, VLOOKUP or another supported approach may be more suitable.

For recurring imports or larger reconciliation jobs, Power Query can merge two tables using a chosen key and load the resulting table back into Excel. It is useful when the task involves repeatable data preparation, but it requires more setup than a single lookup formula. Fuzzy matching options can help identify text that is similar rather than identical, but similar strings are not proof that two records refer to the same person; review possible matches before relying on them.

Frequently Asked Questions

Can VLOOKUP match names on different sheets in the same workbook?
Yes. Include the reference sheet name in the table-range reference, as in the example. The lookup value can be on one sheet while VLOOKUP searches a range on another.

Why does VLOOKUP return #N/A when the name is visible?
The text may not be exactly the same because of a space, punctuation, spelling, or an added middle initial, or the reference range may not include that row. Check the underlying cell contents and the range boundaries, not just how the names look on screen.

Does VLOOKUP distinguish uppercase and lowercase names?
No. Standard VLOOKUP text matching is not case-sensitive. Names that differ only in capitalization are treated as a match, but other character or spacing differences can still matter.

What happens if a name appears twice in the lookup sheet?
VLOOKUP returns the first matching row in the selected range. It does not return all matches or flag the duplicate, so check whether the name is unique or use a more reliable identifier.

Can I use VLOOKUP if the return column is to the left of the name?
No. The lookup column must be the first column in VLOOKUP’s table range, and the function returns data from columns to its right. In Microsoft 365 or Excel 2021 and later, XLOOKUP can search one column and return a corresponding value from a column on either side.

Does the formula work in Excel for Mac or Excel for the web?
VLOOKUP and the same basic formula are available in current Excel for Mac and Excel for the web. The steps to enter or fill a formula may look different by platform, but the sheet-reference and exact-match logic is the same. Regional settings can also affect which argument separator Excel expects.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.