WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

Vlookup with dependent drop down lists in Excel

February 13, 2023
29.7K Views

It is time for you to try to get familiar with creating dependent dropdown lists in excel using vlookup to apply them in your future works. If you frequently work using Excel, it may be convenient for you to know how to use the software's main tools. This way, you can do a better job and reduce the error range using the tools.

Among the things you should learn how to do are dependent dropdown lists in excel using vlookup online, so you should look up information on it. Luckily, you have come across two methods to help you create the dropdown lists and reset them if necessary.

How to create dependent dropdown lists in excel using vlookup function?

To create dependent dropdown lists in excel using vlookup 2016, you will have to follow these steps:

1.Open the Excel document where you want to create the dropdown list using the vlookup function. Now select the cell where your list will start, go to the data tab, and then click on data validation.

2.Now, in the displayed box of data validation, you will have to position the mouse cursor on configuration. Then click on the some value arrow and the list option.

3. Eventually, you will have to indicate where the elements that will appear in your dropdown list are located. In this example, the full range A1:B1 was used. Then click OK for the settings to be integrated.

4. Now, you must select the entire data set that, in this case, would apply from cell A1 to B6.

5. Go to the formulas tab and select create from selection, which would be contained in the defined names group. You could also use the shortcut Control + Shift + F3.

6. A new box will open where you have to check the top row option in the create names from the selection list. Then click OK to close the box.

7. Finally, you can select a new cell where you want to place another dropdown list, go to the Data tab and click on data validation. In the dedicated box, you will have to select list and place the formula =INDIRECT(D3), which the main dropdown menu will contain.

How to reset dependent dropdown lists in Excel with VBA code?

You could also learn how to reset dependent dropdown lists in excel using vlookup Mac by following this method:

1. Open the Excel document where the error appears in the dropdown list. The classic problem is that you put an option on the list, and the wrong answers come up. If you select fruits, they should all appear in this category and not in the list of vegetables or products.

image.png

You can use the VBA code if you are looking to reset the dependent dropdown lists in excel using vlookup, which is:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Target.Column = 4 Then

If Target.Validation.Type = 3 Then

Application.EnableEvents = False

Target.Offset(0, 1).ClearContents

End If

End If

exitHandler:

Application.EnableEvents = True

Exit Sub

End Sub

2. Copy the code using the shortcut control + c on your keyboard and then go to the Developer tab in Excel. Then you will have to select the visual basic option in the code group.

3. Within the VB project box, you must click on the dropdown list in the Microsoft Excel objects folder.

4. You must paste the previously copied code into the code lead at one end.

5. Finally, close the box and see if the changes were what you wanted.

image.png

How to reset a dropdown list using vlookup in Excel in a simple way?

If you are not a fan of using codes, you could try resetting the dependent dropdown lists in excel using vlookup 2019 more simply. For this, you will have to:

1. Select the dropdown list, go to the home tab, and open the conditional formatting box, where you have to click on new rule.

2. Within the box, you must select use a formula to determine which cells to format.

3. In the formula box you will have to place the following function: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0) )

4. You will have to establish that format and click on accept.

To have dependent dropdown lists in excel using vlookup, you must follow a series of steps as explained previously. You must pay attention to these procedures and try to put them into practice from home. It is good that you look for more information about the subject if you have any doubts about it or how to use it.

Dependent dropdown lists in excel using vlookup are a priority in your work with Excel, so you should not ignore these tools. If the content has been useful to you, do not forget to look for more similar information so that you can master excel. You should also do your best to download WPS Office for the sole purpose of using Excel, PowerPoint, and Word on your pc at no cost.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.