Excel Formula for Conditional Data Extraction

Objective

Create an Excel formula that pulls data from another sheet based on a specific status defined in a cell.

Formula

To achieve this, you can use the FILTER function, which is available in Excel 365 and Excel 2021. The syntax is as follows:

=FILTER(Sheet1!A2:A100, Sheet1!B2:B100 = 'Sheet2'!D1, "No data")

Breakdown of the Formula

• FILTER: This function allows you to filter a range of data based on criteria.
• Sheet1!A2:A100: This is the range of data you want to extract (for example, names or other values).
• Sheet1!B2:B100: This is the range where your status criteria are checked (e.g., "Approved", "Pending").
• 'Sheet2'!D1: This cell contains the specific status you want to filter for (e.g., "Approved").
• "No data": This is the output when no data meets the specified criteria.

Example Scenario

Data Setup

Assume:

• Sheet1 contains:
  • Column A: Names
  • Column B: Status
• Sheet2 contains:
  • Cell D1: Criteria (e.g., "Approved")

Step-by-Step Implementation

1. Data Entry:

   • In Sheet1, enter the following data:
     • A2: John
     • B2: Approved
     • A3: Sarah
     • B3: Pending
     • A4: Mike
     • B4: Approved

2. Status Criteria:

   • In Sheet2, enter "Approved" in cell D1.

3. Apply Formula:

   • In a cell on Sheet2 (e.g., A2), enter the formula:

     =FILTER(Sheet1!A2:A100, Sheet1!B2:B100 = 'Sheet2'!D1, "No data")

Outcome

• The above formula will return:
  • A2: John
  • A3: Mike
• If the criterion in D1 is changed to "Pending", only "Sarah" will appear.

Conclusion

Utilizing the FILTER function allows for efficient data extraction based on specific conditions within Excel. This example illustrates how to set the criteria and implement the formula to dynamically pull relevant data based on user-defined conditions.

For those looking to enhance their Excel skills further, exploring the Enterprise DNA Platform may provide valuable insights and training.