Excel Formula 2

                          Rajeev Tiwari
 Excel Formula sikhe aasaan bhasa me

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(

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,

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

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-

. 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(

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

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

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

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)

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)