How to Select from Drop Down and Pull Data from Different Sheet in Excel

In this article, we will learn to select from the drop-down and pull data from a different sheet in Excel. Sometimes, we have a large dataset in a sheet. But we need some specific data from that sheet. So, we need to pull data from that sheet to another sheet. Here, we will discuss 4 easy methods to select from the drop-down and pull data from a different sheet. In these methods, we will create the drop-down list first and then use different functions to pull data.

How to Select from Drop Down and Pull Data from Different Sheet in Excel: 4 Ways

1. Use VLOOKUP Function to Select from Drop Down and Pull Data from Different Sheet in Excel

In this method, we will use the VLOOKUP function to select from the drop-down and pull data from a different sheet in Excel. The VLOOKUP function looks for a value in the leftmost column of a table and then returns a value from the same row from a column you specify.

Dataset Introduction

Here, we will have a dataset that contains sales of three different months of some sellers in three different sheets.

This is sales of January and it is stored in the sheet named Jan.

Here, it is the sales of February and it is stored in the sheet named Feb.

Again, we have the sales of March and it is stored in the sheet named Mar.

STEP 1: Create the Drop Down Menu

Let’s follow the steps below to create the drop-down menu.

In the first place, select another sheet and create a structure of the dataset. We have created the structure of the dataset in a sheet named VLOOKUP function. We have written the Month Name and Sheet Names in Column E.

Secondly, to create the drop-down, select Cell E3.

Thirdly, go to the Data tab and select the Data Validation option. It will open the Data Validation window.

After that, select List from the Allow field and then, select the Source field.

Now, you need to select the options you want to add to the drop-down menu. We have selected Cell E8 to E10. Click OK to proceed.

After clicking OK, you will see a drop-down menu in Cell E3. You can choose the sheets from this drop-down menu.

STEP 2: Pull Data from Different Sheet

Follow the steps below to pull data from different sheets.

Select Cell C5 at first and type the formula:

=VLOOKUP($B5,INDIRECT("'"&$E$3&"'!$B$5:$C$11"),2,FALSE)

Press Enter.

Here, we have used the INDIRECT function inside the VLOOKUP function.

🔎 How Does the Formula Work?

⇒ INDIRECT(“‘”&$E$3&”‘!$B$5:$C$11”)

We have used the INDIRECT function. The INDIRECT function returns the reference specified by a text string. It has one compulsory argument. Here, our argument refers to the text string stored in Cell E3. We have stored a sheet name in Cell E3. Suppose, we have stored Jan in Cell E3. Then, it will return the B5:C11 range of the Jan sheet.

⇒ VLOOKUP($B5,INDIRECT(“‘”&$E$3&”‘!$B$5:$C$11”),2,FALSE)

This formula will look for the value stored in Cell B5 in the table array of the Jan sheet. Here, the column index number is 2 and we are looking for an exact match. So, we have used False.

Finally, use the Fill Handle to see results in the rest of the cells.

Now, if you change the Month Name using the drop-down menu, the dataset will be automatically updated.

We can also see the updated results for March.

2. Select from Drop Down and Pull Data from Different Sheet with Excel INDIRECT Function

In the second method, we will use the INDIRECT function to select from the drop-down and pull data from a different sheet. We will use the previous dataset here.

STEP 1: Drop Down Menu Creating

We will create the drop-down menu first. Let’s observe the steps below to create the drop-down menu.

In the beginning, select another sheet and create a structure of the dataset. We have created the structure of the dataset like the datasets in another sheet and also, written the Month Name and Sheet Names in Column E.

After that, go to the Data tab and select the Data Validation option. It will open the Data Validation window.

Next, select List from the Allow field and then, select the Source field.

Now, you need to select the options you want to add to the drop-down menu. We have selected Cell E8 to E10. It contains the names of the sheets. Click OK to proceed.

In the end, you will see a drop-down menu in Cell E3. You can choose the sheets from this drop-down menu.

STEP 2: Data Pulling from Different Sheet

We have created the drop-down menu in the previous section. Now, we will discuss how to pull data from a different sheet.

Let’s follow the steps below to learn more.

Select Cell C5 at first and type the formula:

=INDIRECT("'"&$E$3&"'!C6")

Here, we have used the INDIRECT function. The INDIRECT function returns the reference specified by a text string. It has one compulsory argument. Our argument refers to the text string stored in Cell E3. We have stored a sheet name in Cell E3. Suppose, we have stored Jan in Cell E3. Then, it will return the Cell C6 of the Jan sheet.

After typing the formula, hit Enter and drag the Fill Handle It will copy the same formula in the rest of the cells.

Now, select Cell C7 and write C7 instead of C6. Then, hit Enter.

After that, select Cell C8 and write C8 instead of C6. Then, hit Enter.

Do the same for the rest of the cells and you will see results like below.

If you change the Month Name using the drop-down menu, the dataset will be automatically updated.

