Navigate
X

V-LOOKUP AUR H LOOKUP

Dosto aaj iss post ki help se main aapko H Lookup aur V Lookup ke barey mein jankari doonga. H/V lookup basicaly table mein use hota hai. V Lookup means verticle lookup, yeh lookup up/down condition mein lagti hai aur H Lookup means Horizontal lookup yeh left/right condition mein lagti hai. Ab hum jante hai ki step by step yeh keise apply hota hai…




V LOOKUP


Step 1

Sabse pehle ek table banaiye school student ke marks aur grade ko show karne ke liye, jaisa ki neeche diya hua hai.



Step 2

Ab hamko student ke average ke hisab sey unka result dalna hai, iske liye aapko criteria set karna hoga jaise ki man lete hai. 0-35 number wale student ka result FAIL show karey, 35-50 waley ka Grade C, 50-75 ke liye Grade B, 75-100 ke liye Grade A aur 100 se above ke liye A+. iss criteria ko table ke sath set karein, same as below.



Upar table mein jo aapko yellow color ka column dikh raha hai vahi par result show hoga.


Step 3

Ab hamey student naam ke aagey “I “column mein V Lookup ki command lagani hai. jisse ki har ek student ka grade aur result pata lag jayega. jesa ki neechey dekh sakte hai.


Formula iss tarah se hoga.
=VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP])

=VELOOKUP( 
Formula start karne ke liye.

LOOKUP_VALUE
Yaha par average wali cell ko dalna hai.

TABLE_ARRAY
Yaha par jo humne criteria set kiya hai usska cell address dalne hai, jaisa (K2 se lekar L6).

COL_INDEX_NUM
Yaha pe hamey 2 dalna hai kyuki humarey criteria ke hisab se 1 index mein number range hai jaisa image me dikh raha hai, 35,50,75 aur 100, inn number range ke hisab se unke aage jo agli cell me jo grade diye hai unko show karna hai, jo ki criteria ke 2nd number par hai isliye 2 dalna hai.

[RANGE_LOOKUP]

Yaha pe 1 dalna hai kyuki 1 approximately (lagbhag) match ke liye hota hai aur 0 exact (bilkul) match ke liye, aur jo jo student ki average hogi vo criteria se upar neeche ho sakti hai.

Last mein brecket ko band kar dent hai, aur sath hi sath TABLE_ARRAY mein dollar sign bhi jodna hai. jaise formula neeche diya hua hai.

=VLOOKUP(H3,$K$2:$L$6,2,1)

Ab upar diya hua formula sab naam ke aagey average waley column mei copy karke paste kani hai. result taiyar jaisa ki aap neechey image mein dekh sakte hai.




H LOOKUP

H lookup mei bhi aapko same V Lookup ki tarah hi step follow karna hai bus isme jo formula hai vo horizontly dalna hai. Isme student name ko left to right dalna hai aur subjects ko up to down dalna hai iske baad jo formula hai voh neechey ki orr se lagega. Jaise ki neeche image mei diya gaya hai.

Step 1

Table ko Horizontaly banana hai.



Step 2

Table array ko bhi horizontaly banana hai.



Step 3

Neeche result bhi horizontal view me show hoga.



H Lookup mein table ke sath sath table arrey ko bhi horizontaly dalna hota hai bus issi difference ke alawa sab kuch V Lookup ki tarah karna hai.


Dosto iss post mein agar kuch samajh mein nahi aaya ho to aap mujhe comment karke bhi bata sakte hai.aur. aagey ki jaankari mein agli post mein batauga.

Ramesh Singh: Hello Dosto ! Mera Naam Ramesh Singh Hai. Main Ek Passionate Blogger Hun. Ye Website Maine Banai Hai. Is Website Par Main Longo Ko Hindi Me Jankari Deta Hun Aur Unki Help Karta Hun. Read More

View Comments (6)

  • Sir H look up ,V look up & Pirat Table ka farmoula send kar dejye plz Sir...

    • iska formula aap upar post me dekh sakte hai, aur pivot table function aapko insert tab ke under mil jayega.

  • Hello sir mera nam sumit hai sir jise home menu ke sare tools ki jankar aapne diya hai thik usi trha mujhe insert menu ki jankari chai so plese sir jald se jald aap ye post kare mujhe jwruri hai

  • Thanku sir jitna jaldi hota hai send kigye plese lakIn sir mujhe notice kaise hoga ki aap ne write kare post kar diya hai

Leave a Comment