XLOOKUP, Nested IF Formula, Grade System और VBA Print Button के साथ एक Professional Annual Marksheet बनाएं – Step-by-Step Complete Guide

📋 विषय-सूची (Table of Contents)

  1. Introduction – Marksheet क्यों और कैसे?
  2. File Structure – दो Sheets का Setup
  3. Step 1: Data Sheet बनाएं (Master Data)
  4. Step 2: Excel Table बनाएं (Structured Reference)
  5. Step 3: Marksheet Sheet का Layout बनाएं
  6. Step 4: XLOOKUP Formula लगाएं (Auto Data Fetch)
  7. Step 5: Grade Lookup Table बनाएं
  8. Step 6: Marks, Total, Percentage Formula
  9. Step 7: Grade Formula (Nested IF)
  10. Step 8: Division/Result Formula
  11. Step 9: VBA Print Button बनाएं
  12. Step 10: Professional Formatting
  13. FAQ – अक्सर पूछे जाने वाले सवाल
💡

Introduction – Marksheet क्यों Automatic बनाएं?

अगर आप एक Teacher हैं, School Administrator हैं, या Coaching Center चलाते हैं, तो हर Exam के बाद Students के लिए Marksheet बनाना एक बड़ा काम होता है। हर बार manually Roll Number देखकर, नाम टाइप करके, Marks जोड़कर Grade निकालना — यह सब बहुत time-consuming और error-prone होता है।

इस Tutorial में हम एक Fully Automatic Excel Marksheet बनाएंगे जिसमें:

✅
इस Marksheet में क्या होगा? सिर्फ Roll Number डालते ही — Student का नाम, पिता का नाम, Marks, Grade, Percentage, और Division सब कुछ Automatically भर जाएगा। एक VBA Button से Print Preview भी होगी।

इस Tutorial को follow करने के लिए आपको Excel 2016 या उसके बाद का version चाहिए (XLOOKUP के लिए Excel 2019 / Microsoft 365 recommended है)।

🗂

File Structure – दो Sheets का Setup

हमारी Marksheet File में दो Sheets होंगी। पहली Sheet में सभी Students का Data होगा (Master Database), और दूसरी Sheet में Marksheet का Template होगा।

⚠️
Important: File को .xlsm (Excel Macro-Enabled Workbook) format में save करें, क्योंकि इसमें VBA Macros होंगे। सामान्य .xlsx में VBA save नहीं होता।
1

Step 1: Data Sheet बनाएं (Master Database)

सबसे पहले Excel open करें और पहली Sheet का नाम “Data” रखें। इस Sheet में Row 1 में निम्नलिखित Headers बनाएं:

ColumnHeader Nameउदाहरण Data
ARoll No1001, 1002, 1003…
BRegistration No20240001, 20240002…
CNameAmit Kumar, Rahul Singh…
DFather NameRaj Kumar, Suresh Singh…
EMother NameSunita Devi, Meena Devi…
FMobile9876543210…
GAddressPatna, Delhi…
HClass10, 12…
IHindi78, 85…
JEnglish82, 88…
KMath90, 92…
LScience85, 86…
MSocial Science80, 84…
NSanskrit/Urdu75, 80…
📌 Pro Tip

Row 1 में सभी Headers बनाने के बाद, Row 2 से अपने सभी Students का data भरना शुरू करें। Marks columns (I से N) में 0 से 100 के बीच का integer value डालें।

2

Step 2: Excel Table बनाएं (Structured Reference)

Data enter करने के बाद, इस data range को एक Excel Table में convert करें। इससे XLOOKUP में Structured References का use होगा, जो formula को ज़्यादा readable और reliable बनाता है।

🔧 Steps

1. Data Sheet में किसी भी data cell पर click करें (जैसे A1)।
2. Keyboard पर Ctrl + T दबाएं।
3. “Create Table” dialog में “My table has headers” checkbox checked रखें।
4. OK click करें।
5. Table Name change करें: Table Design Tab → Table Name box में “Master_Data” टाइप करें।

💡
Excel Table क्यों ज़रूरी है? जब आप बाद में नए students add करेंगे, तो Table automatically expand हो जाएगी और XLOOKUP formula को manually update नहीं करना पड़ेगा। साथ ही Master_Data[Name] जैसा syntax formula को बहुत clear बनाता है।

अगर आप Excel के basic formulas सीखना चाहते हैं, तो यह भी देखें: Excel Basic Formulas Guide

3

Step 3: Marksheet Sheet का Layout बनाएं

अब दूसरी Sheet पर जाएं और उसका नाम “Marksheet” रखें। इस Sheet पर निम्नलिखित Layout बनाएं:

