Excel Day 3: VLOOKUP, XLOOKUP aur IF, interview mein poochhe jaane wale formule
Excel mini-course ka teesra din: ek list se doosri list mein data dhoondhna (VLOOKUP, XLOOKUP), aur IF se Excel ko faisle lena sikhaana. Dukaan ke rate list ka example.

- XLOOKUP ek table se doosri table mein cheez dhoondhta hai; VLOOKUP* uska purana roop hai.
- VLOOKUP mein aakhri argument hamesha FALSE (exact match) rakhiye.
- IF* se Excel shart ke hisaab se jawab deta hai, jaise "Pass" ya "Fail".
Is post mein
Excel mini-course ka teesra din. Aaj ke formule office interviews mein sabse zyada poochhe jaate hain. Pehle Day 2 dekh lijiye.
Example: dukaan ki rate list
Ek sheet mein rate list (A se C column):
| Code | Saamaan | Rate |
|---|---|---|
| 101 | Chawal 1kg | 60 |
| 102 | Daal 1kg | 120 |
| 103 | Tel 1L | 150 |
| 104 | Cheeni 1kg | 45 |
Ab bill mein sirf code likhna hai, aur saamaan ka naam aur rate apne aap aana chahiye. Maan lijiye bill ka code cell F2 mein hai.
1. XLOOKUP (naya, aasaan)
=XLOOKUP(F2, A2:A5, C2:C5)
Matlab: F2 ka code A column mein dhoondho, aur usi row ka C column (rate) lao. Code na mile to: =XLOOKUP(F2, A2:A5, C2:C5, "Code galat").
XLOOKUP Microsoft 365, Excel 2021 aur naye versions mein, aur Google Sheets mein bhi hai.
2. VLOOKUP (purana, har jagah)
=VLOOKUP(F2, A2:C5, 3, FALSE)
- F2: kya dhoondhna hai
- A2:C5: poori table (dhoondhne wala column sabse baayen hona chahiye)
- 3: table ka teesra column (Rate) lao
- FALSE: bilkul wahi code (exact match). Ise bhoolne par galat rate aa sakta hai.
Formula neeche kheenchna ho to table ko $ se pakadiye: $A$2:$C$5 (Day 2 yaad hai?).
3. IF: Excel faisla leta hai
=IF(B2>=33, "Pass", "Fail"): marks 33 ya zyada to Pass, warna Fail.
Dukaan ka example: bill ₹1,000 se zyada ho to 5% discount: =IF(G2>1000, G2*5%, 0).
4. Do formule saath: IFERROR
=IFERROR(VLOOKUP(F2, $A$2:$C$5, 3, FALSE), "Nahi mila"): code na mile to #N/A ki jagah saaf message.
Practice
- Rate list banaiye aur ek chhota bill: Code, Saamaan (XLOOKUP), Qty, Rate (XLOOKUP), Amount (Qty × Rate).
- Total par IF se discount.
- Day 2 ke "Zaroorat / Shauk" column par COUNTIF try kariye: =COUNTIF(D2:D10, "Shauk").
Interview ki baaki taiyaari: interview ke 10 aam sawaal.



Comments