# Updating Cell Values Based on Selection in Drop Down List in Google Spreadsheet

We use drop down lists in Google Sheets very often. But sometimes we might find it problematic for updating cell values based on selection in drop down list in Google Spreadsheet. In this article, we’ll show 5 quick and simple methods using some functions and formulas. I hope, it’ll help you solve your problems.

## A Sample of Practice Spreadsheet

You can download Google Sheets from here and practice very quickly.

## 5 Quick Ways of Updating Cell Values Based on Selection in Drop Down List in Google Spreadsheet

Firstly, let’s get introduced to our dataset. Here we have some products in Column B and their companies in Column C. We’ll make a drop down list of the products and learn how to update the cell values of another drop down list based on the selection in the products’ drop down list in Google Sheets. That’s why we rename this dataset as Datasheet. We’ll use values from this sheet in our methods.

### 1. Combining UNIQUE, FILTER, and SORT Functions

At first, we’ll do the work by applying the UNIQUE, FILTER, and SORT functions. This method is straightforward. We’ll get the output quickly.

Steps:

• Type the following formula in Cell B5
`=SORT(UNIQUE(Datasheet!B5:B13))`
• Hit the Enter to get the unique products list.

Formula Breakdown

• UNIQUE(Datasheet!B5:B13)

This formula searches the unique values and removes the duplicate values from Column B in Datasheet and puts them in the current sheet.

• SORT(UNIQUE(Datasheet!B5:B13))

Finally, it will sort the unique values alphabetically.

• Now we’ll make a drop down list from the list of unique products.
• Select Cell B10. Go to Data > Data validation.

• Select List from a range under the Data validation menu and give the range from B5 to B7 which is our unique product list.
• Then click the Save Button.

• We’ll get a list like this.

• Type the following formula in Cell C5
`=SORT(FILTER(Datasheet!C5:C13,Datasheet!B5:B13=B10))`
• Press Enter to get the relevant company name.

Formula Breakdown

• FILTER(Datasheet!C5:C13,Datasheet!B5:B13=B10)

This formula searches for the companies’ names in Column C from the Datasheet corresponding with the selected product value in Column B from the Datasheet and brings the values here.

• SORT(FILTER(Datasheet!C5:C13,Datasheet!B5:B13=B10))

Finally, it will sort the values alphabetically.

• If you change the product name from the list then the company’s name will automatically get updated.

• Now create a drop down list in Cell C10 with the company’s name by the same procedure.

• When we select the product name from the list the adjacent cell shows the company list.
• The Invalid part is shown because we select smartphones in the product list but have a value of other product company names.
• Below you can see the actual list which you can select easily.

### 2. Merging IF and QUERY Functions

Now we have a different situation. We want to have the product names in separate columns. Moreover, we want to have the company names under each product in separate columns. And also we want to update it based on the selection in drop down list. In this case, we can use the IF and QUERY functions.

Steps:

`=IF(\$B\$10="Keyboard",QUERY(Datasheet!\$B\$5:\$C\$13,"SELECT C WHERE B='Keyboard'"),"")`
• Press Enter to get the output.

• Now type the same formula in Cell C5 by changing the formula a little bit-
`=IF(\$B\$10="Laptop",QUERY(Datasheet!\$B\$5:\$C\$13,"SELECT C WHERE B='Laptop'"),"")`
• Hit Enter to get the result.

Formula Breakdown

• QUERY(Datasheet!\$B\$5:\$C\$13,”SELECT C WHERE B=’Keyboard'”)

This function searches the given value which is ‘Keyboard’ from the Datasheet and from there brings the corresponding value from Column C.

• IF(\$B\$10=”Keyboard”,QUERY(Datasheet!\$B\$5:\$C\$13,”SELECT C WHERE B=’Keyboard'”),””)

Finally, it will place the value if it matches with the given logic in this case ‘Keyboard’.

• IF(\$B\$10=”Laptop”,QUERY(Datasheet!\$B\$5:\$C\$13,”SELECT C WHERE B=’Laptop'”),””)

This formula also works like the previous formula. But here we have just changed the product name which is ‘Laptop’.

Read More: How To Lock Rows In Google Sheets (2 Easy Ways)

### 3. Joining INDEX and MATCH Functions

Now we have a dataset with 3 columns which are products in Column B, companies in Column C, and price in Column D. And we have a drop down list below in Cell B16 with the columns header. This time we can solve the problem by using the INDEX and MATCH functions. Firstly, we have to make a Data Prep column in Column E for this purpose.

Steps:

`=INDEX(B5:D13,,MATCH(B16,B4:D4,0))`
• Press Enter to get the result.

Formula Breakdown

• MATCH(B16,B4:D4,0)

This function searches for the selected product in the drop down list which is in Cell B16. Then it searches for its relative position in Columns B, C, and D.

• INDEX(B5:D13,,MATCH(B16,B4:D4,0))

Finally, it gives the matching values within the data range from Cell B5 to D13.

• . Now, you can select any option from the list and get the updated values as shown in the image.

### 4. Combining VLOOKUP and COLUMN Functions

Again we have a different scenario. Now we have the product’s name in Column A and the company’s name through Column B to Column D. Moreover, we have a drop down list in Cell A10 based on the product name. We want the updated values whenever we select a product from the list. So we can use the VLOOKUP and COLUMN functions together for updating cell values based on the selection in drop down list in Google Spreadsheet.

Steps:

`=VLOOKUP(\$A\$10,\$A\$4:\$D\$7,COLUMN(),0)`
• Hit Enter to get the updated value.

Formula Breakdown

• COLUMN()

It gives the column position of the desired value.

• VLOOKUP(\$A\$10,\$A\$4:\$D\$7,COLUMN(),0)

Then it gives the search result based on Cell A10 and searches it from the range Cell A4 to D7.

• Then apply the Fill Handle tool to apply the formula in all rows.

• Finally, you’ll get the updated value in all rows.

• Changing the product list from the drop down list will automatically update the corresponding cells.

Note: You must start the dataset from the first column, Column A. otherwise the formula won’t work.

### 5. Merging TRANSPOSE, INDIRECT & MATCH Functions

Now we have a different situation from Method 4. We want to have the updated values in a single column based on the drop down list in Cell B6. This time we can do this by using the TRANSPOSE, INDIRECT, and MATCH functions together.

Steps:

`=TRANSPOSE(INDIRECT("C" & MATCH(B6,\$B\$1:\$B\$3,0) & ":E" & MATCH(B6,\$B\$1:\$B\$3,0)))`
• Press Enter to get the output.

Formula Breakdown

• MATCH(B6,\$B\$1:\$B\$3,0)

This function searches for the selected product in the drop down list which is in Cell B6. Then it searches for its relative position through Cells B1 to B3.

• INDIRECT(“C” & MATCH(B6,\$B\$1:\$B\$3,0) & “:E” & MATCH(B6,\$B\$1:\$B\$3,0))

Then it gives the related value within Column C through E.

• TRANSPOSE(INDIRECT(“C” & MATCH(B6,\$B\$1:\$B\$3,0) & “:E” & MATCH(B6,\$B\$1:\$B\$3,0)))

As the values are in a row so this formula now transposes them into a column.

• If you change the product list from the drop down list, you’ll get the updated values quickly.

Note: You have to start your dataset from the first row, Row 1, otherwise the formula won’t work properly.