To match records between two Excel sheets, put the value you want to find in the first column of the lookup range, then use VLOOKUP with an exact match. For example, if Sheet1 has IDs in column A and Sheet2 has IDs in column A with the values to return in column C, enter =VLOOKUP(A2,Sheet2!$A$2:$C$100,3,FALSE) in Sheet1!B2 and fill it down. This returns the value from column C for the matching ID, or #N/A if no exact match exists. VLOOKUP is useful for bringing related details from one list into another, such as a product price, customer status, or department.
What You’ll Need
- An Excel workbook containing both sheets. They can be in separate workbooks, but using sheets in the same workbook is simpler.
- A shared identifier in both lists, such as an order number, product code, or email address. The identifier should be in the first column of the lookup range.
- A clear idea of which value you want to return from the second sheet, such as a price or category.
The example below assumes a sheet named Sheet1 has customer IDs in column A, and a sheet named Sheet2 has customer IDs in column A, names in column B, and statuses in column C. The goal is to bring each customer’s status into Sheet1. You can substitute your own sheet names, columns, and row limits.
Step-by-Step
- Check that both sheets have a common key. Choose a field that identifies the same record in both lists. An order ID is usually safer than a person’s name, because different people can share a name and names may be spelled or formatted differently. Make sure the key appears once per record in the lookup list if you need one unambiguous result.
- Confirm the lookup column is first in the range. VLOOKUP searches only the leftmost column of the range you give it. In this example, the IDs are in Sheet2 column A and the status to return is in column C, so the lookup range must start at A and extend through C. If the ID is in column D and the desired value is in column B, VLOOKUP cannot return from B using a range beginning at D; rearrange the lookup data or use a different lookup method.
- Choose a destination column on the first sheet. On Sheet1, insert or select a blank column next to the records, and add a clear heading such as Status. Click the first result cell beneath the heading, which is B2 in this example. Keeping the result beside its matching ID makes it easier to review.
- Enter the lookup formula. With the lookup value in Sheet1!A2 and the lookup table in Sheet2!A2:C100, enter this in Sheet1!B2:
=VLOOKUP(A2,Sheet2!$A$2:$C$100,3,FALSE). A value of 3 tells Excel to return the third column within the selected range, which is column C. FALSE requests an exact match; use it for identifiers and most record-matching tasks. The dollar signs lock the lookup range so it does not shift when the formula is copied. - Press Enter and check the result. If the ID in A2 appears in Sheet2 column A, B2 should display the corresponding status from column C. For example, if the ID is C-104 and its status in Sheet2 is Active, the formula returns Active. If Excel displays #N/A, the exact key may not be present, or the two cells may differ in type or contents; see Troubleshooting below before assuming the source data is missing.
- Fill the formula down the list. Select B2 and use the fill handle, the small square at the lower-right corner of the selected cell, to drag the formula down alongside the records. If the adjacent column has a continuous list, double-click the fill handle to fill down to the end of that list. Check the last filled row to ensure every record has a result or a clearly understood no-match result.
- Use an error message instead of #N/A if helpful. With IDs in Sheet1!A2:A100 and the lookup table in Sheet2!A2:C100, use this alternative in Sheet1!B2:
=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$C$100,3,FALSE),"Not found"). It displays Not found for any error, not only a missing ID, so inspect unusual results rather than treating every message as proof that the ID is absent. The basic VLOOKUP formula is often better while you are checking the data, because it leaves errors visible. - Verify several matches against the source. Check a result near the top, one in the middle, and one near the bottom of the list. Confirm each returned value belongs to the same ID, especially if the lookup list contains repeated keys. When the results are correct, you can keep the formulas or copy the result column and use Home > Paste > Paste Values to replace formulas with fixed values.
If a sheet name contains spaces, enclose it in single quotation marks in the reference. With the lookup key in Sheet1!A2 and the range in columns A through C of a sheet called Lookup Data, the formula in Sheet1!B2 is =VLOOKUP(A2,'Lookup Data'!$A$2:$C$100,3,FALSE). Excel may add the quotation marks automatically when you select the range with the mouse while building the formula.
Common Mistakes
- Leaving out FALSE. If you omit the final argument, VLOOKUP uses approximate matching by default. For a normal record match, this can return a plausible but incorrect value, especially when the lookup column is not sorted. Use FALSE for exact matches.
- Starting the range in the wrong column. The key must be in the first column of the selected table range. The column number counts from the range’s first column, not from column A on the worksheet. For a range beginning at B, a return value in worksheet column D is column 3 of that range.
- Using a relative range that shifts. When a formula is filled down, a reference such as A2:C100 may move to A3:C101. Lock the lookup range with dollar signs, as in $A$2:$C$100. The lookup value, A2, should generally remain relative so it changes to A3, A4, and so on.
- Expecting VLOOKUP to distinguish duplicate keys. If the lookup column contains the same ID more than once, VLOOKUP returns the first matching row. Remove or investigate duplicates if each ID is supposed to identify only one record.
- Confusing numbers with text. An ID that looks like 00125 may be stored as text in one sheet and as the number 125 in another. Leading zeros can also be lost if an ID was entered as a number. Format and clean the source values consistently before relying on the results.
Tips
- Convert each list to an Excel table with Insert > Table if the data will grow regularly. Tables expand when you add rows, which helps avoid lookup ranges that stop before the newest records. Check the formula range when you add data.
- Use a stable unique code rather than a description, full name, or other field that can change. If there is no single unique field, you may need to create a consistent combined key in both lists before matching.
- Keep the lookup range limited to the columns and rows you need. A fixed range is easy to understand; a whole-column range can also work, but may be less efficient in a very large workbook.
- For a larger task, consider sorting and checking the source lists for duplicates before matching. Sorting is not required when the formula uses FALSE, but it can make records easier to inspect.
- In Microsoft 365 and Excel 2021 or later, XLOOKUP is another option and can look to the left as well as the right. VLOOKUP remains useful when a workbook needs to work with older Excel versions.
Troubleshooting
The formula returns #N/A. Confirm the key really exists in the source list, then compare the two cells closely. Look for leading or trailing spaces, hidden characters, different punctuation, and text-versus-number differences. You can inspect a cell in the formula bar and standardize the source values; for text copied from websites, extra spaces or nonprinting characters can be hard to see. Also check that the correct lookup column is the first column of the selected range.
The formula returns the wrong person’s or product’s details. Check the column index and the order of the columns in the range. Then look for duplicate keys: VLOOKUP returns the first match from the top, not all matching rows. If the key should be unique, identify and correct duplicates in the source list.
Excel shows a formula error as soon as you enter it. Verify the sheet name and range references, and make sure the formula starts with an equals sign and has balanced parentheses. If a sheet name has spaces, use single quotation marks around it. Some regional Excel settings use semicolons rather than commas between arguments; if Excel rejects the comma version, use the separator required by your settings.
New rows are not included. Extend the lookup range to cover the added records or use a table-based approach. After changing a formula, fill it down again and check that the range remains fixed as intended. If a workbook is set to manual calculation, choose Formulas > Calculation Options > Automatic, or press F9 to recalculate.
The formula displays as text instead of calculating. Select the cell, change its number format from Text to General using Home > Number Format, then edit the formula (press F2 and Enter). Also check that there is no apostrophe before the equals sign.
Frequently Asked Questions
Can VLOOKUP match data across two separate workbooks?
Yes. Open both workbooks while creating the formula, select the source range in the other workbook, and Excel will create an external reference. Keep the source file in a stable location; if it is moved or renamed, the link may need updating.
Does VLOOKUP change or combine the two sheets?
No. It returns a value into the cell containing the formula; it does not merge the source sheets or alter the original lookup list. You can later copy the result cells and paste values if you want to keep a static snapshot.
Can VLOOKUP return more than one column?
A single standard VLOOKUP formula returns one column from the range. You can use separate formulas with different column numbers to return several fields, or use XLOOKUP in Microsoft 365 or Excel 2021 and later for a different approach.
Why does VLOOKUP return the wrong match even though the ID looks right?
Check for duplicates and ensure the formula ends with FALSE. Also compare the underlying values, not just how they appear: text and numbers can look alike, and cells may contain extra spaces or leading zeros.
Can I use VLOOKUP when the return column is to the left of the ID?
No. VLOOKUP returns values only from columns to the right of its first, lookup column. Rearrange the data so the key is first, or use XLOOKUP in Microsoft 365 or Excel 2021 and later, which can return a value from either side.
Related
- How to Hide Long Text in Excel Cells Without Deleting It
- Hide Gridlines in Excel: Keyboard Shortcut and Steps
- How to Hide Cells in Excel Without Deleting Data
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Day Timeline: Build a Daily Schedule or Date FilterAn Excel day timeline can mean either a daily schedule laid out across worksheet columns or a PivotTable…
- Excel MAX Function: Find the Highest Value in a RangeTo find the largest number in Excel, enter a formula such as =MAX(B2:B10) in an empty cell, replacing…
- How to Sum in Excel If a Cell Contains a Specific WordTo add numbers when a related cell contains a particular word, use SUMIF with asterisks around the word.…
- Inventory Tracking Spreadsheet: Setup, Formulas, and TipsAn inventory tracking spreadsheet is a worksheet that records what items you have, where they are, and when…
- Attendance Percentage Formula in ExcelTo calculate attendance percentage in Excel, divide the number of sessions attended by the total number of sessions…
- How to Close the Formula Bar in Excel on Any DeviceTo close—or hide—the formula bar in Excel, open the View tab and clear the Formula Bar checkbox. In…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.