# How to Change Drop Down List Based on Cell Value in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

In order to extract specific data based on specific values, we may need to use the drop-down list. Moreover, we need to co-relate the two or more dependent drop-down lists. In this article, we will show you how in Excel to change a drop-down list based on cell value.

## 2 Suitable Ways to Change Drop Down List Based on Cell Value in Excel

In the sections below, we will emphasize the 2 most suitable ways to change the drop-down lists. Firstly, we will apply the OFFSET and MATCH functions in the drop-down lists to make changes based on cell values. Additionally, we will use the XLOOKUP function featured in Microsoft Excel 365 to do the same. In the below image, we have provided a sample data set to accomplish the task. ### 1. Combine the OFFSET and MATCH Functions to Change Drop Down List Based on Cell Value in Excel

In our following data set, we have three different salesmen with their sold products. Now, we want to find the products for a particular salesman. To do so, follow the steps below.

Step 1: Create a Data Validation List

• Go to the Data.
• Click on the Data Validation. Step 2: Select the source for the List

• From the Allow option, select the List. • In the source box, select the source range E4:G4 for the names of the salesmen.
• Press Enter. • Therefore, a drop-down will appear in cell B5. Step 3: Apply the OFFSET function

• Type the following formula for the OFFSET function,
`=OFFSET(\$E\$4)`
• Here, E4 is the reference cell in absolute form. • In the rows argument, put 1 as the value that will count 1 row down from the reference cell E4.
`=OFFSET(\$E\$4,1` Step 4: Use the MATCH function to define the OFFSET function column

• In the cols argument, to select the columns use the MATCH function with the following formula.
`=OFFSET(\$E\$4,1,MATCH(\$B\$5`
• Here, B5 is the cell value selected in the drop-down list. • To select the lookup_array argument for the MATCH function, add E4:G4 as the range in absolute form with the following formula.
`=OFFSET(\$E\$4,1,MATCH(\$B\$5,\$E\$4:\$G\$4` • Type 0 for the Exact match type. The following formula will return 3 for the MATCH
`MATCH(\$B\$5,\$E\$4:\$G\$4,0)` • Write minus 1 (-1) from the MATCH function, because the OFFSET function counts the first column as zero (0).
`MATCH(\$B\$5,\$E\$4:\$G\$4,0)-1` Step 5: Enter the height of the columns

• For selecting 1 in the height argument, it will count that each column has one value.
`=OFFSET(\$E\$4,1,MATCH(\$B\$5,\$E\$4:\$G\$4,0)-1,1` Step 6: Enter the width Value

• For the width argument, type 1.
`=OFFSET(\$E\$4,1,MATCH(\$B\$5,\$E\$4:\$G\$4,0)-1,1,1)` • Therefore, you will see that when we select Jacob in B5, it will result in Chocolate as the first element for Jacob. Step 7: Count the elements of each column

• To count the number of elements in a column, we will apply the COUNTA function in cell C13 with the following formula.
`=COUNTA(OFFSET(\$E\$4,1,MATCH(\$B\$5,\$E\$4:\$G\$4,0)-1,10))` • This will count the element/product number for a particular salesman (Jacob). Step 8: Enter the count height cell value as the height argument in the OFFSET function

• Write the following formula to add the height.
`=OFFSET(\$E\$4,1,MATCH(\$B\$5,\$E\$4:\$G\$4,0)-1,C13,1)` Step 9: Copy the Formula

• Press  Ctrl  +  to copy the formula.
`=OFFSET(\$E\$4,1,MATCH(\$B\$5,\$E\$4:\$G\$4,0)-1,C13,1)` Step 10: Paste the formula

• Paste the formula in the Data Validation source.
`=OFFSET(\$E\$4,1,MATCH(\$B\$5,\$E\$4:\$G\$4,0)-1,C13,1)` • Finally, press Enter to see the change. • As a result, your drop-down list values will change based on another cell value. • Make change the cell value Bryan to Juliana and get the product’s name sold by Juliana. ### 2. Use the XLOOKUP Function to Change Drop Down List Based On Cell Value in Excel

If you are blessed with Microsoft 365, you can make it do with just one formula of the XLOOKUP function. Follow the outlined steps below to do so.

Step 1: Make a Data Validation List

• From the Data Validation option, select the List. Step 2: Type the source range

• Select the source range E4:G4 in the source box.
• Then, press Enter. • Therefore, a Data Validation list will appear. Step 3: Insert the XLOOKUP function

• Select the B5 cell as the look_up.
`=XLOOKUP(B5)` Step 4: Select the lookup_array

• Write the range E4:G4 as the look_array.
`=XLOOKUP(B5, E4:G4)` Step 5: Insert the return_array

• Type the range for the return value E5:G11. • Therefore, the products will return according to a particular salesman. • Now, select any name from the drop-down list and get the products’ names. Notes.  See carefully, that in the above image zero is shown as in the range the cells were blank. That’s why these are considered zero. To remove the zeros follow the steps below.

Step 6: Apply the UNIQUE function

• Type the following formula nested with the UNIQUE.
`=UNIQUE(XLOOKUP(B5,E4:G4,E5:G11),,TRUE)` • Finally, you will get the result you desired. ## Conclusion

Finally, I hope you now understand how to update the drop-down list in Excel based on cell value. All of these strategies should be carried out when your data is being educated and practiced. Examine the practice book and apply what you’ve learned. We are driven to continue offering programs like this because of your generous support.

The Exceldemy staff will get back to you as soon as possible.

Stay with us and continue to learn.

## Related Articles Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

1. Reply Hello,

That was an amazing guide, more complete and useful than any other instruction that I found on the Internet. Thanks a lot.

Just one question:

For method 2 (Using the Xlookup function) is it possible also to get the final results (list of products) in a row next to each other, other than having them in the same column under each other?

• Reply Greetings Sepehr,

# The answer to your question is “Yes“—it is possible to have the list of products in different columns.

# Just use the TRANSPOSE function preceding the UNIQUE+XLOOKUP formula. Of course, you have to have Excel 365 to use the XLOOKUP function.

`=TRANSPOSE(UNIQUE(XLOOKUP(B5,B7:D7,B8:D14),,TRUE))`  Advanced Excel Exercises with Solutions PDF  