MS Excel Formulas In Hindi – Advance Excel Tutorial Part 2

ms-excel-formulas-hindi-advance-ms-excel-course-free-part-1

Advanced Excel formulas with examples in Hindi sikhe

Yeh advance MS Excel tutorial in Hindi mein hum aapko MS Excel ke advance formulas ke baare mein examples ke saath batayenge jisse aap MS Excel mein expert ban jaoge.

Yeh Advance MS Excel tutorial baaki excel courses se behtar hai kyonki:

  • Yeh hindi excel tutorial 100% FREE hai
  • Iss course mein humne aapko simple examples se excel ke baare mein bataya hai

Yeh tutorial Hindi MS Excel full course in Hindi ka 4th part hai. Agar aap chahte hai to aap pichle bhaag bhi kar sakte hai.

Niche diye gaye links par click karke aap uss tutorial pe ja sakte hai –

MS Excel in Hindi full tutorial Beginner course part 1 – Agar excel bilkul nahi aati

MS Excel in Hindi full tutorial Beginner course part 2 – MS Excel ko use karke data analysis kaise karte hai (Sort aur Filter kaise kare)

MS Excel in Hindi full tutorial Advance course part 1 – MS Excel formulas and Functions simple wale


Chaliye dekhetein ke intermediate mein aap konse formulas sikhenge.

Intermediate level formulas aur functions

Intermediate level mein hum niche diye gaye formulas aur functions apply karna sikhenge –

Logical functions:

IF- Tests the validity of a logical expression and returns the result “TRUE” if true and “FALSE” if false

AND- Returns TRUE if all of the arguments evaluate to TRUE.

OR- Returns TRUE if any argument evaluates to TRUE.

Chaliye ab dekhte hain inn functions ko ek example se.

Main yahan class record ki report ka ek simple example liya hai.

Ye mera data hai-

3. LOGICAL FUNCTIONS:

Aur isme humara aim hai

a. PASS in English

IF function

b. PASS in English and Maths

AND function

c. PASS in PCME

Nested IF with AND statements

d. PASS in Optional / Additional

OR function

e.  Grading

IF(multiple)

f. PASS/FAIL in All

Nested IF with AND, OR statements

NOTE: Koi bhi function lagaane se pehle aap apni cell range dhyan se check karle aur accordingly lagayein kyonki meri aur aapki cell range different ho sakti hain.

a. PASS in English

IF function

If function ka use hum logical test karne ke liye karte hai aur ismein agar humari inserted logical condition true hai toh humein true ki value milti hai aur agar false hota hai toh false ki value milti hai.

Yahan par agar humein pata lagana hai ki koi English mein pass hai ya nahi toh hum IF function ka use kar sakte hain. Yahan par humne kisi ke English mein PASS hone ki condition marks in English >= 34 li hai.

Chaliye ab dekhein ki ye function kaise lagatein hai-

Sabse pehle cell mein likhe-

