2D lookup using Vlookup and Match Functions in Excel | Example 3

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

Iss Video mai hum Vlookup aur Match k ek bohot he behtarin Application k baare mai sikhte hai:

Case Study:

  • Employees=400 Names
  • Every Employees
    • Division
    • Grade
    • Grand Total-Expenses

Abb mujhe ye dekhna hai ki, kya unhone apne Grade aur Division k mutabic, Karch jyaada kia ya kum kia hai?

Abb mujhe ye “Refrence Sheet” batayega, jaha Managment ne kaha hai ki,

Agar aapka Division→RAD & Grade→M-4 = 78000 kharch karneki ijazat(Permission) hai.

Mujhe ye dekhna hai ki, kya harr(every) Employee ne Limit mai rehkar kharcha kia hai?

Issme do(2) Logic Help karega:

  1. Konse Divion mai kaam kar raha hai?
  2. Konse Grade ka hai?

Toh chaliye dosto, shuru karte hai Vlookup:

  1. Type ⇒ {=VLOOKUP(}
  2. Lookup Value → Select HFD ⇒ {=VLOOKUP(C5,}
  3. Table Array → Go to “Reference Sheet“→Select Whole Table ⇒ {=VLOOKUP(C5,Ref!B3:I11}
  4. Press F4 → Taaki Table niche khiske nahi ⇒ {=VLOOKUP(C5,Ref!$B$3:$I$11,}
  5. Column Index No. → 8(2nd Column) ⇒ {=VLOOKUP(C5,Ref!$B$3:$I$11,8}
  6. Range Lookup → Flase/0(Exact Match) ⇒ {=VLOOKUP(C5,Ref!$B3:$I$11,8,0)}
  7. Press ENTER↵
    • Answer toh aagya bhaiya, AbduSalaam, Ismael→HFD→M-7=48000 ki Limit honi chaiye
      • Par Problem ye hai ki, M-7 hatakar M-6 likhuga toh Answer change nahi ho raha
      • Uske liye, Column Index No. Change karna hoga
      • Par ye Manually hua, mujhe Automatic Answer Chaiye
  8. Formula mai jaaiye → 8 ko hatakar Match() likhiye ⇒ {=VLOOKUP(C5,Ref!$B$3:$I$11,MATCH(),0)}
  9. Lookup Value → Select M-6 Grade(Cell) ⇒ {=VLOOKUP(C5,Ref!$B$3:$I$11,MATCH(D5,),0}
  10. Lookup Array → Go to “Reference Sheet“→Select Header ⇒ {=VLOOKUP(C5,$B$3:$I$11,MATCH(D5,Ref!B3:I3),0}
  11. Press F4 ⇒ {=VLOOKUP(C5,$B$3:$I$11,MATCH(D5,Ref!$B$3:$I$3),0}
  12. Match Type → 0(Exact Match) ⇒ {=VLOOKUP(C5,$B$3:$I$11,MATCH(D5,Ref!$B$3:$I$3,0),0)}
  13. Press ENTER↵
    • Answer mil gaya bhaiya, AbduSalaam, Ismael→HFD→M-6=51000 ki Limit honi chaiye.

Abb mai koi bhi Division aur Grade likhu mujhe vo Answer apne aap dedega.

Mai agar Double Click karuga Limit Answer k Cell par, mujhe saare Employees k Limit mil jayege.

Agar mai dekhu ki, kitne jano ne Limit Cross kia hai?

  1. Column banaye Cross ka
  2. Function likhe:
    • Select Grand Total-Expense ⇒ =F5
    • Press > ⇒ =F5>
    • Select Limit ⇒ =F5>E5
    • Press Enter↵
      • Answer=FALSE
  3. Hum Filter bhi laga sakte hai:
    • Press Ctrl+A
    • Go to Data→Filter

Aap dekh payege abb ki kisne Limit Cross ki hai aur kisne nahi:

  • True → Jisne Limit Cross kia hai
  • False → Jisne Limit Cross nahi kia hai

Toh ye tha dosto, Vlookup Match ka ek bohot he accha Application

Previous Post | Next Post