How to use the INDEX-MATCH function in MS Excel yani ki Ms Excel Me INDEX-MATCH Function Ka Use Kaise Kare iss post me hum janenge so agar aap microsoft excel use karte hai aur microsoft excel me index-match function kaise istemal karte hai iske baare me janana chahte hai to ye post aapke liye hi hai.

INDEX-MATCH ek atyadhik kushal aur bharosemand lookup samadhan hai jo paramporik VLOOKUP ki seemitataon ko poori tarah samapt karta hai. Iski sabse adviteeya visheshta baen or (Left Lookup) data khojne ki sakshamta hai, jo VLOOKUP mein asambhava hai. Yah gati ke mamle mein bhi shreshth hai, vishaal datasets par yah tezi se parinaam deta hai, kyonki yah kewal nirdisht column shreniyon ko sansadhit karta hai.

Yah sanyojan asaadhaaran lachilapan pradaan karta hai. Yadi aap srot data mein column jodte ya hatate hain, to sutra swayam-samayojit rahta hai aur kshatigrast nahin hota. Yah data prabandhan ko saral banata hai. Iske atirikt, INDEX-MATCH ko aasani se multiple sharton (Multiple Criteria) ke saath samayojit kiya ja sakta hai, jisse yah jatil data nishkarshan ke liye ati-upyogi ban jata hai. Peshevar Excel upyogkarta iski sateekta aur vishwasneeyata ke kaaran ise prathamikta dete hain, jisse yah data vishleshan ka ek anivaarya upkaran ban jata hai.

• Microsoft Excel me Vlookup Function Ka Use Kaise Kare
• Ms Excel Me Keyboard Shortcut Ka Use Kaise Kare

Ms Excel Me INDEX-MATCH Function Ka Use Kaise Kare

INDEX-MATCH function ka sanyojan (combination) MS Excel mein VLOOKUP se kahin adhik lacheela (flexible) aur shaktishaali hota hai, khaaskar jab data badi aur jatil (complex) taalikayon (tables) mein ho.

INDEX-MATCH Kyon Behtar Hai?

1. Column ki Disha (Search Direction) ki Seema Nahi: VLOOKUP keval pehle column mein hi khoj sakta hai aur parinaam hamesha daayeen (right) or ke column se hi vaapas laata hai. INDEX-MATCH baen (left) ya kisi bhi disha mein khoj sakta hai aur parinaam la sakta hai.

2. Gatishil Column Sankhya (Dynamic Column Number): VLOOKUP mein col_index_num ko haath se daalna padta hai. INDEX-MATCH mein, aap MATCH function ka upyog karke column sankhya ko swachalit (automatically) roop se dhoondh sakte hain.

3. Pradarshan (Performance): Bahut bade data seton par, INDEX-MATCH VLOOKUP ki tulna mein aksar tezi se kaam karta hai.

INDEX-MATCH Ka Syntax

INDEX-MATCH sanyojan mein do alag-alag function hote hain, jo is prakaar kaam karte hain:

1. MATCH Function: Yah bataata hai ki aapka lookup_value kis pankti (row) sankhya par sthit hai.

   • Syntax: =MATCH(lookup\_value, lookup\_array, [match\_type])

2. INDEX Function: Yah batai gayi pankti sankhya aur column sankhya par sthit maan (value) ko vaapas laata hai.

   • Syntax: =INDEX(array, row\_num, [column\_num])

Antim Sanyojan (The Combined Formula):

Hum row_num ko khojne ke liye MATCH function ko INDEX ke andar daalte hain.

Udaaharan Ke Saath Upyog (Example)

Maan lijiye aapke paas ek data table hai (A2:D5), aur aap Karmchaari Ka Naam (Name) ka upyog karke unki Karmchaari ID khojna chahte hain. (Yahaan Name column, ID column ke daayeen or hai – VLOOKUP yah nahi kar sakta).

Aap cell F2 mein naam Priya ki Karmchaari ID khojna chahte hain.

1. MATCH Function Se Pankti Sankhya Gyat Karna:

Hum ‘Priya’ naam ko ‘Naam’ column (A2:A5) mein khojenge:

• Yah function ‘Priya’ ki pankti sankhya vaapas karega. Table ke sandarbh mein, yah 2 vaapas karega (Priya doosri pankti mein hai).

2. INDEX Function Se Parinaam Vaapas Laana:

Ab hum INDEX function ka upyog karke Karmchaari ID column (D2:D5) se 2veen pankti ka maan vaapas laayenge:

