Learn aur earn

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.

Sandeep Mittal 1 min read

Excel Day 3: VLOOKUP, XLOOKUP aur IF, interview mein poochhe jaane wale formule
Short mein
  • 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
  1. Example: dukaan ki rate list
  2. 1. XLOOKUP (naya, aasaan)
  3. 2. VLOOKUP (purana, har jagah)
  4. 3. IF: Excel faisla leta hai
  5. 4. Do formule saath: IFERROR
  6. Practice

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):

CodeSaamaanRate
101Chawal 1kg60
102Daal 1kg120
103Tel 1L150
104Cheeni 1kg45

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

  1. Rate list banaiye aur ek chhota bill: Code, Saamaan (XLOOKUP), Qty, Rate (XLOOKUP), Amount (Qty × Rate).
  2. Total par IF se discount.
  3. Day 2 ke "Zaroorat / Shauk" column par COUNTIF try kariye: =COUNTIF(D2:D10, "Shauk").

Interview ki baaki taiyaari: interview ke 10 aam sawaal.

Share karein

Comments

Be the first to comment

Comments appear after review. For advice on your own situation, please contact us directly.

Aur padhein

Excel Day 2: percentage, $ ka jaadu aur pehla chart (free mini-course)

Excel Day 2: percentage, $ ka jaadu aur pehla chart (free mini-course)

Excel Day 1: rows, columns aur pehla formula (free mini-course)

Excel Day 1: rows, columns aur pehla formula (free mini-course)

Professional email kaise likhein: 5 niyam aur 4 ready templates

Professional email kaise likhein: 5 niyam aur 4 ready templates