Find data using Excel INDEX & MATCH function (Dependent List)

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.

Drop Down list of products and return their respective price (INDEX and MATCH functions)
Drop Down list of products and return their respective price (INDEX and MATCH functions)

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

Download Your Example Excel File

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

Create Table of all three Products in store Excel
Create Table for all three Products in store
Now you have three Excel table names for each product category
Now you have three Excel table names for each product category
Create Named Range for each product category for referencing as formula in Drop-Down
Create Named Range for each product category for referencing as formula in Drop-Down
Three different Named Range for each product category for reference to Drop-Down formula
Three different Named Range for each product category for reference to Drop-Down source
  • 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.

Create drop-down list of each Item category Excel
Create drop-down list of each Item category
Use the Excel INDIRECT function to refer Items of a table or list
Use the Excel INDIRECT function to refer Items of a table or list
Similarly create Excel Table and Named Range of pricing for each product category
Similarly create Excel Table and Named Range of pricing for each product category
List of all Excel Table and Named Range (Use Name Manager)
List of all Excel Table and Named Range (Use Name Manager)

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 Excel Drop-Down list

An Excel drop-down list gives a user the ability to select only one option from given predefined possibilities. A drop-down list can simplify the process for an end user and make sure the data is accurate simultaneously.

How Excel Names Range Work

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.

Download Your Example Excel File

First Formula

 Objective:

Translate the chosen product category of column B into a numeric literal in column A.

Arguments

=IFS($F$4=B5,1,B5=$G$4,2,B5=$H$4,3,””,””) – Cell A5
Excel IFS function– Checks multiple condition
IFS (test1, value1, [test2, value2], …)

Interpretation

In this formula, Excel IFS function check four logics and return the corresponding value of correct logic.

Excel IFS function check multiple conditions returns TRUE value
Excel IFS function returns the column number of "Vegetables_Item" to refer "Vegetables_Price" column

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.

Moreover, fourth logic, If none of the options from drop-down list is selected in the adjacent cell by the end user, the formula returns an “” (blank), this hide formula.

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 and MATCH function together returns the value of specific position
Excel INDEX and MATCH function together returns the value of a specific position

INDEX(reference, row_num, [column_num], [area_num])

MATCH(lookup_value, lookup_array, [match_type])

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.

INDEX and MATCH function find the cell position and cell value
Returns the cell position of "Carrot" in Vegetable Item list, accordingly the Excel will fetch price from the price table

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.  

INDEX function returns the value of a given cell position Excel
INDEX function returns the value of a given cell position
  • ‘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.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments