2D lookup using Vlookup and Match Function in Excel | Example 1

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

Iss Video mai hum sikhege, kiss tarah Vlookup aur Match Function ko jodk ek Combo Function banaya jaaye.

Jo aapke liye Perform kare 2D Lookup, aur uska matlab aapko batata hu dosto:

Aapke pass ek Table hai, uss Table mai Vertically(Uppar⇑ se Niche⇓), ek Series of Topic diye gye hai, unke alag alag Sizes aur Prices bhi diye gye hai.

Agar koi User alag alag Pizza Type likhta hai, aur alag alag Size likhta hai, toh Price apne aap mil jana chaaiye:

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

  1. Type ⇒ {=VLOOKUP(}
  2. Lookup Value → Select Farm Fresh ⇒ {=VLOOKUP(G6,}
  3. Table Array → Select Whole Table ⇒ {=VLOOKUP(G6,B9:E12}
  4. Press F4 → Taaki Table niche khiske nahi ⇒ {=VLOOKUP(G6,$B$9:$E$12,}
  5. Column Index No. → 3(3rd Column) ⇒ {=VLOOKUP(G6,$B$9:$E$12,3}
  6. Range Lookup → Flase/0(Exact Match) ⇒ {=VLOOKUP(G6,$B9:$E$12,3,0)}
  7. Press ENTER↵
    • Answer toh aagya bhaiya, Farm Fresh⇒Medium=455
      • Par agar mai Size Change karu Medium to Small, toh Answer vahi hoga Price ka.
      • Uske liye, Column Index No. Change karte 3 to 2 ⇒ {=VLOOKUP(G6,$B9:$E$12,2,0)
      • Answer toh aagya Farm Fresh⇒Small=285
      • Par ye Manually hua, mujhe Automatic Answer Chaiye
  8. Formula mai jaaiye → 2 ko hatakar Match() likhiye ⇒ {=VLOOKUP(G6,$B$9:$E$12,MATCH(),0)}
  9. Lookup Value → Select Large(Cell) ⇒ {=VLOOKUP(G6,$B$9:$E$12,MATCH(H6,),0}
  10. Lookup Array → Select Header ⇒ {=VLOOKUP(G6,$B$9:$E$12,MATCH(H6,B9:E9),0}
  11. Press F4 ⇒ {=VLOOKUP(G6,$B$9:$E$12,MATCH(H6,$B$9:$E$9,),0}
  12. Match Type → 0(Exact Match) ⇒ {=VLOOKUP(G6,$B$9:$E$12,MATCH(H6,$B$9:$E$9,0),0}
  13. Press ENTER↵
    • Answer aagya bhaiya, Farm Fresh⇒Large=625

Abb mai koi bhi Pizza Type aur Size 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.

Next Post