To look up a value from one Excel sheet in another, use VLOOKUP with the source sheet in the table-range argument. For example, if an order ID is in A2 and a sheet named Customers has IDs in column A and names in column B, enter =VLOOKUP(A2,Customers!$A$2:$B$100,2,FALSE) on the order sheet. It returns the matching customer name, or an error if Excel cannot find the ID.
How a two-sheet VLOOKUP works
VLOOKUP searches for a value in the first column of a selected range, then returns a value from another column in the same row. In this example, the value to find is the order ID in A2. The lookup table is on the Customers sheet, and the return value is in its second column. The final argument, FALSE, requests an exact match.
The formula follows this structure: =VLOOKUP(lookup_value,table_array,col_index_num,FALSE). The lookup value can be a cell reference or, in some cases, a typed value. The table array is the range Excel searches. The column index is counted from the left edge of that range—not from the worksheet’s column A. FALSE tells Excel not to use an approximate match.
For the example range Customers!A2:B100, column A is position 1 and column B is position 2. Therefore, 2 returns the customer name. If the selected range were Customers!B2:D100, its first column would be worksheet column B, and its third column would be worksheet column D. Choosing the range and index together is essential.
Prepare the two sheets
Use one sheet for the records that need information and another for the reference table. For instance, an Orders sheet might contain an order ID in each row, while Customers contains one row per customer with an ID and name. The ID is the common key: it connects the rows even if the sheets are organized differently.
Before entering a formula, check that the lookup column is the leftmost column of the source range. VLOOKUP cannot search to the left of that column. Also check that each key is stored consistently. An ID stored as a number on one sheet and as text on the other may look identical but fail to match. Extra spaces, leading zeros, and inconsistent spelling can cause similar problems.
- Put the key to search for in a known cell on the first sheet, such as Orders!A2.
- On the source sheet, put the matching key in the first column of the selected range.
- Place the value to return in a column to the right of that key.
- Use a header row if it helps people understand the data, but normally exclude that row from the lookup range.
For the main example, assume Orders!A2 contains C104, and Customers!A2:B100 contains customer IDs in column A and names in column B. If C104 appears in Customers!A7 and B7 contains Maya Chen, the formula returns Maya Chen. The row number does not need to be the same on both sheets; VLOOKUP finds the matching key and returns a value from that source row.
Enter the formula step by step
- Open the sheet where you want the answer, such as Orders, and select the result cell beside the first order.
- Type an equals sign and VLOOKUP, followed by an opening parenthesis.
- Select the lookup cell, such as A2, then type a comma.
- Click the Customers sheet tab and select the source range, such as A2:B100. Excel inserts the sheet reference into the formula.
- Type a comma, enter 2 because the answer is in the second column of the selected range, then type another comma and FALSE.
- Close the parenthesis and press Enter. If the result is correct, fill the formula down for the remaining rows.
With the stated layout—Orders!A2 contains the ID, and Customers!A2:A100 contains IDs while B2:B100 contains names—the complete formula is =VLOOKUP(A2,Customers!$A$2:$B$100,2,FALSE). If A2 is C104 and the matching source row has Maya Chen in column B, the result is Maya Chen. The dollar signs keep the lookup range fixed when the formula is copied down; they do not change which value is looked up.
Use the formula with sheet names and ranges
When a sheet name has spaces or punctuation, surround it with single quotation marks in the reference. For example, a sheet named Customer List is referenced as ‘Customer List’!A2:C500. Excel often adds the quotation marks automatically when you select the range by clicking the sheet tab, but typing the reference manually requires the correct syntax.
Suppose Orders!A2 contains an ID, while Customer List has IDs in column A, names in B, and regions in C. With the source table in A2:C500, the formula =VLOOKUP(A2,'Customer List'!$A$2:$C$500,3,FALSE) returns the region from the third column of the selected range. For this formula, the layout is specifically that the lookup ID is in Orders!A2 and the return region is in Customer List column C. If the matching row has West in C, the result is West.
Use a range large enough to include all relevant source records. A fixed range ending at row 100 will not include a new record added at row 101. You can extend the range when data grows, or convert the source data into an Excel Table and use a table-based approach. If the sheet name itself contains an apostrophe, Excel doubles that apostrophe inside the quoted sheet reference.
Copy the formula down safely
In the main formula, A2 is a relative reference, so it becomes A3, A4, and so on when copied down. The dollar signs in $A$2:$B$100 make the lookup range absolute, so it stays in place. This combination is usually what a row-by-row lookup needs: a changing lookup cell and a fixed reference table.
After checking the first answer, use the fill handle—the small square at the lower-right corner of the selected cell—to fill the formula into adjacent rows. You can also copy and paste it. On Windows, Ctrl+C and Ctrl+V copy and paste; on Mac, use Command+C and Command+V. If you double-click the fill handle, Excel may fill down alongside adjacent data, but check the last filled row because gaps in neighboring columns can affect how far it extends.
For a larger or frequently updated source list, an Excel Table can make the source range expand as rows are added. Tables also use readable column names in formulas, though the exact structured reference depends on the table and column names. If you use a fixed cell range instead, remember to revise its last row when the source data grows. An existing formula will not automatically know about records outside its stated range.
Common errors and how to fix them
#N/A: The exact lookup value was not found in the first column of the range. Check for leading or trailing spaces, text-versus-number differences, misspelled keys, and whether the correct source range includes the row. For IDs with leading zeros, decide whether the zeros are meaningful and store both sides consistently; converting an ID carelessly can change its identity.
Wrong result: Confirm that the final argument is FALSE and that the column index points to the intended return column. If it is omitted or set to TRUE, VLOOKUP uses approximate matching, which requires the first column to be sorted appropriately and can return an unexpected result when the data is not arranged for that behavior. For ordinary ID or name matching, exact match with FALSE is the safer choice.
#REF!: The column index is larger than the number of columns in the selected range, or the formula’s reference has otherwise become invalid. For example, a range with two columns cannot return column 3. Make the table range wide enough or correct the index.
Formula displays instead of a result: The cell may be formatted as Text, or Excel may be set to show formulas. Change the cell format to General, re-enter the formula, and check the Formulas tab for Show Formulas. Also confirm that the formula begins with an equals sign and that calculation is not set to Manual if results appear stale.
Duplicate keys: VLOOKUP returns the first matching row it encounters in the selected range. If the source contains multiple rows with the same ID, it does not combine them or choose the newest entry automatically. Remove unintended duplicates or define a specific rule for which record should be returned.
VLOOKUP compared with XLOOKUP
VLOOKUP remains useful when working with established workbooks and when the lookup column is the first column of the table range. Its main structural limitation is that it returns values only from columns to the right of the lookup column. It also uses a numeric column index, which can become wrong if someone inserts or rearranges columns within the range.
XLOOKUP is a more flexible alternative in Microsoft 365 and Excel 2021 or later. It accepts separate lookup and return ranges, can return a value from either side of the key, and has an optional argument for a custom not-found result. For a workbook that must open in older Excel versions, VLOOKUP may be more compatible. For newer versions, consider whether XLOOKUP makes the formula easier to read and maintain.
For the same two-sheet example, if Orders!A2 contains an ID, Customers!A2:A100 contains IDs, and Customers!B2:B100 contains names, the XLOOKUP formula =XLOOKUP(A2,Customers!$A$2:$A$100,Customers!$B$2:$B$100,"Not found") returns the matching name or Not found when there is no match. Those cell ranges are the assumed layout, and if A2 is C104 with Maya Chen in the matching name cell, the result is Maya Chen. XLOOKUP is not available in some older perpetual Excel releases, so check the target workbook’s users and version before replacing a compatibility-oriented formula.
Frequently Asked Questions
Can VLOOKUP look across two different Excel sheets?
Yes. Put the lookup value on one sheet and refer to the source range on another by including its sheet name and an exclamation mark, such as Customers!A2:B100. Use exact match, typically by entering FALSE as the final argument.
Does the lookup value have to be in column A?
No. It must be in the first column of the range you select, but that column can be any worksheet column. For example, if the key is in D and the return value is in E, select a range beginning in D and use column index 2.
Why does VLOOKUP return #N/A when the values look the same?
The values may differ in their underlying type or contents. One may be a number and the other text, or one may contain an extra space or a different leading-zero pattern. Compare the source and lookup cells, then standardize the data rather than changing IDs without checking what the digits mean.
Can VLOOKUP return more than one matching result?
VLOOKUP returns one result: the first match in the lookup range. It does not spill every matching row. In Microsoft 365 or Excel 2021 and later, FILTER can return multiple matching records when that is the goal; older Excel versions need a different approach.
Will the formula update when I add rows to the other sheet?
Only if the new rows are included in the lookup range. A fixed reference ending at row 100 will not search row 101. Expand the range or use an Excel Table-based setup that grows with the source data, then verify that the formula points to the intended table columns.
Practical checks before relying on the result
A lookup result is only as dependable as the key and source table behind it. Before using returned values in reports, verify a few known IDs manually, check whether duplicate keys exist, and confirm the source list is current. If a result is used in a downstream calculation, consider how missing matches should be handled rather than treating every error as a valid zero or blank.
Finally, keep the formula readable for the next person who opens the workbook. Use clear sheet and table names, keep the key column consistent, and document any non-obvious choice such as returning the first duplicate. A two-sheet VLOOKUP is straightforward once its three essentials are clear: exact key, source range beginning with that key, and a correct return-column index.
Related
- Access Database Import from Excel: Steps and Tips
- Excel Attendance Format: A Clear Monthly Attendance Tracker
- Excel Attendance File to Print: Set Up and Print It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Show Formulas in Excel: Keyboard Shortcut GuideTo show formulas in Excel for Microsoft 365 on Windows, press Ctrl+` (Ctrl plus the grave accent key,…
- VLOOKUP Excel: Extended Uses, Examples, and LimitsVLOOKUP can do more than retrieve one value from a simple list: it can use exact or approximate…
- Vlookup with multiple criteria?Learn how to use VLOOKUP with multiple criteria in Excel, build a reliable helper key, avoid common errors,…
- Aggregate Excel Columns with Formulas and PivotTablesTo aggregate Excel columns, use a function such as SUM, AVERAGE, COUNT, MIN, or MAX across the cells…
- How to Use the PMT Function in Excel for PaymentsUse Excel’s PMT function to calculate a fixed payment for a loan or other payment plan with a…
- Excel Find and Replace Paragraph Mark: The Right MethodIn Excel for Microsoft 365 on Windows, press Ctrl+H, click the Find what box, and press Ctrl+J to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.