Find Names in Excel Using VLOOKUP: Step-by-Step Guide

Formulas & functions · Updated November 20, 2026

To find a name and return a related value, put the name you want to search for in a cell, then use VLOOKUP to search the first column of your data. For example, with names in A2:A100, departments in B2:B100, and the name to find in E2, enter =VLOOKUP(E2,$A$2:$B$100,2,FALSE) in another cell. The formula returns the matching department, using an exact match. VLOOKUP is useful for looking up names in a list, but it only searches the leftmost column of the range you give it.

What You’ll Need

  • An Excel workbook with a list or table of names and the information connected to them, such as a department, email address, or order total.
  • A name to search for. It can be typed into a separate cell, which makes it easy to change the lookup without editing the formula.
  • Microsoft Excel for Microsoft 365 on Windows. The basic VLOOKUP method also works in other recent Excel versions, including Excel for Mac and Excel for the web, though ribbon locations and keyboard shortcuts can differ.

Before starting, identify which column contains the names and which column contains the result you want. VLOOKUP searches only the first column in its selected range and returns a value from a column to the right. If the names are not to the left of the information you need, you will need to rearrange the range or choose another lookup method.

Step-by-Step

  1. Check how the data is arranged. Find the column that contains the names you want VLOOKUP to search. In this example, names are in column A, departments are in column B, and email addresses are in column C. Row 1 contains headings, so the actual records begin in row 2. Keeping headings out of the lookup range helps avoid confusing them with real records.
  2. Choose a cell for the name you want to find. In E1, type a clear heading such as Lookup name. In E2, type the name exactly as it appears in the list, for example Jordan Lee. Using a separate input cell is practical when you need to search for several people: you can replace the contents of E2 without changing the formula.
  3. Choose where the result should appear. Click an empty cell, such as F2, and consider adding a heading like Department in F1. The result cell should be separate from the source list and from the lookup-name cell, so the formula does not overwrite data it needs to search.
  4. Enter the exact-match formula. With names in A2:A100, departments in B2:B100, and the lookup name in E2, enter this in F2: =VLOOKUP(E2,$A$2:$B$100,2,FALSE). Press Enter. If E2 contains Jordan Lee and that name is listed in A2:A100 with Operations in the adjacent cell in column B, F2 displays Operations.
  5. Understand the formula arguments. E2 is the value Excel searches for. $A$2:$B$100 is the lookup range, with the name column at its left edge. The number 2 tells Excel to return a value from the second column of that range, which is column B. FALSE requests an exact match. The dollar signs keep the range fixed if you later copy the formula to another cell.
  6. Return another detail by changing the column number. If you want the email address from column C instead, include column C in the lookup range and use 3 as the return-column number. With names in A2:A100, departments in B2:B100, email addresses in C2:C100, and the lookup name in E2, enter =VLOOKUP(E2,$A$2:$C$100,3,FALSE). The result is the email from column C for the matching name.
  7. Copy the formula for more searches, if needed. If each row has a different name to look up, place names in E2, E3, and subsequent rows. With names in A2:A100, departments in B2:B100, and lookup names starting in E2, put =VLOOKUP(E2,$A$2:$B$100,2,FALSE) in F2, then fill it down. Excel adjusts the lookup cell from E2 to E3 and so on, while the dollar signs hold the source range in place. You can drag the fill handle at the lower-right corner of F2 down the column.
  8. Check the result against the source row. Confirm that the returned department or other detail belongs to the person shown in the lookup column. If the same name occurs more than once, VLOOKUP returns the result from the first matching row, not every match. Check the source list before relying on a result for a repeated name.
  9. Optionally show a clearer message when there is no match. If you prefer a message instead of the #N/A error, use IFNA around VLOOKUP. With names in A2:A100, departments in B2:B100, and the lookup name in E2, enter =IFNA(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"Not found"). Excel shows Not found only when the lookup returns #N/A; other formula errors are not hidden by IFNA.

For a lookup of whether a name exists, returning a related detail is often enough to confirm a match. If you want the matching name itself returned, you can make the name column the first column of the lookup range and use 1 as the return-column number. With names in A2:A100 and a lookup name in E2, the formula is =VLOOKUP(E2,$A$2:$A$100,1,FALSE). This returns the matching name or #N/A if no exact match is found.

Common Mistakes

Leaving out FALSE. If you omit the fourth VLOOKUP argument or use TRUE, Excel can perform an approximate match. Approximate matching has specific sorting requirements and may return an unexpected row for an ordinary name search. Include FALSE when you want an exact match.

Starting the range in the wrong column. VLOOKUP searches the leftmost column of the range, not whichever column seems relevant in the worksheet. If your selected range begins with departments and names are in the next column, Excel will search departments instead. Adjust the range so its first column contains the names.

Using an incorrect return-column number. The column number is counted from the start of the selected range, not from the worksheet’s A column. If the range is B2:D100, B is 1, C is 2, and D is 3. A number larger than the number of columns in the range causes a #REF! error.

