Need Help? Chat with us

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




Special Offer for First Time Users

If you are the first time user and want to learn Microsoft office skills, then you can avail this special offer discount on your email.

PIvot table Webinar payment Code

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

Learn 100+ Excel Tips & Tricks

“To be an Excel Superstar”



Special Offer for First Time Users

If you are the first time user and want to learn Microsoft office skills, then you can avail this special offer discount on your email.

PIvot table Webinar payment Code

Download ebook