To use VLOOKUP between two sheets in the same Excel workbook, enter a formula on the sheet where you want the answer, point its lookup range to the other sheet, and use an exact match. For example, if Orders!A2 contains a product code and Products has product codes in column A and names in column B, enter =VLOOKUP(A2,Products!$A$2:$C$100,2,FALSE) on Orders to return the matching product name.
What VLOOKUP does between sheets
VLOOKUP searches down the first column of a range for a value, then returns a value from another column on the same row. The word vertical describes its search direction. When the data is on a different worksheet, the formula names that worksheet as part of the range reference; the lookup itself works the same way as it does within one sheet.
For a concrete example, imagine an Orders sheet with order numbers and product codes, and a Products sheet with product codes, product names, and prices. You can look up each code in Products and bring the corresponding name or price into Orders. The two sheets must be in the same workbook for a simple reference such as Products!A:C. If the data is in another workbook, you need a reference to that workbook, and Excel may ask you to locate or update the source file.
VLOOKUP does not combine entire sheets or match on any column you choose. Its lookup value must be found in the leftmost column of the range you give it. That limitation is important when you decide how to arrange the source data or whether another lookup function would suit the task better.
Prepare the two sheets
Before writing the formula, identify the lookup key: a value that appears in both sheets and identifies the record you want. It might be a product code, employee ID, invoice number, or email address. A descriptive name is often a weaker key because different people or products can share similar names.
- On the destination sheet, decide which cell contains the value to search for and where the returned result should appear.
- On the source sheet, place the lookup key in the leftmost column of the range. Put the information to return in a column to its right.
- Check that the keys use the same type and format on both sheets. A number stored as text may not match a numeric value, even if both look alike on screen.
- Check for duplicate keys. VLOOKUP returns the first matching row it encounters, so duplicate identifiers can make the result misleading.
- Keep a clear header row, but normally select the data rows rather than the header when making the lookup range.
For the example below, the Orders sheet has the product code in A2. On Products, column A contains codes, column B contains names, and column C contains prices. The data rows run from row 2 through row 100. This consistent layout makes it easy to check each part of the formula.
Step-by-step: enter the cross-sheet formula
- Open the destination sheet, such as Orders, and select the cell where the result should appear. If the product name should appear beside the code in A2, select B2.
- Type an equals sign and the VLOOKUP function name, followed by an opening parenthesis.
- Use the cell holding the value to search for as the first argument. In this example, that is A2.
- Enter a comma, then select the source range on Products. Include the lookup-code column and every column that might contain a result. The example range is Products!$A$2:$C$100.
- Enter another comma and the number of the return column within that selected range. Column A is number 1, B is number 2, and C is number 3.
- Enter a comma and FALSE to request an exact match, then close the parenthesis and press Enter.
- Check the returned value against the source row. If it is correct, copy the formula down for the remaining orders.
With product codes in Orders!A2:A500 and product codes, names, and prices in Products!A2:C100, enter this formula in Orders!B2 to return the product name for A2: =VLOOKUP(A2,Products!$A$2:$C$100,2,FALSE). If A2 contains P-104 and Products row 7 has P-104 in column A and USB-C Hub in column B, the result is USB-C Hub. The formula uses 2 because the name is the second column of the selected range, not because it is column B on the worksheet.
To fill the formula down, select B2 and drag the small fill handle at the cell’s lower-right corner, or copy and paste it into the cells below. Because A2 is a relative reference, Excel changes it to A3, A4, and so on. The dollar signs in the source range keep that range fixed as the formula is copied.
Understand the four VLOOKUP arguments
VLOOKUP has four arguments in this example. Each controls a different part of the search, and small changes can produce incorrect results without necessarily causing an obvious error.
- Lookup value: A2 is the value to find. It can be a cell reference or a value, but a cell reference is easier to copy down a list.
- Table array: Products!$A$2:$C$100 is the source range. The sheet name and exclamation mark identify the worksheet. The first column of this range must contain the lookup keys.
- Column index number: 2 returns the second column of the selected range. If you change the range’s left edge, the index counts from that new left edge.
- Range lookup: FALSE asks for an exact match. TRUE, or leaving this argument out, uses approximate matching, which has different requirements and can return an unexpected result if the first column is not sorted appropriately.
For a sheet name containing spaces, surround the sheet name with single quotation marks. For example, the reference to a range on a sheet called Product List begins with ‘Product List’! before the cell addresses. Excel usually adds the needed quotation marks automatically when you select a range on that sheet while building a formula.
Make the formula easier to maintain
The dollar signs in $A$2:$C$100 make the source range absolute, so it stays in place when you fill the formula down. Without them, Excel may shift the range by one row each time. You can add the dollar signs yourself or select the range reference while editing and press F4 on Windows to cycle through reference styles. On a Mac, the equivalent key combination can depend on the keyboard’s function-key settings; inserting the dollar signs directly is a reliable option.
A fixed range is simple, but it will not automatically include new rows added below row 100. If the source list grows regularly, format it as an Excel Table using Insert > Table, or Home > Format as Table, and give the table a descriptive name. A table expands as rows are added, which can reduce the need to revise a fixed endpoint. Confirm that any formula or range still refers to the intended columns after changing the source layout.
For occasional checks, you can click a formula cell and inspect the formula bar to see its source sheet, range, and column number. Consistent headers and a dedicated key column also make the workbook easier for another person to audit. If you are importing or pasting data, clean up spaces and inconsistent key formats before relying on the results; visually identical values do not always have identical underlying contents.
Common errors and how to fix them
- #N/A: No exact match was found. Check for extra spaces, spelling differences, missing records, or a number-versus-text mismatch. Also confirm that the lookup value is present in the first column of the selected range.
- #REF!: The column index asks for a column outside the selected range. For example, an index of 3 cannot return a value from a range that contains only two columns.
- The wrong value appears: Confirm that the column index points to the intended field and that duplicate keys are not causing VLOOKUP to return an earlier row. Make sure the final argument is FALSE for an exact lookup.
- The formula changes its source range when copied: Lock the range with dollar signs, as in $A$2:$C$100. Leave the lookup cell relative if each row should search for a different code.
- Excel displays the formula instead of a result: Check that the cell is not formatted as Text. Change the format to General, then edit and re-enter the formula. Also check that Show Formulas is not turned on.
- Unexpected approximate result: Add FALSE as the fourth argument. With approximate matching, the source keys generally need to be sorted in ascending order, and an approximate result is not the same as an exact match.
For a friendlier message when a code is missing, you can wrap the lookup in IFERROR. With the same Orders and Products layout, enter this in Orders!B2 to show Not found instead of an error if the lookup fails: =IFERROR(VLOOKUP(A2,Products!$A$2:$C$100,2,FALSE),"Not found"). For P-104, the result is still USB-C Hub; for a code absent from Products, the displayed result is Not found. Error handling changes what appears in the cell, but it does not fix missing or inconsistent source data, so check the underlying records when a match is expected.
When to use VLOOKUP or another method
VLOOKUP is a practical choice when the key is in the leftmost column of a tidy source range and the return column is to its right. It is familiar, works in many Excel versions, and is often adequate for a straightforward two-sheet lookup. Its main structural limitation is that the return column must be to the right of the lookup column, and the numeric column index can become wrong if columns are inserted or the range is rearranged.
In Microsoft 365 and Excel 2021 or later, XLOOKUP is a more flexible option for many new formulas. It can search one range and return a value from a separate range, including a column to the left of the key, and it offers an optional not-found result. For the example layout, with Orders!A2 holding the code and Products!A2:A100 and Products!B2:B100 holding codes and names, this returns the matching name: =XLOOKUP(A2,Products!$A$2:$A$100,Products!$B$2:$B$100,"Not found"). For P-104, it returns USB-C Hub; if there is no match, it returns Not found.
INDEX and MATCH can also perform flexible lookups in versions where XLOOKUP is unavailable, but they use a different combination of functions and require a little more formula setup. Choose a method based on the Excel versions used by everyone who needs the workbook, the direction of the lookup, and whether the formula needs to remain easy to maintain. Do not replace a working VLOOKUP solely for novelty if its range and matching behavior are clear and appropriate.
Frequently Asked Questions
Can VLOOKUP search a different sheet in the same workbook?
Yes. Put the sheet name and an exclamation mark before the source range, such as Products!$A$2:$C$100. If the sheet name has spaces, Excel uses single quotation marks around it.
Does VLOOKUP work between two separate Excel files?
Yes, but the formula must refer to the other workbook, and the source file must remain available for dependable updates. For a first-time link, selecting the source cells while building the formula is usually easier than typing a long external reference by hand.
Why does VLOOKUP return #N/A when the value is on the other sheet?
The value may differ in an invisible way, such as an extra space, or be stored as text on one sheet and as a number on the other. Also verify that the lookup key is in the first column of the selected range and that the formula uses FALSE for an exact match.
Can VLOOKUP return a value from a column to the left?
No. The lookup column has to be the first column in the table array, and VLOOKUP returns from a column to its right. In Microsoft 365 or Excel 2021 and later, XLOOKUP can return from a separate range to the left or right of the lookup range.
What happens if there are duplicate lookup values?
VLOOKUP returns the first match it finds from the top of the selected range. If duplicates should not exist, remove them or investigate the source data; if they are legitimate, decide which matching record the task actually requires before using a single-result lookup.
Related
- Replace in Excel: Find and Replace Text, Values, and More
- Resize Columns in Excel: Keyboard Shortcuts and Tips
- Remove Quotation Marks in Excel: Methods That Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MIN Formula Examples for Common TasksIf your numbers are in A2:A10, use =MIN(A2:A10) to return the smallest numeric value in that range. Excel’s…
- Excel Formula for Highest Value: MAX and Other OptionsTo find the highest value in Excel, use the =MAX(B2:B20) formula, assuming the numbers you want to compare…
- How to Filter and Sort Data in Excel: A Practical GuideTo filter and sort data in Excel, select a cell in your list, choose Data > Filter to…
- COUNTIF Excel Blank? Count Empty Cells CorrectlyTo count blank cells in Excel, use =COUNTIF(A2:A20,""), replacing A2:A20 with your range. COUNTIF can also count cells…
- Excel Subtotal Filtered Rows: Formula, Steps, and FixesYes. In Excel for Microsoft 365 on Windows, use the SUBTOTAL function to calculate from rows that remain…
- Generate Invoice Numbers in Excel With a Simple FormulaTo generate invoice numbers in Excel, enter ="INV-"&TEXT(ROW()-1,"0000") in cell A2 when row 1 is a header and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.