Forgetting to lock the range before copying. If the formula is filled down and the source range shifts, later lookups might miss records or search the wrong rows. Use dollar signs around the range, as in $A$2:$B$100, to keep it fixed.

Expecting VLOOKUP to distinguish duplicate names. When two records have the same name, VLOOKUP returns the first one it encounters. A name alone may not identify a unique person. Use a more distinctive value, such as an employee ID, or combine identifying information in the source data before looking it up.

Tips

Keep the lookup column and return columns together in a clear table, and use descriptive column headings. Avoid blank rows inside the records, and check that each row’s name and details belong together. A clean, consistently organized list makes formulas easier to set up and results easier to verify.

Make sure names are stored consistently. An extra space at the start or end, a spelling difference, or a middle initial present in only one version can prevent an exact match. Names that look identical may also differ because one contains a nonbreaking space copied from a website. If you receive data from multiple sources, clean and standardize it before depending on lookups.

For a growing list, consider converting the source range into an Excel table. Select a cell in the data and choose Insert > Table, then confirm whether the table has headers. Tables expand as you add records, which can make a lookup range easier to maintain. If you use a regular range instead, remember to extend it when new rows are added beyond row 100.

If your task requires searching a column that is not the first column in the range, VLOOKUP cannot look left. You can rearrange a copy of the data so the search column comes first, or use XLOOKUP in Microsoft 365 or Excel 2021 and later. XLOOKUP can search in one column and return from another without requiring the search column to be on the left. This guide’s VLOOKUP method remains useful when you need compatibility with older workbooks.

When working on Excel for Mac, the VLOOKUP formula and comma-separated arguments are the same in a typical English-language installation. On Excel for the web, enter the formula in a cell just as you would on Windows. The main differences are interface details and shortcuts; the lookup logic does not change.

Troubleshooting

You see #N/A. Excel did not find an exact match. Check that the lookup name is spelled the same way in E2 and the source list, including spaces and punctuation. Confirm that E2 is a text value if the list contains text, and check that the selected range begins with the name column and includes the row you expect. If you use the optional IFNA formula, verify the lookup before treating Not found as proof that the name is absent.

You see #REF!. The return-column number is larger than the number of columns in the selected range, or the formula’s reference points to a deleted area. Count columns from the left edge of the range and make sure the range includes the column holding the value you want. For a range A:C, valid return numbers are 1, 2, and 3.

The formula appears as text instead of calculating. Check whether the cell is formatted as Text or whether an apostrophe appears before the equals sign. Change the cell format to General, edit the formula, and press Enter again. Also confirm that the formula begins with = rather than a leading space or apostrophe.

The result is the wrong person’s information. Look for duplicate names and verify that the formula’s return-column number points to the correct field. VLOOKUP returns the first exact match from top to bottom, so a duplicate name can make a technically valid result misleading. Search using a unique ID where possible.

The formula does not update when you change the lookup name. Make sure the formula refers to the cell you are editing, such as E2, rather than a different cell. If calculation is set to manual, choose Formulas > Calculation Options > Automatic in Excel for Microsoft 365 on Windows. You can also recalculate with F9 on Windows; on Mac, the recalculation shortcut can depend on the keyboard settings, so use the Formulas ribbon if needed.

You get an unexpected result after adding records. Check that the new rows fall inside the formula’s lookup range. A formula limited to $A$2:$B$100 will not find a name added on row 101. Extend the range or use a table-based setup that expands with the data.

Frequently Asked Questions

Can VLOOKUP find a name anywhere in a worksheet?

No. VLOOKUP searches only the first column of the range specified in the formula. Place the names in the range’s leftmost column, or choose a lookup function that can search a different column.

Does VLOOKUP find part of a name?

With FALSE, VLOOKUP looks for an exact match rather than a partial name. You can use wildcard characters with VLOOKUP for some text searches, but wildcard matches can be ambiguous, especially when several names share the same letters. For a straightforward person lookup, an exact name or unique ID is safer.

Why does VLOOKUP return the first duplicate name?

VLOOKUP returns the first matching row it encounters, reading down the first column of the range. It does not return every row with that name. Use a unique identifier or a method designed to return multiple matches if you need all duplicate records.

Can VLOOKUP return a value to the left of the name column?

Not directly. The search column must be the first column in the range, and VLOOKUP returns data from a column to its right. Rearrange the lookup data or use XLOOKUP in Microsoft 365 or Excel 2021 and later when you need to return a value from the left.

Should I use TRUE or FALSE for a name search?

Use FALSE for an exact name match. TRUE is for approximate matching and is generally not appropriate for finding a specific name; it can return a nearby value rather than the intended record.

Can I use the same formula to look up an email address instead of a department?

Yes. Include the email column in the lookup range and change the return-column number to the email column’s position within that range. Count from the left edge of the range, not from the worksheet’s column letters.

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.