🏫 Header Area (Rows 3-5)

CellContent
B3School Name (जैसे “ABC Public School”)
B4“Result” (Bold, Large Font)
B5“Annual Examination 2026”

🔍 Check Result Area (Rows 4-5, Right Side)

CellContent
M4“Check Result” (Label)
M5“Roll Number” (Label)
N5Input Cell – यहाँ Roll Number डाला जाएगा (Yellow Background दें)

👤 Student Info Area (Rows 6-10)

CellLabelFormula Cell
B6Student NameE6
B7Father’s NameE7
B8Roll NumberE8
B9Registration NumberE9
B10ClassE10

📚 Marks Detail Table (Rows 13-19)

CellContent
B13Subject (Header)
D13Total Mark (Header)
F13Marks Obtained (Header)
H13Grade (Header)
B14:B19Hindi, English, Math, Science, Social Science, Sanskrit/Urdu
D14:D19100 (सभी में)
F14:F19XLOOKUP Formula (Marks Auto-Fetch)
H14:H19Nested IF Grade Formula

📊 Result Area (Rows 20-22)

CellContent
B20“Final Result”
B21“Result/Division”
D21Division Formula
G21“Total Marks”
H21SUM Formula
B22“Grade”
D22Grade Formula
G22“Percentage”
H22Percentage Formula
4

Step 4: XLOOKUP Formula लगाएं (Auto Data Fetch)

यह इस Marksheet की सबसे important feature है। N5 cell में जो भी Roll Number डाला जाएगा, उससे automatically Student की सारी information fetch हो जाएगी।

XLOOKUP function Excel 2019 और Microsoft 365 में available है। यह VLOOKUP का एक बेहतर version है।

📌 Syntax

XLOOKUP Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])

✍️ Student Name (Cell E6)

=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[Name], , 0)
  • $N$5 — यह वह cell है जिसमें Roll Number डाला जाएगा ($$ से absolute reference)
  • Master_Data[Roll No] — Data Sheet की “Roll No” column में search होगा
  • Master_Data[Name] — मिलने पर “Name” column की value return करेगा
  • 0 — Exact Match mode

✍️ बाकी Cells के लिए Formulas

CellFormula
E7=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[Father Name],, 0)
E8=$N$5 (Roll Number simply reference करें)
E9=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[Registration No],, 0)
E10=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[Class],, 0)

✍️ Marks Fetch (Cell F14 – Array Formula)

सभी 6 subjects के marks एक ही formula से fetch करने के लिए हम Dynamic Array (Spill) का use करते हैं। F14 में यह formula enter करें और Ctrl + Shift + Enter दबाएं:

=XLOOKUP($N$5, Master_Data[Roll No], Master_Data[[Hindi]:[Sanskrit/Urdu]],, 0)
  • Master_Data[[Hindi]:[Sanskrit/Urdu]] — यह Hindi से Sanskrit/Urdu तक की सभी Columns (6 subjects) को एक साथ return करता है।
  • यह एक Spill Formula है — F14 में enter करने पर F14:F19 तक automatically fill हो जाएगा।
  • बाकी cells (F15:F19) में कोई formula न डालें।
🌟
Spill Formula की खासियत: जब आप F14 में यह Array formula डालेंगे, तो Excel स्वयं F14:F19 में values भर देगा। अगर F15:F19 में पहले से कुछ है तो वो delete करना होगा।
5

Step 5: Grade Lookup Table बनाएं

Marksheet Sheet के Right side में (Column M, N, O) एक Grade Reference Table बनाएं। यह table Grade Formula में reference की जाएगी।

📊 Subject-wise Grade Table (M19:O28)

Cell MCell N (Min Marks)Cell O (Max Marks)
A+91100
A8190
B+7180
B6170
C+5160
C4150
D3340
E (Fail)032

📊 Division/Percentage Table (M11:O15)

Cell M (Label)Cell N (Min %)Cell O (Max %)
1st Division0.60 (60%)1 (100%)
2nd Division0.50 (50%)0.59
3rd Division0.33 (33%)0.49
Fail00.33
⚠️
ध्यान दें: Division table में percentage values को decimal format में enter करें। 60% के लिए 0.60 लिखें, न कि 60। Cells को Percentage format में format करें।
6

Step 6: Marks, Total और Percentage Formula

➕ Total Marks (Cell H21)

=SUM(F14:F19)
  • सभी 6 subjects के Marks का योग
  • Maximum Total = 600 (6 subjects × 100 marks each)

📊 Percentage (Cell H22)

