## Quickly search a data set with many criteria

**Answer:**

Random data, 10000 records:

Column A contains form numbers. The question is : How to quickly filter 1500 records from this data set?

The next sheet "Search values" contains the 1500 form numbers you want to filter.

### Convert data set to a table

- Click Sheet "Data"
- Select cell A1
- Press Ctrl + A
- Go to tab "Insert"
- Click "Table" button or press Ctrl + T
- Enable "My table has headers"
- Press Ok

### Search form numbers

- Select cell G2
- Type =COUNTIF('Search values'!$A$2:$A$1501, [@['#]])
- Press Enter

Make sure you use absolute cell references. The remaining table cells in column G are automatically entered.

### Filter table

- Click the black arrow at the top in column G
- Make sure only 1 is selected

- Press Ok

Here are the 1500 form numbers:

### Download excel *.xlsx file

Quickly search a table using many criteria.xlsx

### Recommended articles

This article describes how to lookup a value across multiple columns and return a corresponding value on the same row:

Vlookup a cell range and return multiple values

VLOOKUP a multi-column range and return multiple values.

Vlookup a cell range and return multiple values

The following post demonstrates how to extract records from a data set using two different conditions:

Extract all rows that contain a value between this and that

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Extract all rows that contain a value between this and that

Extract records from a table using many criteria:

Quickly search a data set with many criteria

Mohsin Ali Raziq asks: I have problem, and o dont know how to solve it, i have data of almost […]

Quickly search a data set with many criteria

Match two criteria and return multiple records:

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Match two criteria and return multiple records

Read this post and see how to extract duplicate records:

This article describes how to filter duplicate rows with the use of a formula. It is, in fact, an array […]

Learn how to filter unique distinct records:

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

How to use a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

Count unique distinct values in a filtered Excel defined Table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Remove common records between two data sets

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

Cyril asks how to hide all columns of a range except columns whose header is found in specific cells. I am […]

Copy filtered Excel tables [VBA]

Today I want to share some pretty useful macros. My first macro copies an excel defined table with vba. It is […]

Highlight duplicates in a filtered Excel defined table

You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]

Copy excel table filter criteria [VBA]

Here is how to copy filter criteria from an excel table and use the same table filters on another table. […]

This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.

Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can expand […]

### 2 Responses to “Quickly search a data set with many criteria”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

what isthe [@['#]] that you type inside the countif formula ?

Douglas Rezende

It is a structured reference used in Excel defined Tables.

The @ character references cells on the same row as the formula.

The '# is the name of the column, not the greatest column name I admit, considering that # characters are used in structured references as well.

https://www.ablebits.com/office-addins-blog/2019/02/06/structured-references-excel-tables/