Excel Formula for Data Retrieval Based on Status

This section outlines how to retrieve data from another sheet based on a specific status without using the FILTER function. We will utilize the INDEX and MATCH functions combined with an array formula to achieve the desired results.

Objective

To extract rows of data from a source sheet (e.g., "DataSheet") to a target sheet (e.g., "SummarySheet") based on the status indicated in a particular cell (e.g., cell A1 in "SummarySheet").

Formula Components

1. INDEX Function: This function returns a value or reference of the cell at the intersection of a particular row and column in a given range.
2. MATCH Function: This function returns the relative position of an item in an array that matches a specified value.
3. Array Formula: This formula allows you to process multiple values at once.

Example Setup

Data Layout

• DataSheet: Contains raw data in columns:

  • Column A: ID
  • Column B: Name
  • Column C: Status

• SummarySheet:

  • Cell A1 will contain the status to filter by (e.g., "Active").
  • Columns A, B, C will be used to list the filtered data.

Sample Data in DataSheet

Formula

In cell A2 of the SummarySheet, enter the following array formula:

=IFERROR(INDEX(DataSheet!A:A, SMALL(IF(DataSheet!C$1:C$100=$A$1, ROW(DataSheet!C$1:C$100)), ROW(1:1))), "")

Explanation of the Formula:

• IFERROR(...): Wraps the formula to handle errors gracefully, returning an empty string (“”) if no match is found.

• INDEX(DataSheet!A:A, ...): This retrieves values from Column A of DataSheet based on the row number determined by the SMALL function.

• SMALL(IF(...), ROW(1:1)):

  • The IF(DataSheet!C$1:C$100=$A$1, ROW(DataSheet!C$1:C$100)) part generates an array of row numbers where the condition (status matches A1) is true.
  • The SMALL(..., ROW(1:1)) function returns the k-th smallest value in the array. As you drag this formula down, ROW(1:1) will increment to 1, 2, 3, etc., effectively pulling each relevant row sequentially.

Dragging the Formula

• After entering the formula in cell A2, drag the fill handle down through cells A3, A4, etc., to populate additional rows with corresponding data from the DataSheet.

Note on Array Formulas

Ensure you enter the formula as an array formula (in older versions of Excel, this is done by pressing Ctrl + Shift + Enter). In Office 365 and Excel 2021, standard entry (Enter) recognizes array formulas automatically.

Conclusion

By following the above steps, you can effectively extract data from one sheet to another based on a specified cell's status without utilizing the FILTER function. Mastery of functions like INDEX and MATCH will enhance your data analysis capabilities in Excel. For further insights into advanced techniques, consider exploring the Enterprise DNA Platform.