=H21 / SUM(D14:D19)
  • H21 = Total Obtained Marks
  • SUM(D14:D19) = Total Maximum Marks (600)
  • Result एक decimal number आएगा (जैसे 0.82 = 82%)
  • Cell H22 को Percentage format में set करें: Ctrl+1 → Number → Percentage → 2 decimal places
7

Step 7: Grade Formula – Nested IF (प्रत्येक Subject के लिए)

प्रत्येक Subject की Grade calculate करने के लिए हम Nested IF + AND formula use करेंगे। यह formula Marks को Grade Lookup Table से compare करता है।

✍️ Hindi Grade (Cell H14)

Nested IF Formula – Grade Calculation
=IF(AND(F14>=$N$21,F14<=$O$21),$M$21, IF(AND(F14>=$N$22,F14<=$O$22),$M$22, IF(AND(F14>=$N$23,F14<=$O$23),$M$23, IF(AND(F14>=$N$24,F14<=$O$24),$M$24, IF(AND(F14>=$N$25,F14<=$O$25),$M$25, IF(AND(F14>=$N$26,F14<=$O$26),$M$26, IF(AND(F14>=$N$27,F14<=$O$27),$M$27, IF(AND(F14>=$N$28,F14<=$O$28),$M$28,"*"))))))))
  • F14 = Subject का प्राप्त Marks
  • $N$21 और $O$21 = Grade Lookup Table की Min और Max values ($ = Absolute Reference)
  • $M$21 = Grade का Label (A+, A, B+ आदि)
  • हर IF condition एक Grade range check करती है और उससे match होने पर उस Grade का label return करती है।
  • अंत में "*" default value है अगर कोई भी condition match न हो।
📋 Copy करने का तरीका

H14 में formula enter करने के बाद, इसे H15:H19 तक copy करें। चूँकि F14, F15, F16... row-wise change होगा और $N$21 आदि absolute हैं, formula automatically सही काम करेगा।

✍️ Overall Grade (Cell D22)

Overall Grade Average Marks के आधार पर calculate होगी। D22 में यह formula डालें:

Overall Grade Formula
=IF(AND(AVERAGE(F14:F19)>=$N$21,AVERAGE(F14:F19)<=$O$21),$M$21, IF(AND(AVERAGE(F14:F19)>=$N$22,AVERAGE(F14:F19)<=$O$22),$M$22, IF(AND(AVERAGE(F14:F19)>=$N$23,AVERAGE(F14:F19)<=$O$23),$M$23, IF(AND(AVERAGE(F14:F19)>=$N$24,AVERAGE(F14:F19)<=$O$24),$M$24, IF(AND(AVERAGE(F14:F19)>=$N$25,AVERAGE(F14:F19)<=$O$25),$M$25, IF(AND(AVERAGE(F14:F19)>=$N$26,AVERAGE(F14:F19)<=$O$26),$M$26, IF(AND(AVERAGE(F14:F19)>=$N$27,AVERAGE(F14:F19)<=$O$27),$M$27, IF(AND(AVERAGE(F14:F19)>=$N$28,AVERAGE(F14:F19)<=$O$28),$M$28,"*"))))))))
8

Step 8: Division / Result Formula

Division calculate करने के लिए Average Percentage को Division Table से compare किया जाएगा।

✍️ Division Formula (Cell D21)

Division / Result Formula
=IF((AVERAGE(F14:F19)/100) >= M13, $O$13, IF(AND(AVERAGE(F14:F19)/100 >= M14, AVERAGE(F14:F19)/100 <= N14), $O$14, IF(AND(AVERAGE(F14:F19)/100 >= M15, AVERAGE(F14:F19)/100 <= N15), $O$15, IF(AVERAGE(F14:F19)/100 >= N16, "Fail", ""))))
  • AVERAGE(F14:F19)/100 = Average Percentage (decimal में)
  • M13 = 0.60 (1st Division का minimum)
  • $O$13 = "1st Division" label
  • अगर Average ≥ 60% → 1st Division
  • अगर 50% ≤ Average ≤ 59% → 2nd Division
  • अगर 33% ≤ Average ≤ 49% → 3rd Division
  • अगर Average < 33% → Fail
💡
Note: Division Table के cells (M13:N16) में actual percentage values हैं (0.60, 0.50, आदि)। इसलिए formula में AVERAGE को 100 से divide किया जा रहा है ताकि दोनों same unit में हों।
9

Step 9: VBA Print Button बनाएं

अब हम एक VBA CommandButton बनाएंगे जो Print Preview launch करेगा और Marksheet का Print Area automatically set करेगा।

🔧 Button Insert करना

📌 Steps

