Need Help? Chat with us

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

[vc_row][vc_column width=”2/3″][vc_column_text][/vc_column_text][vc_btn title=”Buy Excel Course” color=”success” link=”url:http%3A%2F%2Fwww.excelsuperstar.org%2Fexcel-course-hindi-english-mix%2F|||”][vc_column_text]

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

[/vc_column_text][/vc_column][vc_column width=”1/3″][stm_sidebar sidebar=”527″][/vc_column][/vc_row]


Fatal error: Uncaught Error: Call to undefined function setPostViews() in /home/yodalear/public_html/excelsuperstar.org/wp-content/themes/consulting-PURCHASHED/single.php:10 Stack trace: #0 /home/yodalear/public_html/excelsuperstar.org/wp-includes/template-loader.php(106): include() #1 /home/yodalear/public_html/excelsuperstar.org/wp-blog-header.php(19): require_once('/home/yodalear/...') #2 /home/yodalear/public_html/excelsuperstar.org/index.php(17): require('/home/yodalear/...') #3 {main} thrown in /home/yodalear/public_html/excelsuperstar.org/wp-content/themes/consulting-PURCHASHED/single.php on line 10