How to Filter by List of Values in Excel


You can use the following syntax to filter a dataset by a list of values in Excel:

=FILTER(A2:C11,COUNTIF(E2:E5,A2:A11))

This particular formula filters the cells in the range A2:C11 to only return the rows where cells in the range A2:A11 contain a value from the list of values in the range E2:E5.

The following step-by-step example shows how to use this syntax in practice.

Step 1: Enter the Data

First, let’s enter the following dataset in Excel that contains information about various basketball players:

Step 2: Define List of Values

Next, let’s define a list of Team values that we’d like to filter by:

Step 3: Filter by List of Values

Next, let’s type the following formula into cell A14 to filter the dataset by the list of Team names we defined:

=FILTER(A2:C11,COUNTIF(E2:E5,A2:A11))

The following screenshot shows how to use this formula in practice:

Excel filter by list of values

Notice that the filtered dataset only contains the rows where the Team names from original dataset in the range A2:C11 are in the list of names in the range E2:E5.

Note: The FILTER function is case-insensitive. This means if you type “nets” instead of “Nets”, the function will still return the row with the “Nets” in the Team column.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: How to Use Wildcard in FILTER Function
Excel: How to Filter Cells that Contain Multiple Words
Excel: How to Count Filtered Rows

5 Replies to “How to Filter by List of Values in Excel”

  1. Hi, this was really helpfull, thank you very much.

    Is it also possible to rearrenge the list i get after the same order as my E rows?

    Thank you very much.

    1. Hi Tommy…Please elaborate on what you are wanting accomplish with an example so that we may better assist you.

      1. Hi, thanks for fast reply.

        So i normally use numbers only. And when i filter by numbers, the order of the numbers is not going from 1-1000.

        Example:

        I want to filter these numbers in this spesific order(cause i copy paste the list): 5, 3, 9, 2, 4 and 1

        But after i use the formula, the list that appear on the formular is in correct order: 1, 2, 3, 4, 5 and 9.

        So i have to rearrange the numbers manually back to the order i copy paste.

        1. Hi Tommy…To sort numbers in a specific order in Excel, you can use various sorting options available. The steps vary slightly depending on whether you want to sort in ascending or descending order, or if you have a custom sort order in mind. Here are the methods:

          ### 1. Sorting in Ascending or Descending Order

          #### Using the Sort & Filter Feature

          1. **Select the Range**: Click and drag to select the range of cells that contain the numbers you want to sort. If your data includes headers, make sure to include the header row in your selection.

          2. **Open the Sort Dialog**:
          – Go to the **Data** tab on the Ribbon.
          – Click on the **Sort & Filter** group.
          – Click either **Sort Smallest to Largest** (for ascending) or **Sort Largest to Smallest** (for descending).

          3. **Confirm the Sort**:
          – If a sort warning appears (typically if Excel detects adjacent data), select the appropriate option to sort the data correctly.

          ### 2. Custom Sort Order

          If you want to sort numbers in a custom order (e.g., based on a specific sequence not in ascending or descending order), follow these steps:

          #### Using a Helper Column

          1. **Create a Helper Column**: Add a new column next to the numbers you want to sort. This column will help define the custom sort order.

          2. **Define the Custom Order**: In the helper column, assign a number to each entry that represents its position in the desired sort order.

          Example:
          – If you want to sort the numbers `5, 2, 9, 1` in the order `2, 1, 5, 9`, you would create the helper column like this:
          “`
          Number | Helper
          5 | 3
          2 | 1
          9 | 4
          1 | 2
          “`

          3. **Select the Range**: Select the range that includes both the numbers and the helper column.

          4. **Sort by the Helper Column**:
          – Go to the **Data** tab.
          – Click on **Sort** in the Sort & Filter group.
          – In the Sort dialog, choose the helper column under the “Sort by” dropdown.
          – Choose **Smallest to Largest**.

          5. **Apply the Sort**: Click **OK** to sort the numbers based on the values in the helper column.

          ### 3. Using a Custom List

          For more complex custom sorting, you can create a custom list in Excel.

          1. **Open the Custom Lists Dialog**:
          – Go to **File** > **Options** (or **Excel Options**).
          – Select **Advanced**.
          – Scroll down to the **General** section and click on **Edit Custom Lists**.

          2. **Create a Custom List**:
          – In the Custom Lists dialog, click **New List**.
          – Enter the numbers in the desired order in the List Entries box.
          – Click **Add** and then **OK**.

          3. **Apply the Custom Sort**:
          – Select the range of cells you want to sort.
          – Go to the **Data** tab.
          – Click on **Sort**.
          – In the Sort dialog, choose the column with the numbers under the “Sort by” dropdown.
          – Select **Custom List** from the “Order” dropdown.
          – Choose your custom list and click **OK**.

          ### Summary

          – For ascending or descending order, use the **Sort Smallest to Largest** or **Sort Largest to Smallest** options in the Data tab.
          – For a custom sort order, use a helper column to define the specific order.
          – For complex custom orders, create a custom list and sort using the Custom List option.

          By following these steps, you can sort numbers in Excel according to your specific needs.

Leave a Reply

Your email address will not be published. Required fields are marked *