INDEX MATCH Formula Generator left lookup kaise likhta hai?

Yeh lautane wale column ko INDEX mein pehle rakhta hai aur jis column mein aap dhoondhte hain use MATCH mein, isliye jawab search column ke baayein ho sakta hai. VLOOKUP yeh nahi kar sakta kyunki woh sirf apne pehle column ke daayein ke columns lautata hai.

Array aur no-array multiple criteria formulas mein kya fark hai?

Dono TRUE/FALSE tests ko guna karke number 1 dhoondhte hain. Classic form ko Excel 2019 aur usse pehle mein Ctrl+Shift+Enter chahiye, jabki MATCH ke andar INDEX(...,0) wala version array evaluation force karta hai, isliye har Excel version mein normal Enter chalta hai.

INDEX MATCH Formula Generator ka two-way mode cell kaise dhoondhta hai?

Ek MATCH aapke row labels mein row position dhoondhta hai aur doosra MATCH aapke headers mein column position. Phir INDEX data range ke us row aur column ka cell lautata hai, jo ise do keys se lookup banata hai.

INDEX MATCH Formula Generator mein kaun sa match type chunna chahiye?

IDs, names aur codes ke liye exact (0) use karein. Sorted data par lookup se na badhne wali sabse badi value ke liye ascending (1), jaise tax bands, aur unchi se neechi sorted data par lookup se kam na hone wali sabse chhoti value ke liye descending (-1) use karein.

Kya INDEX MATCH Formula Generator doosra ya teesra match lauta sakta hai?

Haan. Nth match mode Excel 2010 aur uske baad ke liye AGGREGATE version, ek classic array version, aur Excel 365 aur Google Sheets ke liye INDEX FILTER version banata hai. Aap n ko number ya cell reference ke roop mein chunte hain.

INDEX MATCH Formula Generator use karte waqt kya mera sheet data private rehta hai?

Haan. Ranges, lookup values aur sample cells aapke browser mein rehte hain aur aapke device par evaluate hote hain. Aapka aakhri mode aur settings, saath hi test grid ke cells, isi browser ki local storage mein save hote hain taaki page waisa hi khule jaisa aapne chhoda tha.

INDEX MATCH Formula Generator

Name: INDEX MATCH Formula Generator
Author: ArrayKit

Left lookups, two-way tables, multiple criteria aur Nth matches ke liye INDEX MATCH formulas banayein, aur unhe browser chhode bina live sample sheet par test karein.

Har formula aur har test result aapke browser mein banta hai. Jo ranges, values aur sample cells aap dalte hain woh kabhi upload nahi hote aur ArrayKit ko kuch nahi bheja jaata.

Banaya aur maintain kiya: Udit Agarwal

VLOOKUP Formula Generator kholein

INDEX MATCH Formula Generator ke baare mein

INDEX MATCH woh lookup hai jo woh karta hai jo VLOOKUP nahi kar sakta: yeh kisi bhi column mein dhoondhta hai, koi bhi doosra column lautata hai, columns daalne par bhi nahi toot-ta aur kai conditions tak failta hai. Ise galat likhna bhi aasaan hai, kyunki return range aur lookup range ka mel baithna zaroori hai aur multi-criteria form ko array handling chahiye. Is generator mein paanch modes hain. Basic aur left lookup classic jodi likhte hain. Two-way row ke liye ek MATCH aur column ke liye doosra MATCH use karta hai. Multiple criteria array form, ek aisa version jise Ctrl+Shift+Enter nahi chahiye, ek helper column aur FILTER ya XLOOKUP alternatives deta hai. Nth match doosra ya teesra occurrence lautata hai. Ek sample sheet result ko locally evaluate karti hai, aur ek comparison table samjhati hai ki INDEX MATCH, VLOOKUP ya XLOOKUP mein se kise kab chunna chahiye. Aap jo type karte hain woh upload nahi hota.