=IF(

Uske baad IF function mein aap apna logical test ya aapki condition for passing in English enter kare aise-

=IF(D11>=34

Iske baad ek comma lagakar aap apne logical test ki value jo aap chahtein ki aapko mile jab test true hai inverted commas mein enter kare aise-

=IF(D11>=34,”PASS”

Ab comma lagakar vovaluelikhein jo aap chahtein hain aapko mile jab logical test ki value false hai aise-

=IF(D11>=34,”PASS”,”FAIL”)


Press ENTER.

Iss cell ko agar aap niche drag karenge toh ye IF logical test baaki cells ke liye bhi apply hojayega aise-

b. PASS in English and Maths

AND function

AND function ka use hum ek se jyada arguments ko ek sath test karne ke liye karte hain.

Iss function mein jo bhi tests hum insert karte hain agar vo sab true hain toh humein ye function lagane par aapko apne cell me “TRUE” milega aur agar inn tests mein se ek bhi false hai to humein cell mein “FALSE” milega.

Ab imagine kare ki humein iss example mein pata lagana hai ki konse students English aur Hindi dono mein pass hain. Iske liye hum AND function ka use kar sakte hain.

Chaliye ab dekhein ki ye function kaise lagta hai-

Sabse pehle cell mein likhe-

=AND(

Ab ismein apna pehla logical test likhein. Yahan mera pehla logical test ki kya student English mein pass hai, uske liye humari condition hai marks in English > =34. Iss test ko likhne ke liye aap vo cell select kare jismein aapke English ke marks hain ya phir vo cell no. type kare jismein aapke English ke marks hain. Uske baad >=34 likhe dein aise-

=AND(D11>=34

Iske baad comma lagayein aur apna second logical test likhein. Yahan humara second logical test hai marks in maths >= 34. Isse aise likhein-

=AND(D11>=34,E11>=34

Ab bracket close kare. Aapka pura function kuch aisa hoga-

=AND(D11>=34,E11>=34)

Press ENTER.

Aapko yahan “TRUE“ ya “FALSE” milega. Agar “TRUE” hai toh iska matlab aapke AND function ke dono logical test true hain aur agar “FALSE” toh matlab kam se kam ek condition false hai.

NOTE: Humne yahan sirf do logical test/conditions enter ki hain but aap comma lagakar apne according aur bhi insert kar sakte hain aise-

=AND(D11>=34,E11>=34,F11>=34)

Ab iske according TRUE ya FALSE milega aapko.

Ab aap isse niche drag karke baaki students ke liye bhi iss function ko apply kar sakte hain.

c. PASS in PCME

Nested IF with AND function

Nested IF function hum tab use karte hain jab humein ek se jyada conditions ya logical tests IF function mein add karne hote hain.

Yahan humein jo PCME-Physics, Chemistry, Maths, English inn sab mein pass hain unka pata karna hai isliye hum yahan IF function mein AND function lagayenge taaki hum ek se jyada logical tests ke liye TRUE/FALSE laga sake.

Isse hum aise lagayenge-

=IF(AND(

Ab ismein apna pehla logical test insert kare jo hai English marks>=34 aise-

=IF(AND(D11>=34

Ab ismein comma lagakar apne baaki logical tests insert kartein rahe aur fir AND statement ke liye bracket close karde aise-

=IF(AND(D11>=34,E11>=34, F11>=34, G11>=34)

Ab ismein comma lagakar aap vo value jo apke logical tests ke according true value ke liye ho vo inverted commas (here “PASS”)  mein likhdein aise-

=IF(AND(D11>=34,E11>=34, F11>=34, G11>=34),”PASS”

Ab comma lagakar jo value logical tests ke liye false mein ho vo inverted commas (“FAIL”) mein likhdein aur IF function ke liye bracket close karde aise-

=IF(AND(D11>=34,E11>=34, F11>=34, G11>=34),”PASS”,”FAIL”)

Press ENTER.

Iss cell ko niche drag karke baaki cells mein yahi function corresponding cells mein apply karle –

d. PASS in Optional / Additional

OR function

OR function ka use hum ek se jyada arguments ko ek sath test karne ke liye karte hain.

Iss function mein jo bhi tests hum insert karte hain agar vo unme se ek bhi true hain toh humein ye function lagane par apne cell me “TRUE” milega aur agar inn tests mein sabhi false hai to humein cell mein “FALSE” milega.

Ab imagine kare ki humein iss example mein pata lagana hai ki konse students optional ya additional subject mein se kisi bhi ek mein pass hain. Iske liye hum OR function ka use kar sakte hain.

Chaliye ab dekhein ki ye function kaise lagta hai-

Sabse pehle cell mein likhe-

=OR(

Ab ismein apna pehla logical test likhein. Yahan mera pehla logical test ki kya student optional subject mein pass hai ya nahi, uske liye humari condition hai marks in optional >=34. Iss test ko aap aise likhe-

=OR(H11>=34

Iske baad comma lagayein aur apna second logical test likhein. Yahan humara second logical test hai marks in additional >= 34 aur phir OR function ke liye apne bracket close kare. Isse aise likhein-

=OR(D11>=34,E11>=34)

Press ENTER.

Iss cell ko niche drag kare aur baaki cells mein apply kare-

e. Grading

IF(multiple)

IF function mein kabhi kabhi humein true ya false ke liye bhi IF function lagana hota hain. Jaise agar kisi student ke marks >=85 hai toh uska grade hai A, agar marks >= 75 hai but < 85 hai toh uska grade B hoga. Aisi conditions jismein aapke logical tests ke bhi true/false ke logical tests ho hum vahan IF multiples times use karte hain.

Ise yahan hum grade karne ke liye use karegenge. Sabse pehle likhe-

=IF(

Ab ismein apna pehla logical test insert kare aur phir comma lagakar inverted commas (here “A”)  mein apne logical tests ki true ke liye value likhe aise-

=IF(D11>=85,”A”

Ab kyonki aapko false ki value mein ek logical test likhna hai isliye ab aap ek comma lagakar dusra IF function lagaye aise-

=IF(D11>=85,”A”,IF(


Ab ismein apne pehle IF function ki false value ke liye jo logical test lagana hai vo lagaye aur phir comma lagakar uski true value inverted commas (here “B”) lagaye aise-

=IF(D11>=85,”A”,IF(D11>=75,”B”

Ab aise hi aap comma lagakar second IF function ke liye jo bhi false value mein aap third IF function se logical test lagana chahte hai vo lagadein aise-

Ab ismein apna logical test enter kare-

=IF(D11>=85,”A”, IF(D11>=75,”B”, IF(D11>=65,”C”

Ab saare required logical tests insert karne ke baad aap sabhi IF statements ko close karne ke liye multiple brackets lagade aise-

Iske baad ENTER press kare.

Isse niche drag kare baaki cells mein yahi function apply karne ke liye.

f. PASS/FAIL

Nested IF with AND, OR statements

Nested IF function hum tab use karte hain jab humein ek se jyada conditions ya logical tests IF function mein add karne hote hain.

Yahan humein jo multiple conditions lagani hai vo hai AND function mein PCME mein pass aur Optional ya additional mein OR function kisi bhi ek mein pass. Agar koi student PCME mein pass hai aur optional ya additional mein se ek mein pass hai toh uske liye true value hum lenge “PASS” aur agar inme se ek bhi condition se false hai toh “FALSE” milega.

Isse hum aise lagayenge-

=IF(AND(

Ab apna pehla logical test likhein-

=IF(AND(D11>=34,

Aise hi aap pehle apne AND function mein PCME mein pass hone ki conditions lagayein-

=IF(AND(D11>=34,E11>=34,F11>=34,G11>=34

Ab ismein AND function mein hi OR function lagayein optional ya additional subject mein pass hone ke liye aise-

=IF(AND(D11>=34,E11>=34,F11>=34,G11>=34,OR(

Phir OR ke logical tests likhein aur ek bracket lagayiye OR statement ko close karne ke liye aur phir second bracket AND statement ko close ko karne ke liye aur phir ek comma lagadein aise-

=IF(AND(D11>=34,E11>=34,F11>=34,G11>=34 ,OR(H11>=34,J11>=34)),

Ab aap jo bhi apne logical tests ke liye true ki value chahte hain vo inverted commas (here “PASS”) likhdein aise-

=IF(AND(D11>=34 ,E11>=34,F11>=34,G11>=34 ,OR(H11>=34,J11>=34)),”PASS”

Ab comma lagar apni false ki value inverted commas mein likhdein aur phir IF function ke liye bracket close kardein aise-

=IF(AND(D11>=34 ,E11>=34,F11>=34,G11>=34 ,OR(H11>=34,J11>=34)),”PASS”,”FAIL”)

Ye aapka complete function hoga-

Press ENTER.

Baaki students ke liye ye function apply karne ke liye iss cell ko select kare aur niche drag kare-



NOTE: Agar aap chahein toh ye raw data aur final excel data sheet download kar sakte hain aur check kar sakte hain apne answers.
Sheets ka link iss article mein sabse last mein diye hue hain.


Chaliye ab dekhte hain ki aap advance level formulas mein kya sikhenge.

Advanced level formulas aur functions

Advanced level mein hum niche diye gaye formulas aur functions apply karna sikhenge –

4. Lookup functions:

TRANSPOSE Returns the transpose of an array

HYPERLINK- Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet

INDEX- Uses an index to choose a value from a reference or array

MATCH- Looks up values in a reference or array

HLOOKUP- Looks in the top row of an array and returns the value of the indicated cell

VLOOKUP- Looks in the first column of an array and moves across the row to return the value of a cell

LOOKUP- Looks up values in a vector or array

Chaliye ab dekhein ye saare functions ek ek example se-

a. TRANSPOSE

Transpose function hum apne data ko transpose karne ke liye karte hain. Transpose karne ka matlab rows ke data ko columns mein convert karna ya columns ke data ko rows mein karna.

Jaise abhi yeh niche diye hue data mein humare “SUBJECT” and “MARKS”  two horizontal rows mein hain.

SELECTION OF COLUMNS

Ab pehle cells select karle jismein aapka transposed data hoga.

NOTE: Ismein apne data ke array ko dhyan mein rakhkar ye cells select kariyega. Inn selected cells mein utni rows honi chahiye jitni aapke data mein columns hai aur utni columns honi chahiye jitni aapke data mein rows hai jaise ki mere data mein 6 columns  aur 2 rows hai toh mein jo cells select karungi usmein 6 rows and 2 columns hone chahiye aur iske baad hum transpose function lagayenge aise –

=TRANSPOSE(

ARRAY

Ab hum ismein apne data (jo aapko transpose karna hai) array  ko select karenge aise-

=TRANSPOSE(B3:G4)

Ab ye function likhne ke baad press CTRL+SHIFT+ENTER kyonki ye array formula hai.

Aur aapko aise apna data transposed miljayega-

b. HYPERLINK-

Hyperlink function apne excel sheet mein links insert karne ke liye karte hain. Jaise kabhi kabhi hum apna data ke sath external links insert karne hote hain toh hum iska use kar sakte hain.

Isse lagane ke liye sabse pehle hum likhenge-

=HYPERLINK(

LINK LOCATION

Ab jaise agar hum ye JOSH TALKS ka link insert karna hai toh hum pehle iske link ko copy karenge.

Aur ab iss link ko inverted commas mein paste kardenge aise-

=HYPERLINK(“Paste link here”

Ab agar humein apne cell mein iss site ke link ka name link location hi rakhna hai toh hum bracket close karke ENTER press kardenge aur humara link insert hojayega.

FRIENDLY NAME

Par agar hume iss link ko iss cell mein kisi name se represent karna ha toh hum link location inverted commas mein likhne ke baad comma lagayenge aur phir jo name hume iss cell mein show karne hai with this ink location vo ab inverted commas mein likhdein aur bracket close karde-

=HYPERLINK(“pasted link”, ”JOSH KOSH-MS Excel Kaise Seekhe”)

Mera complete function ye hai-

=HYPERLINK(“https://www.joshtalks.com/joshkosh/skill-develop-karein/microsoft-excel-kaise-seekhein-in-hindi/?preview=true”, “JOSH KOSH- MS Excel Kaise Seekhe”)

Ab ENTER press karde. Aapko ye miljayega-

Ab aap jab bhi iss link par click karenge aap apni link location par chale jayenge.

Tip: Aap link directly bhi add kar sakte hain. Ye aap Insert mein jakar Links se kar saktein hain.

c. INDEX-

INDEX function se hum kisi cell ki position se usmein kya hai vo pata karte hain.

Jaise ki agar humara data ye hai-

Ab agar humein Eraser ka Count pata karna hai toh aur humein pata hai Eraser row 5 mein hai aur Count column 4 mein toh hum iss information ka use karke pata laga saktein hai Eraser ka Count.

Sabse pehle hum likhenge-

=INDEX(

Array

Ab hum array select karenge. Yahan mein poora data select kar liya hai –

=INDEX(A2:D7

Row num

Ab hum row no. jismein Eraser hai vo mention karenge jaise yahan Eraser row 5 mein hai toh hum row_num mein 5 likh denge-

=INDEX(A2:D7,5

Column num

Next hum column_num mein Count ka column no. likdenge. Yahan Count column 4 mein hai.

=INDEX(A2:D7,5,4

Ab bracket close karke ENTER press karde.

Aapko Eraser ka Count miljayega.

Aise hi aap Gel Pen ka Count bhi pata kar sakte hain. Bas apni row_num aur column_num accordingly likhdein-

=INDEX(A2:D7,3,4)

Ab yahi function kisi Stationery ki Price nikalne ke liye bhi kar saktein hai jaise maine Pencil ke liye kiya hai-

=INDEX(A2:D7,4,3)

d. MATCH

MATCH function INDEX function ke opposite kaam karte hai. Isse hum kisi cell ki value use karke ek specified range of cells mein uski relative range bata deta hai.

Ab jaise mujhe Pencil ki relative range pata karni thi Stationery ki cell ki column range mein.

Isse lagane ke liye sabse pehle likhe –

=MATCH(

Lookup value

Aur phir aap lookup_value mein vo value likh dein jiski range aap pata lagana chahte hai.Ismein aap chahein toh Pencil inverted commas (like “Pencil”) mein likhdein ya phir jiss cell mein aapne apne reference ke liye Pencil likha hai vo select karle aise-

=MATCH(“Pencil”,

OR

=MATCH(G3,

Lookup array

Ab apna lookup_array likhein. Lookup array vo array hota hai jismein aapki lookup value hoti hai. Ye humari specified range hoti hai jisske reference se humari lookup value ki range calculate hoti hai. Maine yahan apni lookup_array li hai Stationery items excluding the word “Stationery”.

=MATCH(“Pencil”,B3:B7

OR

=MATCH(G3,B3:B7

MATCH TYPE

Ab jaise [match_type] chahiye vo yahan select karle aise. Maine Exact match choose kiya kiske liye hum 0 likhkar directly bhi kar sakte hain-

=MATCH( “Pencil” ,B3:B7,0

OR

=MATCH(G3,B3:B7,0

Ab bracket close karke ENTER press kare.

=MATCH( “Pencil” ,B3:B7,0)

OR

=MATCH(G3,B3:B7,0)

Ab aapko apni range miljayegi aise-

e. VLOOKUP

Vlookup mein ‘V’ hai ‘Vertical’ ke liye. Ye function jab humein top to bottom ya vertically koi values find karni hoti hai tab use karte hain. Ye function corporate world mein bahut useful hota hai. Vlookup ka syntax hota hai-

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Maine ye data liya hai-

Ab jaise humein iss top to bottom data mein pata lagana ki Jupiter ka Diameter(km)  kya hai toh hum iss data ke sath Vlookup se pata laga sakte hain.

Isse lagane ke liye sabse pehle hum likhenge-

=VLOOKUP(

Lookup value

Ab aap lookup_value matlab jiss value ke liye aap Diameter pata karna chahte hain vo inverted commas (like “Jupiter”) mein likhdein ya agar aapne apne reference ke liye Jupiter kisi cell mein likha hai jaise maine vo cell no. select ya enter karde aise-

=VLOOKUP(“Jupiter”

OR    

=VLOOKUP(G5

Table array

Ab aap apna table_array matlab jo aapki data ki tab hai vo select karle jaise maine Planet aur Diameter ki rows select ki hain.

=VLOOKUP( “Jupiter” ,B3:C10

OR

=VLOOKUP(G5,B3:C10

Column index num

Next aap apni table array ke according col_index_num likhdein jismein se aap apni lookup value ka data lene chahtein hai jaise mujhe Diameter chahiye tha isliye mere table array ke according col_index_num mein maine 2 likha-

=VLOOKUP( “Jupiter” ,B3:C10,2

OR

=VLOOKUP(G5,B3:C10,2

Range lookup

Aur phir [range_lookup] likhein. Ismein aapko apna match type likhna hai. False hai Exact match ke liye aur True hai approximate match ke liye. Maine yahan False liya hai.

=VLOOKUP( “Jupiter” ,B3:C10,2,FALSE)

OR

=VLOOKUP(G5,B3:C10,2,FALSE)

 Press ENTER. Dekhiye aapko aap result milgaya-

Aise hi aap ye Earth ke liye bhi laga sakte hain-

=VLOOKUP(“Earth”,B3:C10,2,FALSE)

OR

=VLOOKUP(G6,B3:C10,2,FALSE)

Press ENTER.

Aap iss function No. of Moons nikalne ke liye bhi use kar sakte hain. Iske liye aap apna table array aise select kariyega aur ye function lagayiyega-

=VLOOKUP(“Jupiter”,B3:D10,3,FALSE)

OR

=VLOOKUP(J5,B3:D10,3,FALSE)

Press ENTER.

Ye dekhiye Earth ke No. of moons nikalne ke liye-

=VLOOKUP(“Earth”,B3:D10,3,FALSE)

OR

=VLOOKUP(J6,B3:D10,3,FALSE)

Press ENTER.

e. HLOOKUP-

Hlookup mein ‘H’ hai ‘Horizontal’ ke liye. Ye function jab humein left to right ya horizontally koi values find karni hoti hai tab use karte hain. Hlookup ka syntax hota hai-

HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])

Maine ye data liya hai-

Ab jaise humein iss left to right data mein pata lagana ki Jupiter ka Diameter(km)  kya hai toh hum iss data ke sath Hlookup se pata laga sakte hain.

Isse lagane ke liye sabse pehle hum likhenge-

=HLOOKUP(

Lookup value

Ab aap lookup_value matlab jiss value ke liye aap Diameter pata karna chahte hain vo inverted commas (like “Jupiter”) mein likhdein ya agar aapne apne reference ke liye Jupiter kisi cell mein likha hai jaise maine vo cell no. select ya enter karde aise-

=HLOOKUP(“Jupiter”

OR    

=HLOOKUP(B10

Table array

Ab aap apna table_array matlab jo aapki data ki tab hai vo select karle jaise maine Planet,Diameter aur No. of Moons ki rows select ki hain.

=HLOOKUP( “Jupiter” ,B3:J5

OR

=HLOOKUP(B10,B3:J5

Row index num

Next aap apni table array ke according row_index_num likhdein jismein se aap apni lookup value ka data lene chahtein hai jaise mujhe Diameter chahiye tha isliye mere table array ke according row_index_num mein maine 2 likha-

=HLOOKUP( “Jupiter” ,B3:J5,2

OR

=HLOOKUP(B10,B3:J5,2

Range lookup

Aur phir [range_lookup] likhein. Ismein aapko apna match type likhna hai. False hai Exact match ke liye aur True hai approximate match ke liye. Maine yahan False liya hai.

=HLOOKUP( “Jupiter” ,B3:J5,2,FALSE)

OR

=HLOOKUP(B10,B3:J5,2,FALSE)

Press ENTER. Aapko apna  diameter miljayega-

Aise hi aap Uranus ke Diameter ke liye kar sakte hain-

=HLOOKUP( “Jupiter” ,B3:J5,2,FALSE)

OR

=HLOOKUP(B11,B3:J5,2,FALSE)

Press ENTER.

f. LOOKUP-

Lookup formula hum data ko horizontal aur vertical dono tarah se values find karne ke liye use karte hai. Lookup ke do syntax hote hain-

VECTOR FORM  —–      LOOKUP(lookup_value, lookup_vector, [result_vector])

ARRAY FORM     —–      LOOKUP(lookup_value, array)

Ye humara data hai-

Chaliye pehle Vector form dekhte hain-

Lookup value

Yahan hum Ishita ke Chemistry ke marks dekhna chahte hain. Toh sabse pehle aap apni lookup_value enter kare. Isse aap select karke ya inverted commas (here “Ishita”) mein  likhdein-

=LOOKUP(“Ishita“

OR

=LOOKUP(L4

Lookup vector

Ab apna lookup_vector matlab vo column jismein aapki lookup value hai vo select kare. Yahan hai Name column excluding “Name”.

=LOOKUP( “Ishita“ ,B4:B13

OR

=LOOKUP(L4,B4:B13

Result vector

Ab aap apna result_vector matlab jo column se aapka result milega vo vector select karle aise-

=LOOKUP( “Ishita“,B4:B13,F4:F13

OR

=LOOKUP(L4,B4:B13,F4:F13

Ye aapka pura function hoga

=LOOKUP( “Ishita“,B4:B13,F4:F13)

OR

=LOOKUP(L4,B4:B13,F4:F13)

Ab ENTER press karde.

Aapko apni required value miljayegi-

Ek example ye aur dekhein Arshad ke Chemistry marks ka.

=LOOKUP( “Arshad“ ,B4:B13,F4:F13)

OR

=LOOKUP(L5,B4:B13,F4:F13)

Agar Krishna ke Maths ke marks pata karna hai toh ye lagayein-

=LOOKUP( “Krishna“ ,B4:B13,D4:D13)

OR

=LOOKUP(L8,B4:B13,D4:D13)

Chaliye ab dekhein Array form.

Array form mein jo bhi aapki last row ya column hota hai vahin se ye result vector le leta hai. Isliye array bahut dhyan se select karna hota hai.

Ab jaise agar aapko Krishna ke Maths ke marks array form se nikalna hai toh sabse pehle likhein-

=LOOKUP(

Lookup value

Ab ismein apni lookup_value likhlijiye inverted commas (here  “Krishna”) mein ya cell no. jisme lookup value hai enter karde-

=LOOKUP(“Krishna”

OR

=LOOKUP(L8

Array

Ab apni array apne value jo aap nikalna chahtein hain uske according select karle. Jaise mujhe Maths ke marks chahiye the isliye mere Maths ke marks selected array mein sabse last column mein hai. Yaad rakhiyega ki jo value aapko chahiye vo array mein last me hi ho.

=LOOKUP(“Krishna”,B4:D13)

OR

=LOOKUP(L8,B4:D13)

Dekhiye vector and array dono form se same answer aaya hai.

NOTE:
LOOKUP
 function  ka array form HLOOKUP aur VLOOKUP function  se bahut similar hai. Bas HLOOKUP se aap lookup_value first row mein select karte hain, VLOOKUP mein first column aur LOOKUP mein array ke dimensions ke according hota hai.

Isse humare Excel ke saare formulas aur functions khatm hote hain.

Agar aap chahein toh ye raw data aur final excel data sheet download kar sakte hain aur check kar sakte hain apne answers.
Sheets ka link niche diya hua hai.

I hope ye functions aapko samajh aaye honge. Agar aapki koi query ya feedback ho toh aap niche comments sections mein zarur reply kare, hum aapki poori tarah se help karenge.


Yeh tutorial Hindi MS Excel full course in Hindi ka 4th bhag hai. Agar aap chahte hai to aap pichle bhaag bhi kar sakte hai.

Click karke us tutorial pe ja sakte hai

MS Excel in Hindi full tutorial Beginner course part 1 – Agar excel bilkul nahi aati

MS Excel in Hindi full tutorial Beginner course part 2 – MS Excel ko use karke data analysis kaise karte hai (Sort aur Filter kaise kare)

MS Excel in Hindi full tutorial Advance course part 1 – MS Excel formulas and Functions simple wale

Himanshi is an economics student at Delhi University. She likes to juggle around with numbers and take part in data-driven analytical projects. In her spare time, she likes to watch political comedies and is also an aspiring member of ’The Avengers’ ;)

LEAVE A REPLY

Please enter your comment!
Please enter your name here