In this tutorial, we will learn about **How to Use one VLOOKUP to pull data from multiple sheets in Excel**.

**Example**

Here I have a table which consists of Various Services along with the given year. Now I have three sheets of different years.

2017

2016

2015

In every sheet, there are common items like Grocery Stores, Food, and Beverage Stores. Now the problem is in every sheet; the item Grocery store lies in different rows.

So now we want that when we change the year from the given list, then we should get the answer from the given year only.

In order to get the answer of Grocery Store, we will be applying VLOOKUP Formula by applying the following steps given below:

1. Write **=VLOOKUP** and Press Tab Key

Now there are 4 parameters

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

The first parameter **lookup_value** means the value to look for in the first column of a table.

2. Select the **Retail and Food Services** value **=VLOOKUP(A4,**

The second parameter **table_array** means the table from which to retrieve a value.

3. Go to **2017** year and select the **entire table =VLOOKUP(A4,’2017’!A:B**

The third parameter **col_index_num** means the column in the table from which to retrieve a value.

4. Write 2 **=VLOOKUP(A4,’2017’!A:B,2,**

The fourth parameter **range_lookup** means TRUE = approximate match and FALSE = exact match.

5. Write **FALSE or 0** to get the answer as exact match **=VLOOKUP(A4,’2017’!A:B,2,0**

6. Close the **bracket** and press **Enter =VLOOKUP(A4,’2017’!A:B,2,0)**

7. You will get your answer for **Retail and Food Services** from the **Sheet 2017**

Now, if you change the year from 2017 to 2016 or 2015, then your answer will not change.

The reason behind not getting changed is that you are taking ‘’2017’!A: B sheet reference in the name of a table array

So we want that in VLOOKUP Formula, when the table array comes, then it should change the reference on the basis of the yellow cell. In order to do this, follow the given steps below:

1. Copy the **‘’2017’! A: B** sheet reference and paste it on another cell

Now instead of table_array we will be applying INDIRECT Formula

2. Write **INDIRECT** Formula

3. Write **Double Quotation (“) Single Quotation (’)** and again **Double Quotation (“) INDIRECT (”’”**

4. Write **Ampersand (&) INDIRECT (”’”&**

5. Choose the **yellow cell** and press **F4 INDIRECT (”’”&$B$3**

6. Again write **Ampersand (&)** along with **Single Quotation (’) and Double Quotation (“) INDIRECT (”’”&$B$3”’**

7. Write **A:B INDIRECT (”’”&$B$3”’A:B”**

8. Press **Enter** and you will get your answer.

9. Also if you **change the year** then also you will get the answer of the Grocery Store while changing the year.

## Leave a Reply