Mohsin Ali Raziq asks:

I have problem, and o dont know how to solve it, i have data of almost 10000 forms, from which i have to find 1500, so it is very difficult to dig out 1500 one by one through Ctrl+F, is there any way to to put all the forms number at once and then find all those just by clicking. the prinary key is Form Number.

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

  1. Click Sheet "Data"
  2. Select cell A1
  3. Press Ctrl + A
  4. Go to tab "Insert"
  5. Click "Table" button or press Ctrl + T
  6. Enable "My table has headers"
  7. Press Ok

Search form numbers

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

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

Filter table

  1. Click the black arrow at the top in column G
  2. Make sure only 1 is selected
  3. 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.

Comments(0) Filed in category: Excel, Unique distinct values, VLOOKUP 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 has a value (Column C) that […]

Comments(20) Filed in category: Excel, VLOOKUP and return multiple values

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 […]

Comments(0) Filed in category: Excel, Records, Sort values

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 a […]

Comments(143) Filed in category: Excel, Records

Read this post and see how to extract duplicate records:

Extract duplicate records

This article describes how to filter duplicate rows. The array formula in this article contains countifs, a function introduced in […]

Comments(0) Filed in category: Duplicate records, Excel

Learn how to filter unique distinct records:

Filter unique distinct row records

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

Comments(2) Filed in category: Excel, Unique distinct records