1. Developer Tab → Insert → ActiveX Controls → Command Button
2. Marksheet Sheet पर button draw करें (Column M-N, Row 6 के आसपास)
3. Button पर Right Click → Properties → Caption: "Print Preview"
4. BackColor: Green (&H0000FF00&)
5. Design Mode बंद करें

💡
Developer Tab नहीं दिख रहा? File → Options → Customize Ribbon → Right Side में "Developer" checkbox check करें → OK।

✍️ VBA Code – Sheet2 (Marksheet)

Marksheet Sheet tab पर Right Click → View Code करें और निम्नलिखित code paste करें:

' ============================================ ' CommandButton1 Click Event – Print Preview ' ============================================ Private Sub CommandButton1_Click()With ActiveSheet.PageSetup ' Print Area Set करें .PrintArea = "$B$3:$I$31" ' Paper Size – A4 .PaperSize = xlPaperA4 ' Margins (Centimeters में) .TopMargin = Application.CentimetersToPoints(1) .BottomMargin = Application.CentimetersToPoints(1) .LeftMargin = Application.CentimetersToPoints(1.5) .RightMargin = Application.CentimetersToPoints(1.5) ' Page Center करें .CenterHorizontally = True .CenterVertically = True End With ' Print Preview दिखाएं (Direct Print नहीं) ActiveWindow.SelectedSheets.PrintPreview End Sub' ============================================ ' Worksheet Change Event – Button Color Change ' ============================================ Private Sub Worksheet_Change(ByVal Target As Range)' अगर N5 (Roll Number cell) change हो If Not Intersect(Target, Range("N5")) Is Nothing Then If Range("N5").Value <> "" Then ' Roll Number है तो Button Green करें CommandButton1.BackColor = RGB(0, 180, 80) Else ' Roll Number नहीं है तो Button Gray करें CommandButton1.BackColor = RGB(200, 200, 200) End If End If End Sub
✨
Smart Color Change: जब N5 में Roll Number डाला जाएगा, Button का color automatically Green हो जाएगा — यह User को indicate करता है कि Print करना safe है। जब N5 clear होगा, Button Gray हो जाएगा।

✍️ N5 Clear करने के लिए (Optional) – ThisWorkbook

अगर आप चाहते हैं कि Workbook open होने पर N5 automatically clear हो जाए, तो ThisWorkbook module में यह code add करें:

Private Sub Workbook_Open() ' File open होने पर Roll Number cell clear करें Sheets("Marksheet").Range("N5").ClearContents End Sub
10

Step 10: Professional Formatting

Marksheet को professional look देने के लिए निम्नलिखित formatting करें:

🎨 Cells को Format करें

AreaFormatting
School Name (B3)Font Size 16-18, Bold, Center Align, Blue Color
Headers (B13:H13)Bold, Background Color: Dark Blue, Font: White
Subject rows (B14:H19)Alternating Row Colors (Light Blue / White)
N5 (Input Cell)Yellow Background (#FFFF00), Bold Border
Grade ColumnBold, Center Align
Total/Percentage (H21:H22)Bold, Border

🔒 Cells Lock करें (Protection)

📌 Steps

1. पहले Ctrl+A से सभी cells select करें → Ctrl+1 → Protection Tab → "Locked" uncheck करें।
2. अब सिर्फ N5 (Roll Number Input) को select करें → Locked check करें।
3. Review Tab → Protect Sheet → Password set करें → OK।

अब सिर्फ N5 cell में input हो सकेगा, बाकी cells protected रहेंगे।

📝 Footer Notes

Marksheet के नीचे (Rows 29-31) ये notes add करें:

📌 Footer Text

B29: "Grade Rule: 91 to 100 – A+, 81 to 90 – A, 71 to 80 – B+, 61 to 70 – B, 51 to 60 – C+, 41 to 50 – C, 33 to 40 – D, 0 to 32 – E(Fail)"

B30: "Percentage Rule: Above 60% – 1st Division, 50% to 59% – 2nd Division, 33% to 49% – 3rd Division, Below 33% – Fail"

B31: "* This report is computer generated, hence does not require signature."

🎉 बधाई हो! आपकी Marksheet तैयार है!

आपने अब एक fully automatic, professional Excel Marksheet बना ली है जिसमें:

✅ XLOOKUP से Auto Data Fetch  |  ✅ Nested IF Grade Formula  |  ✅ Automatic Division Calculation  |  ✅ VBA Print Button

इस tutorial को Like करें और अपने Teacher/School friends के साथ share करें!

#Excel Hindi Tutorial #Marksheet in Excel #XLOOKUP Formula #VBA Macro Excel #Nested IF Formula #Grade System Excel #School Marksheet #Excel for Teachers #TheOfficeTutorials #Annual Examination #Excel Tips Hindi