# #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
• Grade
• 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↵

#### Waah bhaiya! Answer aagya ID→4085=Name→Adams,JeniferM

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

Excel Course in Hindi [Online Access]
INR 799
Actual Price INR 3̶0̶0̶0̶ / Watch & Learn Excel through videos on TV or Computer, Mobile
90 MP4 videos (Hindi)
87 MP4 videos (English) – Bonus
Total 900+ mins of videos tutorials
Online Access
Free Training Files (For practice)
Lifetime Access