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

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.

Sample Data

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.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

Step 2: Select the source for the List

  • From the Allow option, select the List.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • In the source box, select the source range E4:G4 for the names of the salesmen.
  • Press Enter.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • Therefore, a drop-down will appear in cell B5.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

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.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • In the rows argument, put 1 as the value that will count 1 row down from the reference cell E4.
=OFFSET($E$4,1

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

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.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • 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

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • Type 0 for the Exact match type. The following formula will return 3 for the MATCH
MATCH($B$5,$E$4:$G$4,0)

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • 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

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

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

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

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)

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • Therefore, you will see that when we select Jacob in B5, it will result in Chocolate as the first element for Jacob.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

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

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • This will count the element/product number for a particular salesman (Jacob).

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

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)

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

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)

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

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)

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • Finally, press Enter to see the change.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • As a result, your drop-down list values will change based on another cell value.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

  • Make change the cell value Bryan to Juliana and get the product’s name sold by Juliana.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

Read More: How to Create List from Range in Excel (3 Methods)


Similar Readings


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.

Suitable Ways to Change Drop Down List Based On Cell Value in Excel

Step 2: Type the source range

  • Select the source range E4:G4 in the source box.
  • Then, press Enter.

Sample Data

  • Therefore, a Data Validation list will appear.

Sample Data

Step 3: Insert the XLOOKUP function

  • Select the B5 cell as the look_up.
=XLOOKUP(B5)

Sample Data

Step 4: Select the lookup_array

  • Write the range E4:G4 as the look_array.
=XLOOKUP(B5, E4:G4)

Sample Data

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.

Sample Data

  • Therefore, the products will return according to a particular salesman.

Sample Data

  • Now, select any name from the drop-down list and get the products’ names.

Sample Data

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)

Sample Data

  • Finally, you will get the result you desired.

Sample Data

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

Bhubon Costa

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.

2 Comments
  1. 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))

      Product List in Different Column

Leave a reply

ExcelDemy
Logo