Again, we can also see the updated result for March.

3. Choose from Drop Down and Extract Data from Different Excel Sheet with Data Validation Option

In this method, we will use the Data Validation option from the Data tab. Here, we will use a new dataset. In our dataset, there are ID, Name, and Price of some products. We will use this dataset to create a full dataset in another sheet. In this case, we will extract the data from our Product List dataset.

STEP 1: Insert the Drop Down Menu

You need to insert the drop down menu first. Let’s pay attention to the steps below to create the drop down menu.

Firstly, create the structure of the dataset and then, select Cell B5.

Secondly, select the Data Validation option from the Data tab. It will open the Data Validation window.

Thirdly, select List from the Allow field and then, click on the Source field.

After that, you need to select options you want to add to the drop down menu.
To do so, select the Product List sheet and then, select Cell B5 to Click OK to proceed.

Now, you will see the Product ID in the drop down menu in Cell B5.

Follow the same procedure to bring the drop down menu in Cell C5. You have to select the Product Name in the Data Validation window from the Product List sheet.

To include the drop down menu in Cell D5, you will have to select the Price in the Data Validation window from the Product List sheet.

Finally, you will see drop down menus in the desired cells of Row 5.

STEP 2: Extract Data from Different Sheet

To extract data easily from a different sheet, you need to follow the instructions below.

Select the desired cells of Row 4 & 5.

Now, go to the Insert tab and select Table.

The Create Table window will appear. Check My table has headers if your table has headers. Otherwise, uncheck it. Click OK to proceed.

After clicking OK, you will see results like below.

Next, select Cell D5.

After selecting Cell D5, press the Tab key. It will automatically include the drop-down menu in the desired cells of Row 6. You can fill the desired cells in the row using these drop-down menus.

Follow the same process to insert the drop-down menu in every row and extract data using it.

Last of all, you can change the Number Format to Currency in Column D to represent the dataset like below.

4. Select from Drop Down and Extract Data from Different Sheet Using FILTER Function in Excel

In the last method, we will use the FILTER function to select from the drop-down and extract data from a different sheet. The FILTER function generally filters a range or array. Here, we will use a dataset that contains the ID, Name, Quantity, and Price of some products.

STEP 1: Change the Dataset into a Table

Let’s pay attention to the steps below to change our dataset into a table.

First of all, select any cell of your dataset. We have selected Cell B4 here.

Secondly, go to the Insert tab and select Table.

Check My table has headers in the Create Table dialog box and click OK to proceed.

After clicking OK, the dataset will turn into a table like below.

Now, go to the Table Design tab and change the Table Name. We have named it Product.

STEP 2: Create the Unique List

After creating the table, we need to find the unique values from the Product Name of the table. To do so, follow the steps below:

Go to a new sheet and select any cell. We have selected Cell I4 here.
Next, type the formula:

=UNIQUE(Product[Product Name])

Then, hit Enter to see the unique values in the Product Name column of the table.

Here, we have used the UNIQUE function. The UNIQUE function returns the unique values from a range or array. This formula returns the unique values from the Product Name column of the Product table.

STEP 3: Include the Drop Down Menu

To include the drop menu, go to the sheet where you have listed the unique values.
After that, create the headers of the Product table in Row 4.
Next, select Cell G5.

Now, select the Data Validation option from the Data tab. It will open the Data Validation window.

Select List in the Allow field and then, select the Source field.

Now, you need to select options you want to add to the drop down menu. In this case, these options are the name of the products. We have selected Cell I4 to I6. Click OK to proceed.

After that, you will see the drop down menu in Cell G5.

STEP 4: Insert Data from Different Sheet

We will pull data from a different sheet to the new sheet. Follow the instructions carefully.

Select Cell B5 and type the formula:

=FILTER(Product,Product[Product Name]=G5)

Here, we have used the FILTER function to filter a range. The first argument is called the Product table. The second argument denotes that the Product Name will have to be the same as Cell G5.

Hit Enter to see results like the below.

Finally, if you change the product name using the drop down menu, the dataset will automatically be updated.

Things to Remember

There are certain things we need to remember when we are trying to select from the drop-down menu and pull data from a different sheet in Excel.

In Method-1, use the double quotation sign carefully while typing the formula. Otherwise, the result will be incorrect. Also, be extra careful with the column index number in the VLOOKUP Function.
In Method-2, the formulas do not update automatically when you use the Fill Handle. To avoid this, edit the formula in each row. You can use Method-1 instead.
Method-3 is mainly used for creating a new dataset in a different sheet.
Method-4 is applicable in Excel 365 only. For older versions, use the above methods.

Download Practice Book

Download the practice book here.

Select from Drop Down and Pull Data.xlsx

Conclusion

We have demonstrated 4 easy ways to select from the drop-down and pull data from a different sheet in Excel. I hope these methods will help you to solve your problems. Furthermore, the practice book is also added at the beginning of the article. You can download it and exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!