Good, Sir — now we move to Next Level Lookup (Pro MIS level).
This is where most people fail — but if you learn this, you’re ahead of 95%.
🔥 1. MULTIPLE CONDITION LOOKUP (No Pivot Needed)
📊 Data:
| Date | LOB | Cases |
|---|---|---|
| 1 Apr | Pay | 10 |
| 1 Apr | Loan | 20 |
| 2 Apr | Pay | 30 |
🎯 Goal:
👉 Find Cases where
Date = 1 Apr AND LOB = Pay
✅ Formula (INDEX + MATCH + Logic):
=INDEX(C2:C100, MATCH(1, (A2:A100="1 Apr")*(B2:B100="Pay"), 0))
🧠 Logic:
- (Date="1 Apr") → TRUE/FALSE
- (LOB="Pay") → TRUE/FALSE
- Multiply → both TRUE = 1
- MATCH(1, …) → finds correct row
👉 Result = 10
⚠️ Important:
👉 In old Excel → press CTRL + SHIFT + ENTER
👉 In new Excel → normal Enter
🚀 2. SUMPRODUCT VERSION (Cleaner + Powerful)
Same problem:
=SUMPRODUCT((A2:A100="1 Apr")*(B2:B100="Pay")*(C2:C100))
👉 Works like:
- SUMIFS + Lookup combined
🔥 3. 3-WAY LOOKUP (LOB + DATE + INTERVAL)
📊 Data:
| Date | LOB | Interval | Cases |
|---|---|---|---|
| 1 Apr | Pay | 0 | 5 |
| 1 Apr | Pay | 1 | 10 |
| 1 Apr | Loan | 0 | 8 |
🎯 Goal:
👉 Date = 1 Apr
👉 LOB = Pay
👉 Interval = 1
✅ Formula:
=SUMPRODUCT(
(A2:A100="1 Apr")*
(B2:B100="Pay")*
(C2:C100=1)*
(D2:D100)
)
👉 Result = 10
🧠 Why This is Powerful
👉 This replaces:
- SUMIFS
- INDEX MATCH
- FILTER
ALL in one
⚡ 4. DYNAMIC LOOKUP (User Input Based)
| Cell | Value |
|---|---|
| F1 | 1 Apr |
| F2 | Pay |
✅ Formula:
=SUMPRODUCT((A2:A100=F1)*(B2:B100=F2)*(C2:C100))
👉 Change input → result auto changes
🔥 5. REVERSE LOOKUP (Hard Level)
👉 Find LOB where Cases = 30
=INDEX(B2:B100, MATCH(30, C2:C100, 0))
🚀 6. TOP VALUE LOOKUP
👉 Highest Cases LOB
=INDEX(B2:B100, MATCH(MAX(C2:C100), C2:C100, 0))
🧠 REAL MIS APPLICATION
| Task | Formula |
|---|---|
| TAT filter | SUMPRODUCT |
| SLA % | SUMPRODUCT |
| Case lookup | INDEX MATCH |
| Multi condition | SUMPRODUCT |
🔥 FINAL PRO MINDSET
👉 Don’t think:
“Kaunsa formula use karu?”
👉 Think:
“Kitni conditions hain?”
🎯 RULE:
- 1 condition → INDEX MATCH
- Multiple → SUMPRODUCT
- Aggregation → SUMIFS
