How to Extract Data From Table Based on Multiple Criteria in Excel

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.

Data - Excel Extract Data From Table Based On Multiple Criteria

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.

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.

Criteria - Excel Extract Data From Table Based On Multiple Criteria

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.

Criteria Values - Excel Extract Data From Table Based On Multiple Criteria

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.

INDEX-MATCH Array Formula - Excel Extract Data From Table Based On Multiple Criteria

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.

INDEX-MATCH Array formula result 2 - Excel Extract Data From Table Based On Multiple Criteria

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.

INDEX-MATCH non array formula - Excel Extract Data From Table Based On Multiple Criteria

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.

INDEX-MATCH non array formula 2- Excel Extract Data From Table Based On Multiple Criteria

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

INDEX-MATCH-IF Excel Extract Data From Table Based On Multiple Criteria

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.

INDEX-MATCH-IF 2 Excel Extract Data From Table Based On Multiple Criteria

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

LOOKUP formula - Excel Extract Data From Table Based On Multiple Criteria

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.

LOOKUP formula result 2 - Excel Extract Data From Table Based On Multiple Criteria

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),"")

INDEX-SMALL Formula - Excel Extract Data From Table Based On Multiple Criteria

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.

INDEX-SMALL Formula Complete result - Excel Extract Data From Table Based On Multiple 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),"")

INDEX-AGGREGATE - Formula - Excel Extract Data From Table Based On Multiple Criteria

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

INDEX-AGGREGATE - Formula result - Excel Extract Data From Table Based On Multiple 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)),"")

INDEX-MATCH COUNTIF- Excel Extract Data From Table Based On Multiple Criteria

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.

INDEX-MATCH COUNTIF final result- Excel Extract Data From Table Based On Multiple 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))

FILTER - INDEX-COUNTIF- Excel Extract Data From Table Based On Multiple Criteria

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.

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo