#1 Trick for Excel Dashboards: 2D lookup using Vlookup and Match functions in Excel

Learn: #1 Trick for Excel Dashboards-2D lookup using Vlookup and Match functions in Excel

Chaliye dosto dekhte hai, Iss naye Problem mai hum Vlookup aur Match ko kiss tarah hum istamaal karke humara kaam aasan karte hai:

Case Study:

On VM Sheet:

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

On Excercise Sheet:

  • Selected ID’s

Hume Sub ID’s k:

  • Name
  • Division
  • Grand Total – Expense
    Nikalne hai.

Challenge: Sirf 1 Formula Likhna hai Yellow waale Cell mai, jise hum ek sath Pure Table pe Drag karege, taki Answer apne aap aajaye.

Toh chaliye dosto, dekhte hai kaise nikala jaaye:

Hum Decide karte hai ki konsa Excel Function yaha Istamaal hoone wala hai:

Vlookup:

  • Name mai → Sirf Name aaye
  • Division mai → Sirf Division aaye
  • Grand Total-Expense  mai → Sirf Grand Total-Expense aaye

Aur Match bhi:

  • Konse Column se Answer nikalna hai vo bhi dekhna hoga:
    • Column 2 se Answer nikalake Name mai rakha jaye
    • Column 3 se Answer nikalke Division mai rakha jaaye
    • Column 6 se Answer nikalke Grand Total-Expense mai rakha jaye

Refer:Vlookup Videos

Ye thoda Advance Function hai, dekhte hai kaise Istamaal karna hai:

  1. Type⇒{=VLOOKUP(}
  2. Lookup Value→Select 4805(ID)⇒{=VLOOKUP(A4}
    1. “$” lagaye “A4” k pehle taaki hum Copy+Paste Right ki taraf kare toh “A” he rahe “B” na bane {=VLOOKUP($A4,}
      1. “4” k pehle “$” isiliye nahi lagaya, qki Formula ko Niche ki taraf khichege toh vo, 4→5→6→7 hota nazar aana chaiye
  3. Table Array→Go to VM Sheet→Select “ID” and Press shift+ctrl+right & shift+crtl+down(Whole Table Selected)⇒{=VLOOKUP($A4,VM!A4:Q421}
    1. Press “F4”→Taki Table Niche khiske nahi⇒{=VLOOKUP($A4,VM!$A$4:$Q$421.}
  4. Column Index No.→Type MATCH()⇒{=VLOOKUP($A4,VM!$A$4:$Q$421,MATCH(),}
  5. Rangle Lookup→FALSE/0(Exact Match)⇒{=VLOOKUP($A4,VM!$A$4:$Q$421,MATCH(),0)}
    1. Agar aap ENTER↵ Press karoge toh:
      1. Excel kahega ki, aapke Formula mai kuch Missing hai usse Complete kijiye
      2. Press OK
  6. Lookup Value→Select “Name” in Excercise Sheet⇒{=VLOOKUP($A4,VM!$A$4:$Q$421,MATCH(B3),0)}
    1. Press 2 Times “F4”⇒{=VLOOKUP($A4,VM!$A$4:$Q$421,MATCH(B$3,),0)}
  7. Lookup Array→Select Header of Table from VM Sheet⇒{=VLOOKUP($A4,VM!$A$4:$Q$421,MATCH(B$3,VM!A4:Q4),0)}
    1. Press “F4”→{=VLOOKUP($A4,VM!$A$4:$Q$421,MATCH(B$3,VM!$A$4:$Q$4),0)}
  8. Matchup Type→Flase/0(Exact Match)⇒{=VLOOKUP($A4,VM!$A$4:$Q$421,MATCH(B$3,VM!$A$4:$Q$4,0),0)}
  9. Press ENTER↵

Waah bhaiya! Answer aagya ID→4085=Name→Adams,Jenifer M

Challenge Completed!

Abb maja aayega aapko dosto:

  • Formula ko Drag karege Niche aur Fir Right ki TarafSubke Answers apne aap ajayege.
  • Agar aap Change karoge “Division” ko “Grade” mai toh unke bhi Answers apne aap aajayege.

Toh Dosto, ye tha humara Vlookup aur Match ka Advance Combination

Previous Post