Creating a drop-down list is not just a tool to enter accurate data entry but also to extract information based on the selected parameter by the end user. Like when you choose a country in a form, the Excel worksheet automatically extract states in a given country which is an example of a dependent list.
We can create multiple dependent drop-down lists, but often the purpose of each drop-down list is to be placed as an argument of a query and trigger an action.
A dynamic drop-down list means that a secondary drop-down list (Items) is dependent upon the primary drop-down list (Item Category). In the above example, we have three product category (Item_Category), and each product category have some Items with respective prices in a separate tables.
Our task is to create multiple Drop-Down list in which you select Item Category (Vegetables_Item) then product of that category (Carrot) and finally the respective price of each product.
Let’s dive in;
In Excel Workbook you will create a dependent drop-down list of Items and a dynamic one using Excel Table and Named Range tools. Moreover, returns the price of each item using the INDEX and MATCH function based on the selected drop-down argument.
Excel INDEX and MATCH function is an robust and excellent alternative to the Excel VLOOKUP function.
While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.
Insert Table and Named Range to a list
To add a drop-down list that updates automatically (Dynamic), we will first make a table and name each item list, by performing the following steps:
- Go to worksheet ‘Query’ of Excel working file (Image instructions below)
- To create the first table, select a cell range F4:F16 (Fruits_Item)
- Press Ctrl+T or go to Insert>Table
- Check “My table has headers” and press OK
- Now, click any cell of a new table and select Table Design tab on the top
- Go to Table Name Box and write “Fruits_Item” as a table name, press Enter (create table for Vegetables_Item and Cereal_Item)
- Select a cell range F5:F16 (to create Named Range)
- Now go to Excel ribbon- Formulas>Define Name
- Enter New Name as “Fruits_Items” to name our list
- Press Ok (create Named Range for Vegetables_Items and Cereal_Items)
Remember the above steps is only to create an easy dynamic reference using the Excel drop-down list. Moreover, you create a dependent drop-down lists i.e. Item category (Primary) and Items (Secondary).
- While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.
- Try to replicate the query with a separate Workbook. The Excel solution file is just for reference.
Create your Drop-Down list
We will create a simple drop-down list of Item category and a Products list. Read ‘How to create a drop-down list in Excel’ where you will find detail understanding of drop-down list.
You will use INDIRECT function for the reference of Product items. Read a detail analysis referencing List in a Drop-Down using INDIRECT function.
You will use the drop-down list based on a dynamic list just as a reference point for getting the price of each Item.
Once you insert two drop-down lists (primary and secondary), we will move to the next part of task is to extract the price of each product.
- The Table naming of ‘Item Category’ must be similar to the drop-down list name in Column B.
Keep Reading Similar Blog:
Learn to Manage Data with Excel Table
Converting a list to an Excel table make data sets accessible to style, manage (add or delete row and column) and perform multiple data summary functions.
Create Auto Update and Conditional Drop-Down list
Learn how to insert auto-update, dynamic, and conditional with multiple selections Drop-Down list in Excels. Also adding and removing the pull-down list.
How to Name a Cell or Range in Excel with Formula
Named Range- create, edit, delete and more about naming a range of cells and apply Name to new formula or existing formula quickly in Excel (Complete Guide).
Returns data using Excel INDEX and MATCH function
Now, we will use the INDEX and MATCH function of Excel to get price data of the respective product item. No doubt final formula is complicated, but that’s what I intended, to make sure you understand various aspect INDEX and MATCH formula as well.
Note: In the real world, you would find various kind of data presentation and similarly many ways to write Excel formula.
We have combined two formula to extract data of each item; please refer cell A5 and cell D5. Product and its respective price are placed in the same format but a different table. Both Formula works together to return value of individual Item. I could have merged both formulae but did not, to show the power of Excel IFS function as well.
First Formula
Objective:
Translate the chosen product category of column B into a numeric literal in column A.
Arguments
=IFERROR(IFS($F$4=B5,1,B5=$G$4,2,B5=$H$4,3),””) – Cell A5
Excel IFERROR Function – Substitutes error with a different text string.
Excel IFS function– Checks multiple condition
IFS (test1, value1, [test2, value2], …)
Interpretation
In this formula, the Excel IFS function checks four logics and return the corresponding value of correct logic.
In cell B5 I have selected Vegetables_Item which represent numeric literate of 2 (2nd – column G). Similarly 1 and 3 for Fruits_Item and Cereal_Item.
Then IFERROR function returns blank cells in case of error.
We can also use a simpler function:
=IFERROR(MATCH(B5,F4:H4,0),””)
Purpose
The return value will identify the reference array of product and price from where respective data needs to get extracted.
- If the presentation of Item (F4:H4) and Price (J4:L4) is not in the same sequence, then you will have to create one more formula using IFS function for showcasing numeric literate of price in other formats.
- Note this formula is part of the second formula which is a complicated one 😳
Second Formula
Objective:
Identify Product and Price Array based on the selected Items using drop-down list.
Arguments
=IFERROR(INDEX((Fruits_Prices,Vegetables_Prices,Cereal_Prices), MATCH(C5,INDEX((Fruits_Items,Vegetables_Items,Cereal_Items),0,0,A5),0),,A5),"Select Item")
- Excel INDEX function – Get data from a respective cell position
INDEX(reference, row_num, [column_num], [area_num])
- Excel MATCH function – Get cell position of a data
MATCH(lookup_value, lookup_array, [match_type])
- Excel IFERROR function – Return error statement “Select Item”
IFERROR(value, value_if_error)
The formula contains the name of lists (product and price both). Moreover, each named range represents a cell range.
Interpretation
In this formula, Excel INDEX and MATCH function extract data from a list or array. The formula is in two stages:
First part of formula:
=MATCH(C5,INDEX((Fruits_Item,Vegetables_Item,Cereal_Item),0,0,A5),0)
Formula in Cell A5 returns 2 which indicate 2nd list i.e. Vegetables_Item as explained in First Formula. INDEX function identifies which list among three of them (reference) to investigate by accepting [area num] argument, reference of cell A5.
Moreover, the MATCH formula looks at cell C5 value “Carrot” in the Vegetables_Item list (2nd list- G9) and returns “Carrot” cell position as ‘5’ in the list.
Second part of the Formula:
=IFERROR(INDEX((Fruits_Prices,Vegetables_Prices,Cereal_Prices),5,,A5),”Select Item”)
In above formula, INDEX excel function returns 5th position (row_num) of a price list i.e. Vegetables_Price list ([area_num] – A5). No [column_num] change is required.
Also, in case there is a conflict in the selection, IFERROR Excel function returns “Select Item”.
Purpose
The function returns price of an Item as per the given position of a list.
- ‘5’ is a return value of first part of formula.
- F9 key to quickly break a formula down into pieces
Conclusion
A Drop-Down list provides us an option to select various items or scenarios based on which the Excel trigger a query and returns the corresponding value. INDEX and MATCH function working together is a fantastic tool in the EXCEL’s arsenal for locating data and returning its value.
INDEX and MATCH function is an excellent alternative to VLOOKUP function as well.