Reference Cell in Another Sheet Based on Cell Value in Excel

When we work with large datasets in Microsoft Excel, it is common to reference data from one sheet to another. Instead of manually copying values, you can reference a cell in another sheet based on a specific cell value. This is Particularly useful for dashboards, reports, and automated calculations where data is spread across multiple sheets.

The INDEX-MATCH formula in Excel allows you to look up and retrieve data from another sheet based on a reference value. It is useful when you have large datasets and want to dynamically fetch values like prices, stock numbers, or IDs. This method works best with structured tables where the lookup value and the target value are in separate columns.

Steps:

➤ We have taken two sheets:

• Sheet1
• Sheet2

In Sheet2 we have Column A containing Product ID , Column B contains the product name, Column C contains the Price and Column D contains Stock.

➤ In Sheet1 we want to add Price data by referencing Sheet2. To do that, type the following formula in cell C2 in Sheet1 and press Enter:

➤ Drag the formula down from C2 to the rest of the Price column to fetch prices for all listed products. Excel will automatically adjust C2 to C3, C4, etc., for each row.

This method allows us to dynamically reference a cell in a different Excel sheet  which depends on a cell value. It is useful when you want to create a summary sheet that automatically pulls data from multiple sheets without manually typing formulas for each sheet.

Imagine, a company wants to track sales performance of its regional offices. Each region has its own sheet with monthly sales data. The summary sheet needs to pull total sales for a region based on a dropdown selection of the region name.

Steps:

➤ In our dataset we have Region A with monthly sales data.

➤ Region B sheet contains another set of monthly sales data.

➤ We have created another sheet named Summary where we will collect summary from sheet Region A and Region B by referencing.

➤ In the summary sheet enter the following dynamic reference formula in cell B2.

➤ Drag the formula down from B2 to the rest of the cell to autofill.

When you want to fetch data from different sheets dynamically depending on the value of a cell (for example, the selected region name), Excel does not allow direct dynamic sheet references. But with the help of the INDIRECT function, you can achieve this. This is useful when we want to manage multiple sheets for regions, months, or departments and create one summary sheet.

Steps:

➤ Open your Excel Workbook. We have the following worksheets: “North”, “South”, “West“.

➤ We have created a new sheet named Summary. In column A, type the names of sheets (North, South, West). In column B, list the products (Laptop, Mobile, Tablet).

➤ In the Summary sheet, enter the following Formula to fetch Sales_Q1 value, in cell C2.

Here,

• A2 → Sheet name
• B2 → Product name.
• MATCH(B2, INDIRECT(“‘” & A2 & “‘!A:A”),0) finds the row number of the product in that sheet.
• “‘!B” tells Excel to look into column B (Sales_Q1).

➤ Drag the formula using the fill handle from C2 to the rest of the cells.

➤ To fetch Sales_Q2 (column D), simply replace “!B” with “!C”. Then drag the formula down using fill handle for the rest of the rows.

The VBA (Visual Basic for Applications) helps us to reference data in one sheet from another based on a lookup value.

Here, we will fetch product details from Sheet 1 (ProductData) into Sheet 2 (LookupSheet) dynamically. This method is useful when we work with large datasets, closed workbooks, or when advanced logic is required.

Steps:

➤ We have a Dataset where Sheet 1 stores all product information. Sheet 2 is used by the sales team to look up details (name, price, stock) based on a Product ID

➤ Open the Excel workbook, press  Alt  +  F11  to open the VBA Editor window.
➤ Go to Insert > Module.

➤ A blank code window will open. Paste the following VBA code in the module window.

Function GetProductDetail(ProductID As Variant, ColumnNumber As Integer) As Variant
    Dim ws As Worksheet
    Dim rng As Range
    Dim f As Range
    'Assume Sheet1 has product data in columns starting from A
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A:A") 'Product IDs in column A
    Set f = rng.Find(What:=ProductID, LookIn:=xlValues, LookAt:=xlWhole)
    If Not f Is Nothing Then
        GetProductDetail = f.Offset(0, ColumnNumber - 1).Value
    Else
        GetProductDetail = CVErr(xlErrNA)
    End If
End Function

➤ After that, press  F5  in the VBA editor or go back to Excel.
➤ Go to Sheet 2 and enter the following Function or formula in cell B2.

It returns Product Name from Sheet 1. Drag the formula down using the fill handle to fill up the rest of the cells.

➤ In cell C2, enter the following formula and drag the formula down for all rows.

This formula returns Price from Sheet 1.

➤ In cell D2, enter the following formula and drag the formula down for all rows.

This formula returns Stock from Sheet 1.

Frequently Asked Questions

How to pull text from another sheet in Excel based on cell value?

You can use the formula =INDIRECT(“‘” & A1 & “‘!B2”), where A1 contains the sheet name. This way, Excel pulls text dynamically from the target sheet.

How do I link data from Sheet1 to Sheet2 in Excel?

Type =Sheet1!A1 in a cell of Sheet2. This links the value from cell A1 of Sheet1 directly.

How do I dynamically reference a cell in Excel?

Use the INDIRECT function. Example: =INDIRECT(“‘” & A1 & “‘!B2”) makes the reference change based on the text in A1.

Concluding Words

Referencing cells across sheets in Excel saves time and reduces errors. We have 3 methods that are most commonly used. You can download the excel file that contains all the dataset we have used in this article. Practice using that dataset and let us know your thoughts in the comment section below.