Using Excel VLOOKUP() function across two sheets

Learn using excel vlookup() function across two sheets with practical examples, diagrams, and best practices. Covers excel, excel-formula, vlookup development techniques with visual explanations.
Tags:
Categories:

Mastering VLOOKUP Across Multiple Excel Sheets

Excel spreadsheet with VLOOKUP formula spanning two sheets, illustrating data retrieval.

Learn how to effectively use Excel's VLOOKUP function to retrieve data from different worksheets, enhancing your data analysis and reporting capabilities.

Excel's VLOOKUP function is a powerful tool for looking up specific data in a table and returning a corresponding value. While commonly used within a single worksheet, its true potential often shines when you need to pull information from one sheet into another. This article will guide you through the process of using VLOOKUP across two different sheets, providing practical examples and best practices.

Understanding the VLOOKUP Syntax

Before diving into cross-sheet lookups, let's quickly review the basic VLOOKUP syntax. Understanding each argument is crucial for successful implementation:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value: The value you want to find in the first column of the table_array.
  • table_array: The range of cells where VLOOKUP will search for the lookup_value and the return value. This is where the cross-sheet reference comes into play.
  • col_index_num: The column number in the table_array from which to return the value. The first column in table_array is 1, the second is 2, and so on.
  • [range_lookup]: An optional logical value that specifies whether you want an exact match (FALSE or 0) or an approximate match (TRUE or 1). For most cross-sheet data retrieval, you'll want an exact match.
flowchart TD
    A["Start VLOOKUP"] --> B{"Lookup Value (e.g., Product ID)"}
    B --> C["Specify Table Array (e.g., 'Sheet2'!A:C)"]
    C --> D{"Column Index Number (e.g., 2 for Price)"}
    D --> E{"Range Lookup (FALSE for Exact Match)"}
    E --> F["Return Corresponding Value"]
    F --> G["End VLOOKUP"]
    style A fill:#f9f,stroke:#333,stroke-width:2px
    style G fill:#f9f,stroke:#333,stroke-width:2px

VLOOKUP function flow with cross-sheet reference

Performing a VLOOKUP Across Two Sheets

The key to performing a VLOOKUP across sheets lies in correctly referencing the table_array. When your data is on a different sheet, you simply include the sheet name followed by an exclamation mark (!) before the cell range.

Let's consider a scenario:

  • Sheet1: Contains a list of Order IDs and you want to retrieve the Customer Name for each order.
  • Sheet2: Contains a master list of Order IDs, Customer Names, and Order Dates.

Your goal is to populate the Customer Name column in Sheet1 using the Order ID as the lookup_value from Sheet2.

=VLOOKUP(A2, Sheet2!A:C, 2, FALSE)

Example VLOOKUP formula for cross-sheet data retrieval

In this formula:

  • A2: This is your lookup_value on Sheet1 (e.g., the first Order ID).
  • Sheet2!A:C: This is your table_array. It tells Excel to look in columns A through C on 'Sheet2'. The lookup_value (Order ID) must be in the first column of this range (column A on Sheet2).
  • 2: This is the col_index_num. Since Customer Name is in the second column of our table_array (column B on Sheet2), we use 2.
  • FALSE: Ensures an exact match for the Order ID.

Steps to Implement Cross-Sheet VLOOKUP

Follow these steps to apply the VLOOKUP function across two sheets:

1. Identify Your Data

Determine which sheet contains the data you want to retrieve (the source sheet, e.g., 'Sheet2') and which sheet you want to populate (the destination sheet, e.g., 'Sheet1'). Identify the common column (the lookup_value) between both sheets.

2. Select Destination Cell

On your destination sheet (e.g., 'Sheet1'), select the cell where you want the first result of your VLOOKUP to appear. For instance, if you're populating Customer Name in column B, select B2.

3. Start the VLOOKUP Formula

Type =VLOOKUP( into the selected cell. Then, click on the cell containing your lookup_value on the current sheet (e.g., A2 on 'Sheet1'). Add a comma ,.

4. Define the Table Array

Now, navigate to your source sheet (e.g., 'Sheet2') by clicking on its tab. Select the entire range of columns that contains both your lookup_value (in the first column of the selection) and the data you want to retrieve. For example, A:C. Excel will automatically add the sheet name to your formula (e.g., Sheet2!A:C). Add a comma ,.

5. Specify Column Index Number

Count the column number within your selected table_array (e.g., A:C) that contains the data you want to return. If Customer Name is in column B of 'Sheet2' and your table_array is A:C, the col_index_num is 2. Type this number, followed by a comma ,.

6. Set Range Lookup

Type FALSE for an exact match. Close the parenthesis ) and press Enter. The formula should now retrieve the correct data.

7. Drag Down the Formula

Click on the cell containing your newly entered VLOOKUP formula. Drag the fill handle (the small square at the bottom-right corner of the cell) down to apply the formula to the rest of your data.