2.1 Logical & Lookup Functions (The Swiss Army Knives of Data)

Why These Functions Separate Amateurs from Pros

• 70% of business spreadsheet errors stem from incorrect lookups or flawed logic (Source: KPMG)

• Top consulting firms test these functions rigorously in case interviews

Deep Dive on Critical Functions

1. XLOOKUP vs. VLOOKUP vs. INDEX-MATCH

Business Case:

• "A retail chain needs to merge online/offline sales data where SKUs don't align perfectly" → XLOOKUP with approximate match & if_not_found

2. FILTER & UNIQUE (Game Changers for Dynamic Reports)

=FILTER(SalesData, (Region="West")*(Qty>100), "No matches")  
=UNIQUE(FILTER(Clients, Year="2024"))  

Academic Application:

• "Extract all unique journal titles from a messy research database"

3. IFS vs. Nested IF (Readability Matters)

=IFS(Score>=90,"A", Score>=80,"B", TRUE,"C")  // Clean alternative to:  
=IF(Score>=90,"A",IF(Score>=80,"B","C"))     // Nested hell  

Assignment:

• Rebuild a client tiering model using XLOOKUP & IFS that auto-updates when CRM data changes