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

We have a dataset of movies, their genres, leading actors, and years of release.

We will provide the Genre and Actor name as criteria in a separate table and extract the Movie name.

Method 1 – Extracting a Single Value Based on Multiple Criteria

Case 1 – Using an INDEX-MATCH Array Formula

We put Action as Genre and Christian Bale as Actor.

Steps:

• Insert the following formula in cell H7.

• Since this is an array formula, press Ctrl + Shift + Enter to apply it if you do not have Excel 365. Otherwise, press Enter.
• Change the criteria values, and you will find updated values.

Read More: How to Extract Specific Data from a Cell in Excel 

Case 2 – Using an INDEX-MATCH Non-Array Formula

Steps:

• Insert the following formula in cell H7.

• Hit Enter.
• Feel free to modify the criteria values.

Read More: How to Extract Data Based on Criteria from Excel

Case 3 – Applying an INDEX-MATCH-IF Combination

Steps:

• Insert the following formula in cell H7.

• Since this is an array formula, press Ctrl + Shift + Enter to apply it if you do not have Excel 365. Otherwise, press Enter.

Read More: How to Extract Data from Excel Sheet 

Case 4 – Use the LOOKUP Function

The LOOKUP function performs a matching lookup in a range and returns the corresponding value. It’s a slightly obsolete function that is not recommended for large datasets.

Steps:

• Insert the following formula in cell H7.

• Press Enter.
• Change the criteria value to see whether the formula works for other values.

Method 2 – Extracting Multiple Values Based on the Criteria

Case 1 – Using the INDEX-SMALL Combination

Steps:

• Make sure the dataset starts with row 1.
• Insert the following formula in cell H4.

• Hit Enter.
• Drag down the formula with the Fill Handle tool.

• You can see the multiple outcomes in cells H4, H5, and H6.

Read More: How to Extract Data from a List Using Excel Formula

Case 2 – Applying the INDEX-AGGREGATE Combination

Steps:

• Make sure the dataset starts at row 1.
• Insert the following formula in cell H4.

• Since this is an array formula, press Ctrl + Shift + Enter to apply it if you do not have Excel 365. Otherwise, press Enter.
• Drag down the formula with the Fill Handle tool.

• You can see the multiple outcomes in cells H4, H5, and H6.

Case 3 – Applying the INDEX-MATCH-COUNTIF Combination

Steps:

• Insert the following formula in cell H7.

• Hit Enter.
• Drag down the formula with the Fill Handle tool.

• You can see the multiple outcomes in cells H7, H8, and H9.

Read More: How to Extract Data from Cell in Excel 

Case 4 – Using FILTER Function

This function is only available in Excel 365 and newer versions of Excel.

Steps:

• Insert the following formula in cell H7.

• Hit Enter.
• You can see the multiple outcomes in cells H7, H8, and H9.
  

Method 3 – Use the Custom Filter Feature

We will extract the movies that have been released between 2006 and 2013.

 Steps:

• Select only the headers of the dataset.
• Go to the Data tab and select Filter.

• You can see a drop-down button in each header name of the dataset.

• Click on the drop-down button next to the Release column.
• Select Number Filters and choose Between.

• A Custom AutoFilter dialog box will pop up.
• Type 2006 in the greater than or equal to box.
• Type 2013 in the less than or equal to box.
• Click OK.

• You will get all the details for movies that were released between these years.

Method 4 – Apply the Advanced Filter Feature

To utilize the Advanced Filter option in Excel, you have to define the condition in your worksheet to use later. We define our condition of extracting movie details of Release year in cells G5 and H5. We typed >=2007 in cell G5 and <=2013 in cell H5. We will be using the cell reference numbers of those cells.

Steps:

• Select the whole data table.
• From the Data tab select Advanced in the Sort & Filter group.

• An Advanced Filter dialog box will appear.
• You will see the range of your selected data in the box next to the List range option.
• In the box for the Criteria range, select the cells carrying the defined conditions. We selected cells G4:H5.
• Click OK.

• You will get all the details only for the movies that were released between 2008 to 2013.

Method 5 – Insert a Table to Extract Data Based on Criteria

We will extract the movies released between 2008 and 2013.

Steps:

• Select the cells B4:E20.
• Go to the Insert tab and select Table.

• A Create Table dialog box will appear.
• Make sure My table has headers is marked.
• Click OK.

• The headers of the Table have drop-down icons.

• Click on the drop-down button next to the Release column.
• From the drop-down list, select Number Filters and choose Between.

• A Custom AutoFilter dialog box will pop up.
• Type 2008 in the greater than or equal to box.
• Type 2013 in the less than or equal to box.
• Click OK.

• You will get all the details only for the movies that were released between 2008 to 2013.

Method 6 – Apply the SUMPRODUCT Function for Numerical Values

We have a new dataset with the Name, Subject, and Score columns. You can see the criteria where we will find the Score in cell G6 for the name Rose, and for the subject Math.

Steps:

• Insert the following formula in cell G6.

• Hit Enter.

Method 7 – Use the SUM Function for Numerical Results

Steps:

• Insert the following formula in cell G6.

• Hit Enter.

Method 8 – Use the SUMIFS Function for Finding Numerical Values

Steps:

• Insert the following formula in cell G6.

• Hit Enter.

Practice Section

We’ve included practice datasets you can use to test these methods.

Download the Practice Workbook

Extract Data from Table Based on Multiple Criteria.xlsx

Further Readings

• Excel Formula to Get First 3 Characters from a Cell
• How to Extract Month and Day from Date in Excel
• How to Extract Month from Date in Excel
• How to Extract Year from Date in Excel

<< Go Back To Extract Data Excel | Learn Excel