We can apply the **VLOOKUP** and **INDIRECT functions** to look up data in a variable sheet name in Excel. With the following illustrations, we will learn how to use these functions in simple steps.

*Figure 1- Results of using the Excel VLOOKUP and INDIRECT function*

**General Formula**

**=VLOOKUP(val,INDIRECT("'"&sheet&"'!"&"range"),col,0)**

**Formula**

**=VLOOKUP($B5,INDIRECT("'"&C$4&"'!"&"B5:C11"),2,0)**

**Setting up the Data**

- We will assign a name to the blank sheet called “
**Wk1.**” - In this sheet, we will set up our hypothetical data titled
**“Groceries”**and**“Quantity”**in**Columns B and C**as displayed in the figure below**.**

* Figure 2 – Setting up Datasheet”Wk1.”*

- We will create identical sheets with different values for the quantity section as found in the “
**Wk1**” in sheets**Wk2,**and**Wk3 respectively.**

* Figure 3- Setting up Datasheet “Wk2.”*

* Figure 4 – Setting up Datasheet “Wk3.”*

- We will create a 4th sheet, titled “
**Budlog**.” Here we will summarize our data. - In this sheet, we will enter the following names in
**Cells C4**to**E4**,**“wk1”, “wk2**”, and “**wk3.**” - We will set up our hypothetical list of data titled
**Groceries**in**Columns B.**

* Figure 5- Setting up Data Sheet “Budlog”*

- To lookup data, we will pick a cell in the sheet:
**budlog**”, assign the VLOOKUP formula and extract the data by creating a dynamic reference to the sheet name for the Budget sheet.

**Applying the VLOOKUP and INDIRECT function**

- We select the
**Cell C5** - We will enter the formula below on
**Cell C5**

**=VLOOKUP($B5,INDIRECT("'"&C$4&"'!"&"B5:C11"),2,0)** - We will
**press the enter key**

* Figure 6- Applying the VLOOKUP and INDIRECT functions*

- We select
**Cell C5**again - Next, we use the fill handle tool (the small plus sign found at the bottom of
**Cell C5**) to drag the formula from**Cell C5 to Cell E11**

* Figure 7- Result of using the VLOOKUP and INDIRECT functions*

**Explanation**

**=VLOOKUP($B5,INDIRECT("'"&C$4&"'!"&"B5:C11"),2,0)**

The **VLOOKUP function** will check all data inside the **INDIRECT function**. It will use the dynamic reference to name the sheets prepared in the **Excel workspace**. The mixed reference is represented by **$B5** which will lock **Column B** and allow for copying across the table.

**=INDIRECT("'"&C$4&"'!B5:C11")**

The **INDIRECT function** in this situation will refer to the column headings in row 4 and match it against the sheet names in the workbook (i.e., “wk1”, “wk2”, “wk3”)

**Note**

**If the VLOOKUP function does not find a match with our lookup value, it will return with a #N/A error.**

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment