The row_num argument is ignored since there is only one row. The array doesn’t need to be fixed with the $ symbol since you want the array to move down when the formula is dragged down to the last row. Instead, the array should reference to the specific row. Since you want the list values in separate cells, the array to be used should not be that of the entire table. In Cell I5, you want to display the contents of the first row, depending on the selected Div group in cell I5. The INDEX-MATCH approach can be used to display the list. Using the same technique, there is also an option to display the list in separate cells.Ĭreate the first dropdown list using the same method above or copy cell G4 to cell I4. Go to Data Validation, select List, and in Source, paste into the Data Validation list.Ĭlick OK and you will see that the second dropdown list changes according to the chosen option for the first dropdown. Go to the cell where you want the second dropdown list, cell G5. Once you get the results needed, highlight the entire formula, right click and select Copy (or press CTRL C). Press ESC or CTRL Z to leave this view and revert back to the normal formula. To check if it returns the correct list, go to the formula bar and hit the F9 key.
When you hit ENTER, the value will not show the full list result of the formula, but instead only display the contents of one of the cells. (Here is a link to other INDEX MATCH guides.) Since this will be pasted to Data Validation, make sure to fix the cell references by adding $ or pressing the F4 key. match_type: There are three options, “1- Less than”, “0 – Exact match”, and “-1 – Greater than”.lookup_array: This is where to look for the lookup_value.lookup_value: The value you want to look for.This will return a number to dictate how many columns to move to the right to find the answer. Since we want this to be dynamic according to the selection in the first dropdown list, the MATCH() function will be used. column_num: Indicates what column number the answer list is found in.
Since we want to include all the rows, leave this blank. row_num: This dictates how many rows to move down the array in order to find the answer.array: The area where the answer could be.
One option is to write the formula in a random cell in the spreadsheet and then copy and paste that as the Source in the Data Validation window. However, writing the formula directly on Source box of the Data Validation window will not give you the Excel help to assist you with the syntax. Instead, a combination of Excel’s INDEX() and MATCH() function will be used. However, unlike the first dropdown list, a direct cell reference can’t be used for the second list since the source will be different depending on the selected option in the first list. The worksheet name and the cell range will be added to the Source field in the Data Validation dialog box.Ĭlick the down arrow on the right side of the Source box to accept the input and expand the dialog box.In the same way, go to Data Validation under the Data tab and select List. Select the cells that contain your options. Click the tab for the worksheet containing the drop-down list options. You can access your workbook underneath the dialog box. The Data Validation dialog will shrink down to just the Source box. To add the options to your drop-down list from a separate worksheet, click the up arrow on the right side of the Source box. You can also hide the worksheet containing the options (right-click on the worksheet tab and select Hide). You're going to use this list to populate the drop-down in each of the selected cells. The second method is faster because you've already created a list of items on a separate worksheet. This can be time consuming if you have a long list of items. The first method involves manually typing the options in the Source box separated by commas. You need to specify the source for the options in each drop-down list.