Excel mein VLOOKUP aur HLOOKUP functions ka use data ko retrieve karne ke liye hota hai. Ye dono functions aapko ek table mein se specific data ko find karne aur wo data kisi specific location pe return karne ki suvidha dete hain. In dono functions ka basic concept yeh hai ke aap ek value ko search karte hain aur us value se related information kisi doosri column ya row se retrieve karte hain.
1. VLOOKUP Function
VLOOKUP ka full form hai Vertical Lookup. Yeh function vertical columns mein search karne ke liye use hota hai.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: Yeh wo value hai jo aap search karna chahte hain.
- table_array: Yeh wo table range hai jahan aap data ko search karna chahte hain.
- col_index_num: Yeh wo column number hai (1 se start hota hai) jahan se aapko data retrieve karna hai.
- [range_lookup]: Yeh optional hai. Agar TRUE diya jaaye, toh function approximate match dhoondhega, aur agar FALSE diya jaaye, toh exact match dhoondhega.
VLOOKUP Function Ka Basic Example
Maan lijiye, aapke paas ek sales data hai jisme product IDs aur unke prices diye gaye hain. Aapko kisi product ka price search karna hai.
| Product ID | Product Name | Price |
|---|---|---|
| 101 | Laptop | 50000 |
| 102 | Mobile | 20000 |
| 103 | Tablet | 15000 |
Agar aapko product ID 102 ke liye price find karna hai, toh aap VLOOKUP function ka use karenge.
Formula:
=VLOOKUP(102, A2:C4, 3, FALSE)
- 102: Yeh wo value hai jise aap search karna chahte hain.
- A2:C4: Yeh wo range hai jahan se aapko data search karna hai.
- 3: Yeh wo column number hai jahan se price ko retrieve kiya jaa raha hai (Price 3rd column mein hai).
- FALSE: Yeh specify karta hai ke exact match chahiye.
Result:
Formula ke apply karne se 20000 (Mobile ka price) return hoga.
2. HLOOKUP Function
HLOOKUP ka full form hai Horizontal Lookup. Yeh function horizontal rows mein search karne ke liye use hota hai. Agar aapko data rows mein search karna ho, toh aap HLOOKUP function ka use karte hain.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: Yeh wo value hai jo aap search karna chahte hain.
- table_array: Yeh wo table range hai jahan aap data ko search karna chahte hain.
- row_index_num: Yeh wo row number hai jahan se aapko data retrieve karna hai (row index 1 se start hota hai).
- [range_lookup]: Yeh optional hai. Agar TRUE diya jaaye, toh function approximate match dhoondhega, aur agar FALSE diya jaaye, toh exact match dhoondhega.
HLOOKUP Function Ka Basic Example
Maan lijiye aapke paas sales data hai jisme months aur unke sales diye gaye hain. Aapko kisi specific month ka sales data retrieve karna hai.
| Month | Jan | Feb | Mar |
|---|---|---|---|
| Sales | 10000 | 15000 | 12000 |
Agar aapko month "Feb" ka sales data find karna hai, toh aap HLOOKUP function ka use karenge.
Formula:
=HLOOKUP("Feb", A1:D2, 2, FALSE)
- "Feb": Yeh wo value hai jise aap search karna chahte hain.
- A1:D2: Yeh wo range hai jahan se aapko data search karna hai.
- 2: Yeh row number hai jahan se aapko sales data retrieve karna hai (Sales 2nd row mein hai).
- FALSE: Yeh specify karta hai ke exact match chahiye.
Result:
Formula ke apply karne se 15000 (February ka sales data) return hoga.
3. VLOOKUP aur HLOOKUP Functions Mein Differences
| Feature | VLOOKUP | HLOOKUP |
|---|---|---|
| Lookup Direction | Vertical (Columns) | Horizontal (Rows) |
| Use Case | Jab aapko columns mein data search karna ho. | Jab aapko rows mein data search karna ho. |
| Lookup Value | First column mein hona chahiye. | First row mein hona chahiye. |
| Column/Row Index | Column index number specify karna hota hai. | Row index number specify karna hota hai. |
4. Approximate vs Exact Match
- Approximate Match (TRUE): Agar aap TRUE use karte hain, toh function value ko closest match ke saath return karega.
- Exact Match (FALSE): Agar aap FALSE use karte hain, toh function exact match ko dhoondhega.
Example:
Agar aapko approximate match chahiye, toh formula aise hoga:
=VLOOKUP(105, A2:C4, 3, TRUE)
Is case mein, agar 105 exact match nahi milta, toh VLOOKUP 105 ke closest match ko return karega.
5. Practical Examples
VLOOKUP Example - Salary Calculation
Maan lijiye, ek company mein employees ki ID aur unke salaries ka data hai. Aapko employee ID ke basis par unka salary find karna hai.
| Employee ID | Name | Salary |
|---|---|---|
| 101 | Ali | 50000 |
| 102 | Sara | 60000 |
| 103 | Usman | 55000 |
Agar aapko employee ID 102 ka salary find karna hai, toh formula hoga:
=VLOOKUP(102, A2:C4, 3, FALSE)
Isse 60000 (Sara ka salary) return hoga.
HLOOKUP Example - Monthly Sales
Maan lijiye aapke paas monthly sales ka data hai aur aapko kisi specific month ka sales figure retrieve karna hai.
| Month | Jan | Feb | Mar |
|---|---|---|---|
| Sales | 10000 | 15000 | 12000 |
Agar aapko February ka sales figure retrieve karna hai, toh formula hoga:
=HLOOKUP("Feb", A1:D2, 2, FALSE)
Isse 15000 return hoga.
Conclusion
VLOOKUP aur HLOOKUP functions Excel mein bohot powerful tools hain, jo aapko vertical ya horizontal tables mein se data retrieve karne mein madad karte hain. VLOOKUP ka use vertical search ke liye hota hai, jabki HLOOKUP ka use horizontal search ke liye. In dono functions ka use aap data analysis, reporting, aur decision-making tasks mein efficiently kar sakte hain.