How to do a VLookup in Excel | Part 2

Learn: The Power of VLOOKUP in Excel

Iss Video ko dekhne k pehle, mai Recommend karuga ki aap isska pehle waale Video ko Jarur Dekhiye:

Excel Vlookup  – Part 1(Basics)

Usske↑ basis pe aap bohot kuch aap Iss Video se Samaj payege.

Abhi mera Sawal ye hai ki:

Mere pass jo Table hai,

  • Usme Chaar(4) Naam hai
  • Test 1 & Test 2 hai
  • Scores hai Out of 100 marks

Usske alawa,

Mujhe chaiye ki doo(2) Cell jo likhe hai, unme jo Naam hai:

  • Rishabh
  • Jignesh Bhai

Uske pass waale Cell mai unke Score pull hojaye.

Toh Chaliye dosto mai aapko batata hu, ye kaise karte hai:

Note: Jo Lookup Value aapne Choose kiya hai vo Lookup Value aapko pehle Column mai paaya jaaye, agar aapne table usske pehle waale Column par se select karoge toh aapko Answer nahi milega(Refer this Post)

Vlookup ka Formula istamaal karege:

  1. Rishabh ⇒ {=VLOOKUP(}
  2. Lookup Value → Rishabh(B12) ⇒ {=VLOOKUP(B12,}
  3. Table Array → Choose kare Table ko, Naam se Test 2 tak(F4:H8) ⇒ {=VLOOKUP(B12,F4:H8,}
  4. Column Index No. → Test 2(3) ⇒ {=VLOOKUP(B12,F4:H8,3,}
  5. Range Lookup → Double Click kijiye False-Exact Match(False) ⇒ {=VLOOKUP(B12,F4:H8,3,FALSE)}
  6. Press ENTER ↵

Waah Bhaiya! Answer mil gaya → Rishabh=65

Agar Column Index No. ko Change kare → Test 1(2):

{=VLOOKUP(B12,F4:H8,3,FLASE)} ⇒  {=VLOOKUP(B12,F4:H8,2,FLASE)}

Waah Bhaiya! ye bhi Answer mil gaya → Risahbh=45

Abb mera Final Sawal iss Video mai:

Agar  {=VLOOKUP(B12,F4:H8,2,FLASE)} ←ye Cell Formula ko Copy kia

Paste kia niche Jignesh Bhai mai, toh Answer aayega → Jignesh Bhai=88

Lekin mai Niche firse same Naam likhu → Jignesh Bhai

{=VLOOKUP(B12,F4:H8,2,FLASE)} ←ye Formula ko Copy-Paste karu

Toh ye Answer nahi aayega=#N/A.

Toh Chaliye Dosto, iska kaaran batata hu:

  • Formula ko Double Click kare(Dhyaan dijiye Red Border Table ki taraf)
  • Press ENTER ↵
  • Next Cell mai Press kare F2(Table ka border niche sarak jaayega)

Issiliye Jignesh Bhai ka Score aaya #N/A

Toh abb mera Objective ye hai ki, table niche sarke nahi

Chaliye Dosto, mai aapko batauga ye kaise karte hai:

  • Pehle(1st) Formula mai jaaiye
  • Table Array ko Choose kijiye(F4:H8)
  • Press F4
  • Dollars($) lagg gye ⇒ {=VLOOKUP(B12,$F$4:$H$8,2,FLASE)}
    • Dollars($) se ye Range Fix hojayega, Table kidhar nahi sarkega
  • Press ENTER ↵
  • Abb mai Copy-Paste niche Karuga → Dono(2) Jignesh Bhai mai

Waah bhaiya! yaha bhi Answer aagya Jignesh Bhai=88.

Toh Dosto, ye tha humara Vlookup ka dusra(2nd) padav(part):

Previous Vlookup | Next Vlookup