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.


Download Practice Book

Download the practice book here.


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

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.

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

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

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

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

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


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.

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

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

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

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

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

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

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

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

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

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

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

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.

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

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.

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

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

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

  • We can also see the updated result for March.

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

Read More: VLOOKUP with Drop Down List in Excel


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.

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

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

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

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

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

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

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

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

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

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")

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

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.

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

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

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

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

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

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

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

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

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

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

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

Read More: How to Make a Drop-Down List Based on Formula in Excel (4 Ways)


Similar Readings:


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.

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

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.

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

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

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

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

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

  • 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.

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

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

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

  • 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.

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

  • 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.

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

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

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

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.

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

  • Now, go to the Insert tab and select Table.

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

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

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

  • After clicking OK, you will see results like below.

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

  • Next, select Cell D5.

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

  • 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.

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

  • 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.

Read More: How to Extract Data Based on a Drop Down List Selection in Excel


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 below.

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

Read More: Create Excel Filter Using Drop-Down List Based on Cell Value


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.

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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo