Using INDEX and MATCH to Retrieve Data and Chart with Multiple Criteria

Excel

Use multiple criteria to retrieve data from tables that do not have a unique identifier or index for each row . This example uses two drop-down lists to select and then chart Product and Region for the months Jan through Dec.

Concatenate (join) two columns together to create a unique key you can use to retrieve and chart data.

Concatenate (join) two columns together to create a unique key you can use to retrieve and chart data.

Concatenate (join) two columns together to create a unique key you can use to retrieve and chart data.

In the table C26:Q41 the Product column from D25 downward and Region from E25 downward are not unique. However, by combining these two columns you can create a unique column of data. This is shown in the Index column in C25 and below.

Download the example and tutorial files for learning INDEX and MATCH functions

Click here to learn how to create drop-down lists.

Click here to learn how to create data validation lists.

Users select the Product and Region they want to chart by selecting from the drop-down lists in C7 and D7. These two selections are concatenated (joined together) to create the unique criteria shown in cell C21. The & symbol concatenates cell contents,

=$C$10&$D$10

The unique value in C21 can now be used by MATCH to look down C26:C41 to find a row containing an exact match. That gives us the row from which we want to retrieve data.

The column of data we want to retrieve is specified in row F21:Q21. The changing column numbers match the columns of the months in the table.

The formula in F21 uses the INDEX and MATCH combination to find the matching row. The column retrieved for January is specified by F$23 which is column 4 or Jan.

=INDEX($C$26:$Q$41,MATCH($C$21,$C$26:$C$41,0),F$23)

In this formula,

$C$26:$Q$41                                                     is the table of data

MATCH($C$21,$C$26:$C$41,0)                  finds the row by searching C26:C41 for an exact match to the contents of C21

F$23                                                                     is the column to retrieve data from

Download the example and tutorial files for learning INDEX and MATCH functions

Click here to learn how to use VLOOKUP with multiple criteria.

Click here to see a comparison between VLOOKUP and the combination of INDEX and MATCH.

Share the power...

Leave a Comment:

Leave a Comment: