#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

Case Study:

On VM Sheet:

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

On Excercise Sheet:

• Selected ID’s

Hume Sub ID’s k:

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

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↵