Features

Paanch modes: basic, left lookup, two-way, multiple criteria aur Nth match
Exact, ascending (1) aur descending (-1) match types, sorted-data chetavniyon ke saath
Ek se chaar tak multiple criteria, array formula, no-array version, helper column, XLOOKUP aur FILTER ke roop mein
Chetavni deta hai jab return aur lookup ranges alag rows se shuru hon ya unki unchai alag ho
IFERROR ya IFNA wrapper, TRIM, text-aur-number conversion aur wildcard options
Kisi bhi locale mein Excel aur Google Sheets ke liye comma ya semicolon argument separator
Live sample sheet jo matched cell aur lautaye gaye cell ko highlight karti hai
INDEX MATCH, VLOOKUP aur XLOOKUP ki side-by-side tulna

INDEX MATCH Formula Generator kaise use karein

Ek mode chunein: basic, left lookup, two-way, multiple criteria ya Nth match
Lookup value aur ranges dein, aur unhe dollar-sign button se lock karein
Apni sheet ke hisaab se wrapper, trimming aur separator options chunein
Mukhya formula copy karein, ya doosre Excel versions ya Google Sheets ke liye koi alternative
Result pakka karne ke liye sample sheet edit karein, phir kuch galat lage to chetavniyan padhein

Example

Input

Mode:        Multiple criteria
Return:      $D$2:$D$100  (Sales)
Criterion 1: $A$2:$A$100 = F2  (South)
Criterion 2: $B$2:$B$100 = G2  (Apple)

Output

=IFERROR(INDEX($D$2:$D$100,MATCH(1,INDEX(($A$2:$A$100=F2)*($B$2:$B$100=G2),0),0)),"Not found")

Live test: South + Apple returns 95 (the first matching row)

Extra INDEX(...,0) purane Excel mein Ctrl+Shift+Enter ki zaroorat hata deta hai.

Common errors aur troubleshooting

INDEX MATCH galat row ki value lautata hai. — Return range aur lookup range alag rows se shuru hoti hain. INDEX wahi position use karta hai jo MATCH ne payi, isliye dono ranges ek hi row se shuru honi chahiye aur unki unchai barabar honi chahiye. Jab aisa na ho to generator chetavni deta hai.
Multiple-criteria formula purane Excel mein #VALUE! ya sirf pehla cell dikhata hai. — Classic array form ko Excel 365 se pehle Ctrl+Shift+Enter chahiye. INDEX(...,0) wala no-array version, helper column, ya XLOOKUP use karein, jo sab normal Enter se chalte hain.
List mein saaf maujood value ke liye MATCH #N/A deta hai. — Text ke roop mein rakhe numbers aur trailing spaces check karein. TRIM aur matching number ya text conversion on karein, aur jab tak list sorted na ho match type exact (0) par rakhein.
Nth match formula khaali hai ya n bahut bada hone par error deta hai. — Jitne match maange hain unse kam matches hain. Formula ko IFERROR mein wrap karein; IFNA is case mein aane wala error nahi pakadta.

Aksar pooche jaane wale sawaal

