Excel Formula to Filter Data Based on Status

Objective

To create an Excel formula that retrieves data from another sheet where the status is either "Travel In Progress," "Travel Pending," or "Selection Pending."

Formula Explanation

The formula utilizes the FILTER function, which is available in Excel 365 and Excel 2021. The FILTER function allows for dynamic filtering of data based on specified criteria.

Formula Syntax

=FILTER(SourceRange, (StatusRange = "Travel In Progress") + (StatusRange = "Travel Pending") + (StatusRange = "Selection Pending"))

Parameters

• SourceRange: The range of data you want to return (e.g., Sheet2!A2:D100).
• StatusRange: The range containing the status values (e.g., Sheet2!B2:B100).

Logic

• The formula checks if each entry in StatusRange matches any of the specified statuses.
• The + operator acts as an OR condition among the comparisons.
• FILTER returns all rows from SourceRange where the corresponding status meets one of the conditions.

Practical Example

Let’s assume you have the following data in Sheet2:

Implementing the Formula

1. In Sheet1, where you want to retrieve the filtered data, place the following formula in cell A1:

   =FILTER(Sheet2!A2:B100, (Sheet2!B2:B100 = "Travel In Progress") + (Sheet2!B2:B100 = "Travel Pending") + (Sheet2!B2:B100 = "Selection Pending"))

Result

The result in Sheet1 will be:

Conclusion

The provided formula effectively filters and retrieves data based on specific status criteria from another sheet. Users can adapt the SourceRange and StatusRange to fit their data context. For further enhancement of Excel skills related to data analysis, consider exploring courses on the Enterprise DNA Platform.