Extracting the data on the time of necessity is the prime factor of using any spreadsheet, Excel is not an exception. You store data in tabular form in Excel to extract them. Today we are going to show you how to extract data from a table based on multiple criteria.

First things first, let’s get to know about the dataset which is the base of our examples.

Here we have a table that contains a list of movies with the genre and lead actor along with the releasing year. Using this dataset, we will retrieve data based on multiple criteria.

Note that this is a basic table to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.

**Table of Contents**hide

## Practice Workbook

You are welcome to download the practice workbook from the following link.

## Extract Data Based on Multiple Criteria

Here for example we will provide genre and actor name as criteria and based on these criteria we will extract the movie name.

### 1. Return Single Value

In this section, we will return a single value. Based on the criteria only one value will be fetched. Let’s explore.

#### I. INDEX-MATCH Array Formula

We can use the combination of the **INDEX **and **MATCH** functions. **INDEX** returns the value at a given location in a range. **MATCH** locates the position of a lookup value in a range.

To know these functions, check these articles: INDEX, MATCH.

Let’s set the criteria value. For the time being Thriller as *Genre* and Hugh Jackman at the *Actor* field.

The formula will be the following one

`=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0))`

**B4:B19** is the array from where we will extract value. And the **MATCH** function sets the row number that is to be fetched.

You can see we have provided 1 as the *lookup_value* within **MATCH**. And *lookup_array *is generated through the multiplication of criteria matching logic.

Through **$H$4=$C$4:$C$19** we have checked for the genre and **$H$5=$D$4:$D$19 **for the actor name.

It finds 1 among the array of the multiplication result. And return the row number and the **INDEX** function returns the movie name.

It’s an array formula so we need to use **CTRL + SHIFT + ENTER** to execute it.

Change the criteria values, and you will find updated values.

#### II. INDEX-MATCH Non-Array Formula

We can form a non-array formula combining **INDEX **and **MATCH**.

Let’s see the formula first

`=INDEX($B$4:$B$19,MATCH(1,INDEX(($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0,1),0))`

Here you can see, we have used a couple of **INDEX** functions there. The outer **INDEX** function does the extraction job where the insider one helps to detect the row number.

Within the inner **INDEX, **we have checked the criteria value. Here the two logical operations multiplied within **INDEX **and acted as the array reference.

Mere **ENTER** will execute the formula.

Feel free to modify the criteria values, you will find updated values.

#### III. INDEX-MATCH-IF Combination

In earlier sections, we have checked the conditions and multiplied them to force them to act together. We can leave the multiplication by using the **IF** function.

**IF **runs a logical test and returns the Boolean value (**TRUE** or **FALSE**) as result. To know about the function visit this IF article.

Our formula will be the following one

`=INDEX($B$4:$B$16,MATCH(1,IF($C$4:$C$16=$H$4,IF($D$4:$D$16=$H$5,1)),0))`

Here we have two conditions to match that’s why two **IF**. They are working as nested **IF** (one inside another). The outer **IF** function checks a condition first (irrespective of the sequence) and the second condition (inner **IF)** is the *if_true_value* for the first **IF**.

You need to use the **CTRL+SHIFT+ENTER** to execute the formula.

Change the criteria values, and you will find updated a value.

#### IV. LOOKUP Function

We can use the **LOOKUP** function to perform our task of extracting data based on criteria.

The **LOOKUP** function performs a matching lookup in a range and returns the corresponding value. For further information, visit the Microsoft Support site.

Now let’s explore the formula

`=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))`

Here we have set 2 as the *lookup_value*. And the two logical operations in a form of divide 1 by them is the *lookup_vector*.

Here, it divides 1 by an array of **TRUE/FALSE** values (**$C$4:$C$19=$H$4**), and then by another array of **TRUE/FALSE** values (**$D$4:$D$19=$H$5**). This will return either 1 or a **#DIV/0!** error.

The *lookup_value *tells the formula to match it with the numeric value in the range, once the match is found the value will be derived from the array **B4:B19**.

No need to press **CTRL + SHIFT + ENTER** to execute.

Change the criteria value to see whether the formula is working perfectly or not for other values.

Note that we have used 2 as the *lookup_value*. This can be any number starting from 1.

### 2.Return Multiple Values

#### I. INDEX-SMALL Combination

Different function combinations can be used for extracting multiple data based on criteria. One of the combinations is the **INDEX **– **SMALL **combination.

