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

In Microsoft Excel sometimes we may need to extract data from a range or list. This process can be done in various ways. Today in this article, I am going to share with you how you can extract data from a list using excel formula. So, Let’s get started.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


5 Suitable Methods to Extract Data from a List Using Excel Formula

In the following section, 5 methods to extract data from a list using formulas will be thoroughly discussed.

1. Combine INDEX, MATCH, COUNTIF Functions to Extract Unique Values from a List

From the following Dataset, we will describe extracting unique values combining the INDEX, MATCH, COUNTIF Functions.

Suppose, we have a dataset containing some names. Now we will extract unique names from the list with the help of excel formulas. Follow these steps below to learn!

Extract Data from a List Using Excel Formula

Step 1:

  • Click on the cell (D5) where we want our result.
  • Then we will apply the following formula-
=INDEX(B5:B13,MATCH(0,COUNTIF($D$4:D4,B5:B13),0))

Where,

  • INDEX(B5:B13)= Retrieve 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

Step 2:

  • As this formula is an array formula, we need to press Ctrl + Shift + Enter at the same time to apply it. After pressing it, we will find the unique name in the column.

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

Step 3:

  • To see all the unique names, we have to drag down the cell using the autoFill.

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

Thus, we will get all the unique names from the list in our desired column.

Read More: How to Extract Data from Excel Based on Criteria (5 Ways)


2. Merge FILTER, COUNTIF Functions to Extract Common Values from a List in Excel

In the previous method, we have extracted items from one list. In this method, we will compare two lists and find common values from the lists.

Following is a dataset containing names in two columns. Now, we will find common names in different columns by combining the FILTER and the COUNTIF Functions.

Merge the FILTER, COUNTIF Functions to Extract Common Values

Steps :

  • Select Cell (F5) where we will get our result.
  • Apply this formula-
=FILTER(B5:B13,COUNTIF(D5:D13,B5:B13))

Where,

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

Merge the FILTER, COUNTIF Functions to Extract Common Values

  • Pressing Enter we will get only one name in the desired column.
  • Dragging down by applying the autoFill handler, we will find all the common names from both lists.

Merge the FILTER, COUNTIF Functions to Extract Common Values

Read More: Extract Filtered Data in Excel to Another Sheet (4 Methods)


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

When we extract data from a list sometimes there are different types of conditions. In this method, we are going to extract data containing single criteria and multiple criteria. You can visit this link for more information.

3.1 Extract Data from a List Based on Single Criteria

Suppose, 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. In this method, we will find the associated names with the product named Mango. To do this, we will use the combination of the IFERROR, INDEX, IF, MATCH, and COUNTIF Functions.

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

Where,

  • 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 simultaneously to see the results.
  • Now in the Output product column, we can put any product from our list. So, it will show the names related to the product.
  • In the following section, we have put “Mango” in the output product column. Thus, in the Names column, all the names regarding the product mango are shown in the column.

Extract Data from a List Based on Single Criteria

3.2 Extract Data from a List Based on Multiple Criteria

In this section, we will discuss extracting data with Array formulas based on multiple criteria. In the following dataset, we have taken three columns containing Product, Region, and Names. On the right side, there are columns to show desired outputs. Here we will apply the combo of the IFERROR, INDEX, MATCH, COUNTIF, and the IF functions.

Extract Data from a List Based on Multiple Criteria

Steps :

  • Select Cell(H5) where the output will be shown.
  • Then 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)),"")

Where,

  • 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.
  • 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 at the same time, we will get the output.
  • Now, in the output columns, if we choose product and region, this formula will filter the list and show us the names related to these multiple criteria.

Extract Data from a List Based on Multiple Criteria

Thus, we can get our results against multiple criteria.

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


Similar Readings


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

Now we will find some unique names using the LOOKUP and the COUNTIF Functions. In the following dataset, we took some names from which we will extract only the unique names.

Combine the LOOKUP and COUNTIF Functions to Extract Data

Steps:

  • Select cell (D5).
  • Enter the 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)

Where,

  • The COUNTIF Function counts each value and returns values.
  • 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, we will see the name in the list.

Combine the LOOKUP and COUNTIF Functions to Extract Data

  • To get all the unique names to drag down below using the autoFill feature.

Combine the LOOKUP and COUNTIF Functions to Extract Data

Here are all the unique names from the list.

Read More: Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP


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

In the following method, we will extract data within the given range. 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. To do this, we will use the combination of the INDEX, SMALL, IF, MATCH, ROW, ROWS, and COLUMNS functions.

Steps:

  • According to the range, 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) where we want our 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))

Where,

  • 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 end value.
  • INDEX($B$5:$E$14,,$I$6)>=$I$4 = this part entered 80 as starting value.

Now, press Ctrl+ Shift + Enter simultaneously to apply this formula. We will get our first extracted data in cells that match the condition

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

Then drag around the fill handle towards the row and column. Thus, the resulting data will be shown which matches the condition.

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

Read More: How to Pull Data From Another Sheet Based on Criteria 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.

Conclusion

In this article, we have learned about how to extract data from a list using excel formula. I hope this article has helped you with the problems. Feel free to ask us questions in the comment section if you find any problems.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo