VLOOKUP Formula Generator

Lookup ek baar bata dein aur Excel ya Google Sheets ke liye sahi VLOOKUP paayein, IFERROR, XLOOKUP aur INDEX MATCH versions aur live test ke saath, sab aapke browser mein.

Formula builder aur live test poori tarah aapke browser mein chalte hain. Jo lookup values, ranges aur sample cells aap type karte hain woh kabhi upload nahi hote, aur ArrayKit ko kuch nahi bheja jaata.

Banaya aur maintain kiya: Udit Agarwal

VLOOKUP, INDEX MATCH and XLOOKUP, Explained

INDEX MATCH Formula Generator kholein

VLOOKUP Formula Generator ke baare mein

Zyadatar toote hue VLOOKUPs kuch gine-chune wajahon se fail hote hain: range anchored nahi hoti, column index ek se off hota hai, approximate match unsorted data par chal raha hota hai, ya ek number ki text se tulna ho rahi hoti hai. Yeh generator lookup value, table range aur return column poochta hai, phir formula likhta hai jisme argument order aur quoting pehle se sahi hote hain. Number ke bajaye column letter type karein aur use table ke shuru ke hisaab se convert kar diya jaata hai. Ek click mein IFERROR ya IFNA, TRIM, text-ya-number conversion ya wildcard jodein, aur European locales ke liye argument separator ko semicolon par badlein. Formula ke neeche ek chhoti editable sheet use asli VLOOKUP rules se evaluate karti hai aur #N/A ko imaandaari se dikhati hai. Sab kuch aapke device par compute hota hai; koi cells ya formulas upload nahi hote.

Features

  • VLOOKUP ko sahi argument order, quoting aur text ke andar doubled quotes ke saath likhta hai
  • Return column number ya letter se, table ke pehle column ke hisaab se convert hota hua
  • Ek click mein $ locking, whole-column ranges aur bounded rows 2 se 100 ki range
  • Aapke apne not-found text ke saath IFERROR ya IFNA wrapper
  • Gande lookup values ke liye TRIM, text-as-number, number-as-text aur wildcard options
  • Barabar ke XLOOKUP, INDEX MATCH, FILTER aur QUERY formulas, har ek ke Copy button ke saath
  • Kisi bhi locale mein Excel aur Sheets ke liye comma ya semicolon separators
  • Exact, approximate, wildcard aur unsorted-data behaviour wala live test grid, saath mein #N/A ki checklist

VLOOKUP Formula Generator kaise use karein

  1. Lookup value ko F2 jaise cell ke roop mein dein, ya dhoondhne ke liye text ya number type karein
  2. Table range dein jisme lookup column pehle ho, phir use dollar-sign button se lock karein
  3. Return column number ya letter se chunein aur exact ya approximate match chunein
  4. Zaroorat ho to IFERROR, TRIM ya separator change jodein, phir formula copy karein
  5. Sample sheet par result check karein aur agar #N/A dikhe to checklist padhein

Example

Input

Lookup value:  F2  (the cell contains 103)
Table range:   $A$2:$D$100
Return column: 3  (Price)
Match type:    Exact
If not found:  IFERROR with "Not found"

Output

=IFERROR(VLOOKUP(F2,$A$2:$D$100,3,FALSE),"Not found")

XLOOKUP:      =IFERROR(XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100),"Not found")
INDEX MATCH:  =IFERROR(INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0)),"Not found")
Live test on the sample table: 103 returns 4.00

Ek hi lookup teen tarah se likha gaya, live test mein cherry wali row highlight hui.

Common errors aur troubleshooting

  • Value table mein saaf dikhne ke bawajood VLOOKUP #N/A deta hai. — Shayad dono values ke type alag hain ya unmein chhupe spaces hain. Text ke roop mein rakha number kabhi asli number ke barabar nahi hota, aur trailing space ek alag value bana deta hai. TRIM option on karein, ya number aur text options se ek taraf convert karein.
  • Formula pehli row mein chalta hai aur neeche jaakar fail hota hai. — Table range lock nahi thi, isliye formula fill karte waqt woh neeche khisakti gayi. Lock $ button dabayein taaki range $A$2:$D$100 dikhe aur dobara neeche fill karein.
  • Unsorted table par VLOOKUP galat row deta hai. — Approximate match maanta hai ki pehla column ascending sorted hai aur binary search karta hai. IDs, names aur codes ke liye exact match par jaayein, aur approximate match ko tax brackets jaisi ranges ke liye rakhein.
  • Table range badalne ke baad Excel #REF! batata hai. — Column index range ke columns ki sankhya se bada hai. Generator yeh check karta hai aur formula copy karne se pehle table ki chaudai batata hai.

Aksar pooche jaane wale sawaal

VLOOKUP Formula Generator column letter ko index mein kaise badalta hai?
Yeh aapke table range ke pehle column ko aapke type kiye letter se ghatata hai aur ek jodta hai. Jis table ki shuruaat column B se ho, usmein return column D index 3 ban jaata hai. Agar letter table ke baayein ho, to tool samjhata hai ki VLOOKUP baayein nahi dekh sakta aur INDEX MATCH sujhata hai.
VLOOKUP Formula Generator ka live test asal mein kya evaluate karta hai?
Yeh aapke formula ke options ko page par maujood chhoti sheet par chalata hai, wahi rules lagakar jo ek spreadsheet lagati hai: exact match case ignore karta hai aur wildcards maanta hai, approximate match ek binary search hai jo sorted data maangta hai, khaali return cells zero ke roop mein aate hain, aur mismatched types #N/A dete hain.
VLOOKUP ko wrap karte waqt IFERROR chunna chahiye ya IFNA?
IFNA sirf not-found error chhupata hai, isliye galat column index ya toot gayi range phir bhi dikhti hai. IFERROR har error chhupata hai, jo reports ke liye kaam ka hai lekin galtiyan dhak sakta hai. Generator dono deta hai aur live test mein har ek ka result dikhata hai.
VLOOKUP mein approximate match option kab surakshit hai?
Sirf tab jab pehla column ascending sorted ho aur aapko lookup value se na badhne wali sabse nazdeeki value chahiye, jaise score-to-grade table. Generator chetavni deta hai, aur test grid unsorted data ko flag karta hai kyunki tab jawab bina kisi error ke galat ho sakta hai.
Mere VLOOKUP ke neeche XLOOKUP aur INDEX MATCH versions kyun dikhte hain?
Woh wahi inputs par wahi cell lautate hain lekin VLOOKUP ki seemaon se bachte hain: woh baayein dekh sakte hain aur column daalne par toot-te nahi. Jo bhi aapke saathiyon ke Excel ya Google Sheets version se mel khaye use copy karein.
Kya VLOOKUP Formula Generator meri table ya formula kahin bhejta hai?
Nahi. Formula text aur sample cells sirf aapke browser mein sambhaale jaate hain. Aapki aakhri settings aur test grid ke cells aapke device ki local storage mein yaad rakhe jaate hain taaki page waisa hi khule jaisa aapne chhoda tha.

Related tools

  • INDEX MATCH Formula Generator — Left lookup, two-way lookup aur multiple criteria ke liye INDEX MATCH formulas banayein, live test table aur copy-ready output ke saath.
  • 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 JSON Converter — CSV ya TSV ko type inference aur nesting ke saath saaf JSON mein badlein, sab browser mein.

Excel & Spreadsheets ke aur tools

  • INDEX MATCH Formula Generator — Left lookup, two-way lookup aur multiple criteria ke liye INDEX MATCH formulas banayein, live test table aur copy-ready output ke saath.
  • 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.

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