Bhubon Costa
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 + C  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.
Read More: How to Create List from Range in Excel (3 Methods)
Similar Readings
- How to Create Dependent Drop Down List with Multiple Words in Excel
- Creating a Drop Down Filter to Extract Data Based on Selection in Excel
- How to Extract Data Based on a Drop Down List Selection in Excel
- Create Excel Filter Using Drop-Down List Based on Cell Value
- How to Add Item to Drop-Down List in Excel (5 Methods)
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)
Read More: How to Edit Drop-Down List in Excel (4 Basic Approaches)
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.
Read More: How to Add Blank Option to Drop Down List in Excel (2 Methods)
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.
Read More: Unique Values in a Drop Down List with VBA in Excel (A Complete Guide)
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.
If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.
The Exceldemy staff will get back to you as soon as possible.
Stay with us and continue to learn.
Related Articles
- How to Copy Filter Drop-Down List in Excel (5 Ways)
- VBA to Select Value from Drop Down List in Excel (2 Methods)
- How to Create a Drop Down List with Unique Values in Excel (4 Methods)
- Excel Drop Down List Not Working (8 Issues and Solutions)
- Excel Drop Down List Depending on Selection
- How to Make a Drop Down List in Excel (Independent and Dependent)
- Auto Update Drop Down List in Excel (3 Ways)
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?
Greetings Sepehr,
Thank you for your appreciation.
# 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))