VLOOKUP function ek bahut powerful formula hai jo Microsoft Excel me available hai. Is formula se hum text, numbers ya cell reference ke hisab se search kar sakte hain. VLOOKUP formula ke use se, hum ek table ya range me search kar sakte hain aur ek specific value ke corresponding dusre cell ki value retrieve kar sakte hain.
Excel VLOOKUP function ki madad se kisi bhi table ke columns se value search kiya ja sakta hai. Jaisa ki naam se pata chalta hai VLOOKUP function me “V” ka matlab hai “vertical” hai, matlab ki yah vertical search karta hai. Ye function horizontally search ke liye HLOOKUP bhi available hai.
VLOOKUP function ka format ekdam simple hai. Is function ka format is tarah hai:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Yaha par har argument ke bare me detail me jaan lete hain:
1) lookup_value: Yah wo value hai jisko search kiya jana hai. Is value ko wo column me search kiya jata hai, jo ki table_array ke first column ke barabar hota hai.
2) table_array: Yah wo table hai jisme lookup_value search kiya jana hai.
3) col_index_num: Yah wo column hai jisme se aap value retrieve karna chahte hain. Is column ko table_array se count kiya jata hai.
4) [range_lookup]: Yah optional argument hai. Yah TRUE ya FALSE ho sakta hai. True matlab ki function next smaller value ko search karega agar search value se choti hai. False matlab ki function exact match ke liye search karega. Default value TRUE hota hai.
Ab aage hum kuch examples ki madad se is function ke use ki understanding karenge:
Simple VLOOKUP Example
Agar humare pass ek table hai jisme “Product ID”, “Product Name” aur “Price” columns hain, aur hume Product ID ke hisab se Price retrieve karna hai. To hum VLOOKUP function ka use kar sakte hain. Jaisa ki niche diya gaya hai:
=VLOOKUP(D2, A2:C8, 3, FALSE)
Yaha par lookup_value cell D2 hai jisko search karna hai. table_array A2:C8 hai jisme lookup_value search kiya jayega. col_index_num 3 hai, matlab Price column hai, jo ki retrieve kiya jayega. Last argument, [range_lookup], FALSE hai, matlab ki function exact match ke liye search karega.
VLOOKUP Example with Multiple Criteria
Hum VLOOKUP function ka use multiple criteria ke liye bhi kar sakte hain. Agar humare pass ek table hai jisme “Product ID”, “Product Name”, “Color” aur “Price” columns hain, aur hume Product ID aur Color ke hisab se Price retrieve karna hai. To hum kuch is tarah ka formula ka use kar sakte hain:
=VLOOKUP(D2&E2, A2:D8, 4, FALSE)
Yaha par lookup_value D2&E2 hai jisko search karna hai. Agar aap carefully dekhe to humne lookup_value ke liye D2 aur E2 cell ko concatenate kiya hai. table_array A2:D8 hai jisme lookup_value search kiya jayega. col_index_num 4 hai, matlab Price column hai, jo ki retrieve kiya jayega. Last argument, [range_lookup], FALSE hai