Excel Return All Values That Match Criteria

Matching values in Excel can be a common task, especially when working with large datasets. There are several ways to return all values that match a specific criteria in Excel, and we will explore some of the most common methods.

Using INDEX-MATCH Function

One of the most powerful and flexible ways to return all values that match a specific criteria is by using the INDEX-MATCH function combination. The syntax for this function is:

=INDEX(range, MATCH(lookup_value, lookup_array, [match_type])

Where:

• range is the range of cells that you want to return values from.
• lookup_value is the value that you want to search for.
• lookup_array is the range of cells that you want to search in.
• [match_type] is the type of match that you want to perform (e.g. exact match, approximate match, etc.).

For example, suppose you have a table with the following structure:

Employee ID	Name	Department
101	John Smith	Sales
102	Jane Doe	Marketing
103	Bob Johnson	Sales
104	Maria Rodriguez	HR

If you want to return all the names of employees in the Sales department, you can use the following formula:

=INDEX(A:A, MATCH("Sales", C:C, 0))

This formula will return the value "John Smith" and "Bob Johnson".

Using FILTER Function

Another way to return all values that match a specific criteria is by using the FILTER function, which is a new function in Excel 365. The syntax for this function is:

=FILTER(range, criteria)

Where:

• range is the range of cells that you want to filter.
• criteria is the criteria that you want to apply to the filter.

For example, suppose you have the same table as before:

Employee ID	Name	Department
101	John Smith	Sales
102	Jane Doe	Marketing
103	Bob Johnson	Sales
104	Maria Rodriguez	HR

If you want to return all the names of employees in the Sales department, you can use the following formula:

=FILTER(A:A, C:C="Sales")

This formula will return the values "John Smith" and "Bob Johnson".

Using PivotTables

PivotTables are a powerful tool in Excel that can help you summarize and analyze large datasets. You can use PivotTables to return all values that match a specific criteria by creating a filter on the PivotTable.

For example, suppose you have the same table as before:

Employee ID	Name	Department
101	John Smith	Sales
102	Jane Doe	Marketing
103	Bob Johnson	Sales
104	Maria Rodriguez	HR

To create a PivotTable that returns all the names of employees in the Sales department, follow these steps:

1. Select the entire table range (A:C).
2. Go to the "Insert" tab in the ribbon.
3. Click on "PivotTable".
4. Choose a cell to place the PivotTable.
5. In the PivotTable Fields pane, drag the "Department" field to the "Filters" area.
6. Right-click on the "Department" filter and select "Filter" > "Sales".

The PivotTable will now return the values "John Smith" and "Bob Johnson".

Using VLOOKUP Function

The VLOOKUP function is another way to return all values that match a specific criteria in Excel. The syntax for this function is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Where:

• lookup_value is the value that you want to search for.
• table_array is the range of cells that you want to search in.
• col_index_num is the column number that you want to return values from.
• [range_lookup] is the type of match that you want to perform (e.g. exact match, approximate match, etc.).

For example, suppose you have the same table as before:

Employee ID	Name	Department
101	John Smith	Sales
102	Jane Doe	Marketing
103	Bob Johnson	Sales
104	Maria Rodriguez	HR

If you want to return all the names of employees in the Sales department, you can use the following formula:

=VLOOKUP("Sales", A:C, 2, FALSE)

This formula will return the value "John Smith" and "Bob Johnson".

Gallery of Excel Functions to Return All Values That Match Criteria

Excel Functions to Return All Values That Match Criteria

We hope this article has helped you learn how to return all values that match a specific criteria in Excel. Whether you use the INDEX-MATCH function, FILTER function, PivotTables, or VLOOKUP function, there are many ways to achieve this in Excel. If you have any questions or need further assistance, please don't hesitate to ask.