Excel Filter Function - multiple criteria in a single column

Question

I have dataset that is about 200,000 rows by about 50 columns. I am trying to filter on the first column but want to pull multiple items.

For example, I want to filter on column A when the name is: Jones or Smith or Apple or West? My list of what I am trying to filter on is about 20 items long. Is there a way to do this without doing a seperate item for each of my search criteria?

Answer 1

Let's say your data are on Sheet 1, in A2:AZ200000 (A1:Z1 are headers).

On another sheet, say Sheet 2, you enter the values you want to filter on in A2:A21, with the same header in A1 as on Sheet 1.

The following formula will return the filtered data:

=FILTER('Sheet 1'!A2:A1Z200000, COUNTIF('Sheet 2'!A2:A21, 'Sheet 1'!A2:A200000), "")

Alternatively, you can use Advanced Filter:

• Click anywhere in your dataset.
• On the Data tab of the ribbon, in the Sort & Filter group, click Advanced.
• Click in the Criteria range box.
• Point to the list of names on Sheet 2, including the header.
• Click OK.

Answer 2

Hi,

On such a large dataset, you should be using Power Query instead.