How to Extract Data from a List Using Excel Formula (5 Methods)

Method 1 – Combine INDEX, MATCH, and COUNTIF Functions to Extract Unique Values from a List

We have a dataset containing some names. We will extract unique names from the list.

Extract Data from a List Using Excel Formula

Steps:

  • Click on the first cell (D5) where you want the result.
  • Apply the following formula:
=INDEX(B5:B13,MATCH(0,COUNTIF($D$4:D4,B5:B13),0))

INDEX(B5:B13)= Retrieves values to add to the unique list.

COUNTIF($D$4:D4,B5:B13)= Counts unique values contained in the range.

The MATCH Function finds the exact match & zero values for the function.

Combine the INDEX, MATCH, COUNTIF Functions to Extract Unique Values

  • Press Ctrl + Shift + Enter to apply it. You’ll get the first result.

Combine the INDEX, MATCH, COUNTIF Functions to Extract Unique Values

  • Drag down the cell using AutoFill.

Combine the INDEX, MATCH, COUNTIF Functions to Extract Unique Values


Method 2 – Merge FILTER and COUNTIF Functions to Extract Common Values from a List in Excel

We will find common names in different columns from the two lists.

Merge the FILTER, COUNTIF Functions to Extract Common Values

Steps:

  • Select cell F5.
  • Apply this formula:
=FILTER(B5:B13,COUNTIF(D5:D13,B5:B13))

COUNTIF(D5:D13,B5:B13)= Takes List-2 (D5:D13) as range and List-1 (B5:B13) as criteria and counts common items. The Filter Function filters value got from the COUNTIF Function.

Merge the FILTER, COUNTIF Functions to Extract Common Values

  • Pressing Enter gets only one name in the desired column.
  • Drag down the Fill Handle to find all the common names from both lists.

Merge the FILTER, COUNTIF Functions to Extract Common Values


Method 3 – Apply an Array Formula to Extract Data from a List Based on Criteria

Case 3.1 – Extract Data from a List Based on Single Criteria

We have a dataset containing one list with Products and another list with product owner Names. In the other two columns, results will be shown. We will find the names that correspond to the product named Mango.

Extract Data from a List Based on Single Criteria

Steps:

  • Select cell G5.
  • Enter the following formula:
=IFERROR(INDEX($D$5:$D$19, MATCH(0, IF($F$5=$B$5:$B$19, COUNTIF($G$4:$G4, $D$5:$D$19), ""), 0)),"")

IF($F$5=$B$5:$B$19, COUNTIF($G$4:$G4, $D$5:$D$19) works as the lookup array. The COUNTIF($G$4:$G4, $D$5:$D$19) part filters the data where $G$4:$G4 is the range and $D$5:$D$19 is the criteria. We want the exact match, so we select 0. The INDEX function introduces a range for the IFERROR Function and the MATCH function returns the position of the item in the range.

Extract Data from a List Based on Single Criteria

  • Press Ctrl + Shift + Enter to see the results.
  • In the Output product column, we can put any product from our list. It will show the names related to the product. We have put “Mango” in the output product column.

Extract Data from a List Based on Single Criteria


Case 3.2 – Extract Data from a List Based on Multiple Criteria

We have taken three columns containing Product, Region, and Names. On the right side, we’ll use both product and region as criteria.

Extract Data from a List Based on Multiple Criteria

Steps:

  • Select cell H5 where the output will be shown.
  • Enter the following formula:
=IFERROR(INDEX($D$5:$D$19,MATCH(0,COUNTIF(H4:$H$4,$D$5:$D$19)+IF($B$5:$B$19<>$F$5,1,0)+IF($C$5:$C$19<>$G$5,1,0),0)),"")

IF($B$5:$B$19<>$F$5,1,0)+IF($C$5:$C$19<>$G$5,1,0),0)= working as a lookup array.

COUNTIF(H4:$H$4,$D$5:$D$19)= This part filters the data where (H4:$H$4) works as range and ($D$5:$D$19) works as criteria. For the exact match, we use 0.

The INDEX function introduces a range for the IFERROR Function and the MATCH function returns the position of the item in the range.

Extract Data from a List Based on Multiple Criteria

  • Pressing Ctrl + Shift + Enter applies the formula and returns all the results.
  • If we choose a product and a region, this formula will filter the list and show the names related to these multiple criteria.

Extract Data from a List Based on Multiple Criteria

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


Method 4 – Combine the LOOKUP and COUNTIF Functions to Extract Data from a List in Excel

In the following dataset, we duplicated some names, so we’ll extract the unique values.

Combine the LOOKUP and COUNTIF Functions to Extract Data

Steps:

  • Select cell D5.
  • Enter the following formula in the cell:
=LOOKUP(2,1/((COUNTIF($D$4:D4,$B$5:$B$14)=0)*(COUNTIF($B$5:$B$14,$B$5:$B$14)=1)),$B$5:$B$14)

The COUNTIF Function counts each value and returns values. The LOOKUP Function will match the value in the array and return in the result range.

Combine the LOOKUP and COUNTIF Functions to Extract Data

  • Press Enter to get the first name in the list.

Combine the LOOKUP and COUNTIF Functions to Extract Data

  • Drag down using the AutoFill feature until you start getting error values.

Combine the LOOKUP and COUNTIF Functions to Extract Data


Method 5 – Merge INDEX, SMALL, MATCH, ROW, and COLUMNS Functions to Extract Data from a List

The following dataset contains student information. We took a range of marks from 80 to 100. Students whose marks are between 80 to 100 will be shown in the output column.

Steps:

  • The starting value is 80 and the end value is 100.
  • As marks are in the third column, we put 3 in the I6

Merge the INDEX, SMALL, MATCH, ROW, and COLUMNS Functions to Extract Data

  • Select cell G11 for the result.
  • Enter the following formula:
=INDEX($B$5:$E$14,SMALL(IF((INDEX($B$5:$E$14,,$I$6)<=$I$5)*(INDEX($B$5:$E$14,,$I$6)>=$I$4),MATCH(ROW($B$5:$E$14),ROW($B$5:$E$14)),""),ROWS(G11:$G$11)),COLUMNS($A$1:A1))

INDEX($B$5:$E$14,,$I$6)= This part usually returns a single value or an entire column or row from a given cell range.

INDEX($B$5:$E$14,,$I$6)<=$I$5 = this section entered 100 as the end value.

INDEX($B$5:$E$14,,$I$6)>=$I$4 = this part entered 80 as starting value.

  • Press Ctrl + Shift + Enter simultaneously to apply this formula.

Merge the INDEX, SMALL, MATCH, ROW, and COLUMNS Functions to Extract Data

  • Drag the fill handle to the right to fill the row, then down.

Merge the INDEX, SMALL, MATCH, ROW, and COLUMNS Functions to Extract Data

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


Things to Remember

  • As the range of the data table array to search for the value is fixed, don’t forget to put the absolute reference ($) sign in front of the cell reference number to avoid the error.
  • The FILTER function is only available in Excel 365. You won’t be able to use it if you are using other versions of MS Excel.
  • When you are applying an array formula, you have to press Ctrl + Shift + Enter simultaneously to apply the formula.

Download the Practice Workbook


Related Articles

<< Go Back To Extract Data Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

2 Comments
  1. Working excellently. Thanks for sharing!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo