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

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

### Case Study:

• Employees=400 Names
• Every Employees
• Division
• 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,

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?

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