The **SMALL** function returns values based on their position in a list ranked by value. To know more check this SMALL article.

Along with these two, we will need a few helper functions, **IF**, **ROW** and **IFERROR**. Check the articles for further information: IF, ROW, IFERROR.

Our formula will be the following one

`=IFERROR(INDEX($B$2:$B$17,SMALL(IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")`

Here every function has its purpose. The **INDEX** function returns the value from the array **B2:B17** and the big **SMALL** portion provides the row number, that is to be fetched.

**IF,** within the **SMALL,** checks whether the criteria are matched or not. We have two conditions to match, that’s why we multiplied both the logical operations to check the criteria. And the **ROW **function iterates over the cells of the column.

Then the outer **ROW** denotes the k-th value for the **SMALL** function. Together these functions return the row number and **INDEX **returns the result.

**IFERROR** to deal with any error that may arise from the formula. We have set it in such a way that for error it will provide an empty cell.

Drag it down, you will get all the values that match the criteria.

#### II. INDEX-AGGREGATE Combination

The **AGGREGATE **function in Excel allows us to perform various tasks. One function for several operations. We can use this function to return multiple values based on multiple criteria.

Let’s get to know about the function a little bit, the **AGGREGATE **function returns an aggregate calculation like AVERAGE, COUNT, MAX, etc.

The syntax for the **AGGREGATE **function is as follows:

`AGGREGATE(function_number,behavior_options, range)`

**function_number: **This number specifies which calculation should be made.

**behavior_options: **Set this using number. This number denotes how the function will behave.

**range: **Range you want to aggregate.

The **AGGREGATE **function does several tasks so numbers of functions are predefined within it. We are listing few frequently used function numbers

Function | Function_number |
---|---|

AVERAGE | 1 |

COUNT | 2 |

COUNTA | 3 |

MAX | 4 |

MIN | 5 |

PRODUCT | 6 |

SUM | 9 |

LARGE | 14 |

SMALL | 15 |

To know more about the function, visit the Microsoft Support site.

Now let’s see the formula, it will be a combination of **INDEX **and **AGGREGATE**.

`=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")`

Here we have used **15** as the *function_number* in **AGGREGATE**. From the above table, you can see **15** calls for the **SMALL** function operation. You can see apart from using **AGGREGATE** (and the function number and behavior option number) the formula is exactly the same as the earlier **INDEX**–**SMALL **formula.

The mechanism is the same, **INDEX** holds the array that returns values based on matches found at the **AGGREGATE **portion of the formula.

**6** for the behavior option, which denotes **ignore error values**.

Drag it down, you will get all the values that match the criteria.

Remember to use **CTRL+SHIFT+ENTER** to execute the formula.

#### III. INDEX-MATCH-COUNTIF Combination

For returning multiple values based on multiple criteria, we can use the combination of **INDEX**, **MATCH**, and **COUNTIF**.

**COUNTIF **counts cells in a range that meets a single condition. To know more about this function, visit this article: COUNTIF.

Our formula will be the following one

`=IFERROR(INDEX($B$4:$B$19,MATCH(0,COUNTIF(H5:$H$5,$B$4:$B$19)+IF($C$4:$C$19<>$H$4,1,0)+IF($D$4:$D$19<>$H$5,1,0),0)),"")`

Within the **MATCH** function, we provided 0 as the *lookup_array,* and for *lookup_range* we have used the **IF** portion containing **COUNTIF**.

Here, the **COUNTIF** function excludes any value that has already been fetched. And two **IF **functions check two conditions. We have added these functions so that they together form the *lookup_range*.

The **MATCH** portion returns the value as long as 0 is found. The value here works as the row number for **INDEX**.

Drag it down, you will get all the values that match the criteria.

#### IV. FILTER Function

If you are using Excel 365, then you can perform the task with a single built-in function called **FILTER**.

The **FILTER** function filters a range of data based on given criteria and extracts matching records. To know about the function, visit this article: FILTER.

Let’s explore the formula

`=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))`

**B4:B19** is the array that is to be filtered. Then we have provided the condition, based on what we will extract values. Since we need to check two criteria, we have multiplied them.

Here you will not need to drag down the formula, at one go this will provide all the values and fulfill the list.

## Conclusion

That’s all for today. We have listed several methods to extract data from a table based on multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other approaches that we have missed here.