Auto-Populate Information Based on Drop-down Selection in Google Sheets

Another exciting Google Sheets tutorial awaits! Get ready to learn how to automatically fill in information based on a drop-down selection.

Spreadsheet applications have always fascinated me. They are like vast oceans containing hidden gems of data manipulation possibilities. Google Sheets is no exception.

To truly harness the power of Google Sheets, you need to dive deep into its functionalities. In this tutorial, we’ll explore three essential Google Sheets functions and a menu command to achieve our goal. These are the IF logical function, the UNIQUE function, and the QUERY function. Don’t worry if you’re not familiar with the menu command called Data validation. I’ll guide you through it step by step.

But first, let me explain what I mean by “auto-populate information based on drop-down selection.”

Imagine having a drop-down menu with a list of products. When you choose a product from the menu, the price and other relevant information about that product automatically fill in other cells. This convenient feature saves you time and effort.

Now, let’s dive into an example to help you grasp the concept better.

Example to Auto-Populate Information Based on Drop-down Menu in Google Sheets

Take a look at the first two screenshots below:

The first screenshot (Figure 1) displays the sample data, and the second screenshot (Figure 2) shows the output.

You can use similar data to populate information according to your needs. I chose this sample because it’s one I created earlier to address a reader’s query.

In this example, you can choose any team from the drop-down list, such as “Team 1,” “Team 2,” “Team 3,” or “Free Agent.” When you select a team, the corresponding player names will appear in the respective column.

Now that you understand the concept, let’s move on to the tutorial on how to auto-populate information based on a drop-down selection.

But before we begin, let me clarify a few more things.

If you only need to display a single value corresponding to your selection, you can use functions like VLOOKUP, HLOOKUP, or XLOOKUP. However, if you want to perform calculations based on a drop-down selection, the best way is to use the SUMIF function in Google Sheets.

Now, let’s get back to the tutorial.

Steps to Auto-Populate Information Based on Drop-down Selection

1. Open a new Google Sheets file.

2. Create three tabs with the following names: Master Sheet, Team Members, and Team Name.

3. In the Team Members sheet, enter the information shown in Figure 1 above (you can also use my sample sheet shared at the end of this post). Feel free to replace the names with real ones.

4. Once you’ve finished entering the data, name the ranges to make the formulas more readable. If you’re unsure how to name ranges in Google Sheets, refer to the documentation.

   For our example, let me walk you through the steps to name the ranges:

   • Go to Data > Named Ranges and set the rules as shown below:

   

5. Now, go to the tab named Team Name and apply the following formula to the very first cell:

   =UNIQUE('TEAM MEMBERS'!B2:B30)

   The result will look like the following, which you can use for your drop-down selection:

   

6. Great! The drop-down list is now ready. Let’s move on to the final steps.

   • Go to the sheet named Master Sheet.
   • In cells B1, C1, D1, and E1, enter the column headings as shown in the image above.
   • Now, in cell A2, we will create the drop-down list. To do that, go to Data > Data validation and set the data validation rules as shown below:

   

   Your drop-down list is now ready for action.

7. Finally, when you select a team from the drop-down list, you need to populate the corresponding column with the players’ names. Insert the following query formulas in cells B2, C2, D2, and E2:

   • First Formula:

     =IF(A2="TEAM 1",QUERY(TEAMPLAYERS,"SELECT A WHERE B='TEAM 1'"),"")

   • Second Formula:

     =IF(A2="TEAM 2",QUERY(TEAMPLAYERS,"SELECT A WHERE B='TEAM 2'"),"")

   • Third Formula:

     =IF(A2="TEAM 3",QUERY(TEAMPLAYERS,"SELECT A WHERE B='TEAM 3'"),"")

   • Fourth Formula:

     =IF(A2="FREE AGENT",QUERY(TEAMPLAYERS,"SELECT A WHERE B='FREE AGENT'"),"")

   These formulas will automatically populate the corresponding columns with the names of the players based on the selected team.

If you’ve followed all the steps correctly, you should now see the desired result. However, if anything isn’t working as expected, feel free to ask me any questions in the comments.

And there you have it! You’ve successfully learned how to auto-populate information based on a drop-down selection in Google Sheets.

If you’d like to explore more Google Sheets tutorials and become a Sheets wiz, head over to Crawlan.com for more exciting content.

Happy Sheets-ing!

Example Sheet: 20719