INDEX MATCH Formula Generator left lookup kaise likhta hai?: Yeh lautane wale column ko INDEX mein pehle rakhta hai aur jis column mein aap dhoondhte hain use MATCH mein, isliye jawab search column ke baayein ho sakta hai. VLOOKUP yeh nahi kar sakta kyunki woh sirf apne pehle column ke daayein ke columns lautata hai.
Array aur no-array multiple criteria formulas mein kya fark hai?: Dono TRUE/FALSE tests ko guna karke number 1 dhoondhte hain. Classic form ko Excel 2019 aur usse pehle mein Ctrl+Shift+Enter chahiye, jabki MATCH ke andar INDEX(...,0) wala version array evaluation force karta hai, isliye har Excel version mein normal Enter chalta hai.
INDEX MATCH Formula Generator ka two-way mode cell kaise dhoondhta hai?: Ek MATCH aapke row labels mein row position dhoondhta hai aur doosra MATCH aapke headers mein column position. Phir INDEX data range ke us row aur column ka cell lautata hai, jo ise do keys se lookup banata hai.
INDEX MATCH Formula Generator mein kaun sa match type chunna chahiye?: IDs, names aur codes ke liye exact (0) use karein. Sorted data par lookup se na badhne wali sabse badi value ke liye ascending (1), jaise tax bands, aur unchi se neechi sorted data par lookup se kam na hone wali sabse chhoti value ke liye descending (-1) use karein.
Kya INDEX MATCH Formula Generator doosra ya teesra match lauta sakta hai?: Haan. Nth match mode Excel 2010 aur uske baad ke liye AGGREGATE version, ek classic array version, aur Excel 365 aur Google Sheets ke liye INDEX FILTER version banata hai. Aap n ko number ya cell reference ke roop mein chunte hain.
INDEX MATCH Formula Generator use karte waqt kya mera sheet data private rehta hai?: Haan. Ranges, lookup values aur sample cells aapke browser mein rehte hain aur aapke device par evaluate hote hain. Aapka aakhri mode aur settings, saath hi test grid ke cells, isi browser ki local storage mein save hote hain taaki page waisa hi khule jaisa aapne chhoda tha.

Related tools

VLOOKUP Formula Generator — Sahi VLOOKUP formula step by step banayein: IFERROR, exact ya approximate match, XLOOKUP aur INDEX MATCH alternatives aur live test.
Excel Formula Generator — Conditional sums, lookups, dates, text splitting ya ranking jaisa task chunein aur paste-ready Excel ya Google Sheets formula paayein.
Excel Formula Explainer — Koi bhi Excel ya Google Sheets formula paste karein aur har function aur argument ka aasaan bhasha mein step-by-step breakdown paayein.
Excel Data Cleaner — Ganda spreadsheet saaf karein: spaces trim, case theek, blanks aur duplicates hatayein, dates aur numbers standardise karke .xlsx ya CSV export karein.
Excel to CSV Converter — Ek Excel workbook ki kisi bhi sheet ko delimiter aur date options ke saath saaf CSV mein export karein.
CSV to Markdown Table — CSV ya TSV ko ek GitHub-flavored Markdown table mein badlein

Excel & Spreadsheets ke aur tools

Excel Formula Explainer — Koi bhi Excel ya Google Sheets formula paste karein aur har function aur argument ka aasaan bhasha mein step-by-step breakdown paayein.
Excel Formula Generator — Conditional sums, lookups, dates, text splitting ya ranking jaisa task chunein aur paste-ready Excel ya Google Sheets formula paayein.
Excel Function Translator — Excel formulas ko English, German, French, Spanish, Italian, Portuguese, Dutch, Swedish aur Russian ke beech sahi separators ke saath translate karein.
Excel VBA Macro Generator — Tasks ki library se ready-to-run VBA macros banayein (loops, blank rows hatana, sheets PDF mein export, mail aur aur bhi) apni ranges aur naamon ke saath.
CSV to Excel — CSV ya TSV files ko browser mein formatted .xlsx workbook banayein: delimiter detection, header styling, column types aur kai sheets.
JSON to Excel — JSON arrays ya nested objects ko Excel workbook mein badlein: flatten columns, har array ke liye sheet aur typed cells.
Excel Data Cleaner — Ganda spreadsheet saaf karein: spaces trim, case theek, blanks aur duplicates hatayein, dates aur numbers standardise karke .xlsx ya CSV export karein.
VLOOKUP Formula Generator — Sahi VLOOKUP formula step by step banayein: IFERROR, exact ya approximate match, XLOOKUP aur INDEX MATCH alternatives aur live test.

Excel & Spreadsheets ke sabhi tools dekhein (9) · Saare tools