FILTER Function: Extract Sales Data Meeting Multiple Criteria

FILTER Function: Dynamic Multi-Criteria Extraction

Traditional Excel me data filter karne ke liye AutoFilter click karna padta tha ya complex INDEX-MATCH-AGGREGATE helper formulas banane padte the. Modern Excel ka FILTER function pure dataset ko dynamically query karta hai.

1. Syntax & Core Mechanics

=FILTER(array, include, [if_empty])

• array: Original data table ya range (e.g., A2:E200).
• include: Boolean array criteria. TRUE rows return hongi, FALSE filter out hongi.
• if_empty: Optional message (e.g., "No Records Found") agar koi match na mile.

2. Multi-Criteria Filtering: AND Logic (*) & OR Logic (+)

Corporate reporting me single criteria kaafi nahi hota. Example: Tata Motors EV dealer sales me hume wo dealers chahiye jo "West" region me hain AUR jinki quarterly sales > ₹50,00,000 hain:

=FILTER(A2:F150, (C2:C150="West") * (E2:E150>5000000), "No Qualifying EV Dealers")

Agar hume "Nexon EV" YA "Punch EV" me se kisi ka bhi sales record chahiye (OR condition):

=FILTER(A2:F150, (D2:D150="Nexon EV") + (D2:D150="Punch EV"), "No Matching Models")

[!TIP] Excel me Boolean math follow hota hai: Multiplication (*) represents AND condition (both conditions must be 1/TRUE). Addition (+) represents OR condition (either condition can be 1/TRUE).

3. Slicing Output Columns: FILTER inside CHOOSECOLS

Agar pure 20 columns return nahi karne, balki sirf Dealer Name aur Revenue columns display karne hain:

=CHOOSECOLS(FILTER(A2:F150, C2:C150="West"), 2, 5)