• Yah function D2:D5 range ki doosri item (yaani 102) vaapas karega.

3. Antim INDEX-MATCH Formula (Maan lijiye G2 mein):

• Parinaam: 102

INDEX-MATCH Ke Laabh (Advantages)

INDEX-MATCH ek shaktishaali tareeka hai jo VLOOKUP ki sabhi kamiyon ko door karta hai.

Do Sharton Ke Saath INDEX-MATCH Ka Upyog (INDEX-MATCH with Two Criteria)

Jab aapko ek value wapas chahiye jo do alag-alag column mein sharton ko poora karti ho, to hum MATCH function ke bheetar ek Array Formula ka upyog karte hain.

Basic Formula Sanrachna:

Step-by-Step Udaaharan

Maan lijiye aapke paas data A2:D100 mein hai:

Aap Criteria 1 (Salesperson = “Ravi”) aur Criteria 2 (Region = “North”) ke aadhaar par Sales Amount jaanna chahte hain.

1. Formula Likhein:

Us cell mein jahaan aap parinaam chahte hain, yeh formula type karein:

=INDEX(D2:D100, MATCH(1, (C2:C100=F2) * (B2:B100=F3), 0))

2. Array Formula Ke Roop Mein Enter Karein (Crucial Step):

Yah formula saamanya formula nahin hai. Ise kaam karne ke liye, aapko ise type karne ke baad kewal Enter nahin dabana hai, balki Ctrl + Shift + Enter ek saath dabana hoga.

Parinaam: Jab aap Ctrl + Shift + Enter dabate hain, to Excel swachalit roop se aapke formule ke chaaron or Curly Brackets {} laga dega:

{=INDEX(D2:D100, MATCH(1, (C2:C100 = F2) * (B2:B100} = F3), 0))}

(Ye brackets aapko manually type nahin karne hain.)

Yah Kaam Kaise Karta Hai? (Explanation)

• Tark (Logic): Jab aap do taarkik (logical) Arrays ko guna karte hain, to TRUE ko 1 aur FALSE ko 0 maana jaata hai.

  • Jahaan dono shartein poori hoti hain (TRUE * TRUE), gundhphal 1 hota hai.

  • Yadi koi bhi shart poori nahin hoti hai, to gundhphal 0 hota hai.

• MATCH(1, …): Yah function khojta hai ki 1 kahaan hai (yaani, wah pehli pankti jahaan dono shartein satya hain) aur uski sthiti sankhya wapas kar deta hai.

• INDEX(…): INDEX function phir us sthiti sankhya ka upyog karke vaanchhit Sales Amount (D2:D100 se) lautaata hai.

INDEX-MATCH Function Ka Benefits

INDEX-MATCH combination Excel mein ek adhunik aur ati-shaktishali lookup tareeka hai. Iski pratham aur sabse badi visheshta Left Lookup ki akhand kshamta hai. Jabki VLOOKUP sakhti se kewal daayi or (right) hi data khoj sakta hai, INDEX-MATCH aazadi deta hai ki aap apne lookup column ke baayi or ke data ko bhi sahajta se wapas laa sakte hain. Yah saral lagne wali suvidha jatil database structures mein nirnayak (crucial) saabit hoti hai.

Dusra bada benefit iski gati aur kushalata (efficiency) hai. Bade aur vishaal datasets par, jahaan lakhon rows hoti hain, INDEX-MATCH VLOOKUP se tezi se process karta hai. Iska kaaran yah hai ki yah kewal do nirdisht range (lookup aur return range) ko hi scan karta hai, jabki VLOOKUP ko poori table array ko samagra roop se process karna padta hai. Is kushal chunaav se calculation time mein mahila kami aati hai.

Teesra ahem fayda iski dridhata (robustness) aur lachilapan (flexibility) hai. VLOOKUP column number par aashrit hota hai, aur agar aap source data mein koi naya column daal dete hain ya hata dete hain, to VLOOKUP ka column index badal jaata hai aur formula toot jaata hai. Iske vipreet, INDEX-MATCH cell reference (range) par kaam karta hai, isliye structure mein badlaav aane par bhi yah akshunn bana rahta hai.

Ant mein, INDEX-MATCH ko aasani se Multiple Criteria lookup ke liye badhaya ja sakta hai. Yeh ise vishwa-star par data analysis aur complex reporting ke liye anivaarya (essential) tool bana deta hai. Is behtar functionality ke kaaran, yeh vyavsaayik (professional) Excel users ki prathamikta hai.