2D lookup using Vlookup and Match functions in Excel | Example 2

Learn: 2D lookup using Vlookup and Match functions in Excel | Example 2

Iss Video mai hum dekhege Vlookup+Match kaise istamaal kia jaaye?

Mai aapko ek Practical Example se samjhata hu ki, Vlookup+Match kya hai?

Aapke pass ek Price Card hai, jaha par alag alag Bikes k Prices diye gye hai, alag alag Shero(Cities) mai Bikes k Prices alag hai:

Mai chahta hu ki, mujhe Bikes k naam likhu aur Cities k naam likhu, toh apne aap Answer mil jaana chaaiye:

Toh chaliye dosto, mai batata hu aapko, kaise karte hai:

  1. Type ⇒ {=VLOOKUP(}
  2. Lookup Value → Select Bike ZX200 ⇒ {=VLOOKUP(B8,}
  3. Table Array → Select Whole Table ⇒ {=VLOOKUP(B8,B11:E14}
  4. Press F4 → Taaki Table niche khiske nahi ⇒ {=VLOOKUP(B8,$B$11:$E$14,}
  5. Column Index No. → 3(3rd Column) ⇒ {=VLOOKUP(B8,$B$11:$E$14,3}
  6. Range Lookup → Flase/0(Exact Match) ⇒ {=VLOOKUP(B8,$B11:$E$14,3,0)}
  7. Press ENTER
    • Answer toh aagya bhaiya, BikeZX200⇒Mumbai=84,150
      • Par Problem ye hai ki, Mumbai hatakar Pune likhuga toh Answer change nahi ho raha
      • Uske liye, Column Index No. Change karte 3 to 2 ⇒ {=VLOOKUP(B8,$B11:$E$14,2,0)
      • Par ye Manually hua, mujhe Automatic Answer Chaiye
  8. Formula mai jaaiye → 2 ko hatakar Match() likhiye ⇒ {=VLOOKUP(B8,$B$11:$E$14,MATCH(),0)}
  9. Lookup Value → Select Pune(Cell) ⇒ {=VLOOKUP(B8,$B$11:$E$14,MATCH(C8,),0)}
  10. Lookup Array → Select Header ⇒ {=VLOOKUP(B8,$B$11:$E$14,MATCH(C8,B11:E11),0)}
  11. Press F4 ⇒ {=VLOOKUP(B8,$B$11:$E$14,MATCH(C8,$B$11:$E$11,),0}
  12. Match Type → 0(Exact Match) ⇒ {=VLOOKUP(B8,$B$11:$E$14,MATCH(C8,$B$11:$E$11,0),0)}
  13. Press ENTER
    • Answer aagya bhaiya, Bike ZX200⇒Pune=82,500

Abb mai koi bhi Bike Type aur City likhu mujhe vo Answer apne aap dedega.

Toh dosto, Vlookup aur Match ki ye jodi, aapko 2D Lookup karne mai bohot sahita karegi.

Note: Vlookup Senior Formula hai, vo pure table ko lega aur Match Junior Formula hai, vo sirf Header ki taraf ishara karega.

Ye Formula kab istamaal karna hai:

  • Jab aapke pass 2 Variable hoge, Vertical & Horizontal aur 2 Variable ko dekhte hue aapko Table k bich(middle) mai se Answer nikalna hai.

Aapko ye Basics clear hoga Vlookup Match ka, toh mai aapko Advance Vlookup Match jarur dikhauga.

Previous Post | Next Post