Excel Formula: Return All Values That Match Criteria

Excel formulas are incredibly powerful tools for managing and analyzing data. One of the most useful formulas for returning values that match specific criteria is the INDEX-MATCH formula combination, often considered more flexible and powerful than VLOOKUP for certain tasks. However, with the introduction of new functions like FILTER, the game has changed significantly, offering users more straightforward and efficient ways to return values that match criteria.

Using INDEX-MATCH

The INDEX-MATCH formula is a popular choice for looking up values in a table. It works by using the MATCH function to find the relative position of the value you're looking for, and then the INDEX function to return the value at that position.

Example:

Suppose you have a table with employee names in column A and their corresponding salaries in column B. You want to find the salary of a specific employee.

=INDEX(B:B, MATCH("EmployeeName", A:A, 0))

• Replace "EmployeeName" with the name of the employee you're looking for.
• A:A refers to the range of cells containing the employee names.
• B:B refers to the range of cells containing the salaries.

Using VLOOKUP

Although not as flexible as INDEX-MATCH, VLOOKUP can be simpler to use for straightforward lookups.

Example:

=VLOOKUP("EmployeeName", A:B, 2, FALSE)

• "EmployeeName" is the value to look up.
• A:B is the range of columns to search in.
• 2 is the column index that contains the value you want to return.
• FALSE means you want an exact match.

Using FILTER (Available in Excel 2019 and Later)

FILTER is a newer function that directly filters data based on criteria and returns the matched values. This function is more intuitive and straightforward for the task at hand.

Example:

If you have a table with employee data and you want to return all salaries for a specific department:

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

• B:B is the range of salaries you want to return.
• A:A="Sales" specifies the condition for filtering (looking for "Sales" in column A).

Tips for Using These Formulas

1. Absolute vs. Relative References: Understand when to use absolute (e.g., $A$1) versus relative (e.g., A1) references, depending on whether you want the reference to change or stay fixed when you copy the formula.

2. Error Handling: Be aware that these formulas can return errors if the value is not found. Use the IFERROR function to handle such cases, providing a default value or message.

3. Optimization: For large datasets, optimization techniques like using specific ranges instead of entire columns can significantly improve formula performance.

4. Criteria Complexity: If your criteria are more complex (e.g., multiple conditions), consider using more advanced techniques like array formulas or Power Query.

In summary, the choice of formula depends on your Excel version, personal preference, and the complexity of the criteria you're applying. FILTER offers a simple, direct approach when available, while INDEX-MATCH provides flexibility and power for more complex tasks.