Create multiple dependent drop-down lists (data validation) in Excel with the Index and Match function, a better alternative to the VLOOKUP function that returns dynamic drop-down lists with several values based on given criteria.
Creating a drop-down list (Check the video below) 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 extracts 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 table.
Our task is to create multiple Drop-Down lists in which you select Item Category (Vegetables_Item) then the 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 functions are a 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 the Table Design tab on the top
- Go to Table Name Box and write “Fruits_Item” as a table name, press Enter (create a 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 are only to create an easy dynamic reference using the Excel drop-down list. Moreover, you create 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 the Item categories and a Products list. Read ‘How to create a drop-down list in Excel’ where you will find a detailed understanding of the drop-down list (data validation).
You will use the INDIRECT function for the reference of Product items. Read a detailed analysis referencing List in a Drop-Down using the 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 the 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:
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).
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.
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.
Returns data using Excel INDEX and MATCH function
Now, we will use the INDEX and MATCH functions of Excel to get price data of the respective product item. No doubt the final formula is complicated, but that’s what I intended, to make sure you understand various aspects INDEX and MATCH formula as well.
Note: In the real world, you would find various kinds of data presentations and similarly many ways to write Excel formulas.
We have combined two formulas to extract data for each item; please refer to cell A5 and cell D5. Product and its respective price are placed in the same format but in 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 returns 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 the IFS function for highlighting 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:
The first part of the formula:
=MATCH(C5,INDEX((Fruits_Item,Vegetables_Item,Cereal_Item),0,0,A5),0)
The formula in Cell A5 returns 2 which indicates the 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 the “Carrot” cell position as ‘5’ in the list.
The 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, the IFERROR Excel function returns “Select Item”.
Purpose
The function returns the price of an Item as per the given position of a list.
- ‘5’ is a return value of the first part of the formula.
- F9 key to quickly break a formula down into pieces
Take Away
A Drop-Down list provides us an option to select distinct items or scenarios based on which the Excel triggers 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 the VLOOKUP function as well.
