آموزش تابع INDEX در اکسل

معرفی تابع INDEX

تابع INDEX در اکسل برای برگرداندن مقدار یک سلول از داخل یک محدوده (Range) یا یک آرایه (Array) بر اساس شماره سطر و شماره ستون استفاده می‌شود. یعنی شما به INDEX می‌گویید «از این جدول، مقدارِ سطر X و ستون Y را بده» و اکسل همان مقدار را برمی‌گرداند.

INDEX یکی از مهم‌ترین توابع اکسل برای گزارش‌گیری و ساخت داشبورد است، چون:

• به جای جستجوی مستقیم با VLOOKUP، می‌تواند با ترکیب MATCH بسیار انعطاف‌پذیرتر باشد.
• روی جدول‌های افقی و عمودی به یک شکل کار می‌کند.
• می‌تواند مقدار را از یک محدوده برگرداند و در فرمول‌های دیگر استفاده شود.

مثال ساده فارسی: فرض کنید در محدوده A2:C6 یک جدول داریم و می‌خواهیم مقدارِ «سطر 3» و «ستون 2» این محدوده را بگیریم.

=INDEX(A2:C6;3;2)

خروجی این فرمول مقدار سلولی است که در تقاطع سطر سوم و ستون دوم از محدوده A2:C6 قرار دارد (یعنی معادل B4 در شیت).

کاربردهای اصلی تابع INDEX

• استخراج مقدار از جدول بر اساس شماره سطر و ستون
• ساخت جستجوهای پیشرفته با ترکیب INDEX + MATCH (جایگزین حرفه‌ای VLOOKUP)
• جستجوی دوطرفه (Two-way lookup): پیدا کردن مقدار بر اساس نام سطر و نام ستون
• ساخت گزارش‌های پویا (با انتخاب کاربر از لیست کشویی)
• برگرداندن یک «محدوده» برای استفاده داخل توابعی مثل SUM (در سناریوهای خاص)
• کار با آرایه‌ها و فرمول‌های داینامیک در نسخه‌های جدید اکسل

ساختار (Syntax)

فرم عمومی:

=INDEX(argument1, argument2, ...)

فرم رایج (آرایه/محدوده):

=INDEX(آرایه_یا_محدوده;شماره_سطر;[شماره_ستون])

نکته: در اکسل دو شکل برای INDEX وجود دارد (Array و Reference). در کارهای روزمره، 90٪ مواقع همان فرم «آرایه/محدوده» استفاده می‌شود که در این مقاله تمرکز اصلی روی همان است.

آرگومان‌ها

array (آرایه یا محدوده) / محدوده‌ای که می‌خواهیم از آن مقدار برگردد

این آرگومان همان جدول یا محدوده‌ای است که از داخل آن خروجی می‌خواهید. مثلاً A2:C6.

row_num (شماره سطر) / شماره ردیف داخل محدوده

شماره سطر داخل محدوده را مشخص می‌کند (از 1 شروع می‌شود). اگر محدوده شما A2:C6 باشد، row_num=1 یعنی سطر A2:C2.

column_num (شماره ستون) / شماره ستون داخل محدوده (اختیاری در محدوده‌های تک‌ستونه)

شماره ستون داخل محدوده را مشخص می‌کند (از 1 شروع می‌شود). اگر محدوده شما چندستونه باشد، معمولاً باید آن را وارد کنید. اگر محدوده یک ستون باشد (مثلاً A2:A10) می‌توانید column_num را ندهید.

مثال‌های ساده و پایه

مثال 1: برگرداندن مقدار یک سلول از جدول

فرض کنید داده‌ها در A2:C6 هستند و می‌خواهیم مقدار سطر 2 و ستون 3 را برگردانیم.

=INDEX(A2:C6;2;3)

نتیجه: مقدارِ خانه‌ای که در محدوده A2:C6، سطر دوم و ستون سوم قرار دارد (معادل C3 در شیت).

مثال 2: محدوده تک‌ستونه (بدون شماره ستون)

فرض کنید لیست نام‌ها در A2:A10 است و می‌خواهیم نامِ پنجم را برگردانیم.

=INDEX(A2:A10;5)

نتیجه: مقدار سلول پنجم در این محدوده (معادل A6).

مثال 3: شماره سطر از یک سلول (پویا کردن خروجی)

فرض کنید شماره سطر دلخواه را در E2 وارد می‌کنید و می‌خواهید از جدول A2:C6 ستون دوم را برگردانید.

=INDEX(A2:C6;E2;2)

نتیجه: با تغییر عدد E2، خروجی هم تغییر می‌کند.

مثال‌های کاربردی و واقعی

مثال 1: جایگزین حرفه‌ای VLOOKUP با INDEX + MATCH

سناریو: در ستون A کد کالا و در ستون B قیمت داریم (A2:B100). می‌خواهیم قیمتِ کدِ موجود در E2 را برگردانیم.

=INDEX(B2:B100;MATCH(E2;A2:A100;0))

مزیت: برخلاف VLOOKUP، محدود به این نیست که ستون کلید حتماً سمت چپ باشد؛ همچنین در جدول‌های بزرگ، انعطاف بیشتری دارد.

مثال 2: جستجوی دوطرفه (پیدا کردن مقدار با نام ردیف و نام ستون)

سناریو: یک جدول فروش داریم که سرفصل ماه‌ها در B1:M1 و نام محصول‌ها در A2:A20 است و اعداد فروش داخل B2:M20. کاربر نام محصول را در P2 و نام ماه را در Q2 انتخاب می‌کند. مقدار فروش همان تقاطع را می‌خواهیم.

=INDEX(B2:M20;MATCH(P2;A2:A20;0);MATCH(Q2;B1:M1;0))

نتیجه: دقیقاً فروشِ محصول انتخاب‌شده در ماه انتخاب‌شده.

مثال 3: استفاده با SUM (جمع یک ستون مشخص که با INDEX انتخاب می‌شود)

سناریو: در جدول B2:M20 ستون‌ها ماه‌ها هستند. کاربر ماه را در Q2 انتخاب می‌کند و می‌خواهیم جمع فروش همان ماه را برای همه محصولات حساب کنیم.

=SUM(INDEX(B2:M20;0;MATCH(Q2;B1:M1;0)))

توضیح: وقتی row_num را 0 می‌گذارید، INDEX کل ستونِ انتخاب‌شده را برمی‌گرداند و SUM آن را جمع می‌کند (این رفتار در بسیاری از نسخه‌ها کار می‌کند؛ اگر در فایل شما مشکل داشت، از روش‌های جایگزین مثل SUMIFS یا SUMPRODUCT استفاده کنید).

مثال 4: ترکیب با COUNTIF برای پیدا کردن «n-اُمین» مورد مطابق شرط

سناریو: لیست مشتریان در A2:A200 است. می‌خواهیم فقط مشتریانی که نامشان «علی» است را یکی‌یکی استخراج کنیم. در E2 عدد 1، در E3 عدد 2 و … می‌گذاریم تا n-اُمین «علی» برگردد.

=INDEX(A2:A200;SMALL(IF(A2:A200="علی";ROW(A2:A200)-ROW(A2)+1);E2))

نکته: این فرمول آرایه‌ای است. در نسخه‌های جدید (Microsoft 365) معمولاً مستقیم کار می‌کند؛ در نسخه‌های قدیمی ممکن است نیاز به ورود آرایه‌ای داشته باشد (Ctrl+Shift+Enter).

مثال 5: ترکیب با OR و AND (کنترل شرط قبل از برگرداندن نتیجه)

سناریو: اگر «شهر = تهران» و «وضعیت = فعال» بود، نام را از لیست برگردان، وگرنه خالی بگذاریم. فرض کنید نام‌ها در A2:A100، شهر در B2:B100 و وضعیت در C2:C100 است و شماره ردیف مدنظر در E2 آمده.

=IF(AND(INDEX(B2:B100;E2)="تهران";INDEX(C2:C100;E2)="فعال");INDEX(A2:A100;E2);"")

نتیجه: خروجی فقط وقتی شرایط برقرار باشد نمایش داده می‌شود.

مثال 6: ترکیب با XLOOKUP (وقتی می‌خواهید محدوده خروجی پویا شود)

سناریو: نام ماه را داریم و می‌خواهیم ستون متناظر آن ماه را از یک جدول انتخاب کنیم. XLOOKUP شماره ستون را می‌دهد، INDEX ستون را برمی‌گرداند (برای استفاده در محاسبات بعدی).

=INDEX(B2:M20;0;XLOOKUP(Q2;B1:M1;SEQUENCE(1;COLUMNS(B1:M1));0))

این روش در نسخه‌های جدید عالی است چون SEQUENCE و XLOOKUP را نیاز دارد.

ترکیب تابع INDEX با فرمول‌های دیگر

• INDEX + MATCH برای جستجوی دقیق و انعطاف‌پذیر

  =INDEX(C2:C200;MATCH(G2;A2:A200;0))

• INDEX + MATCH + MATCH برای جستجوی دو بعدی

  =INDEX(B2:M20;MATCH(P2;A2:A20;0);MATCH(Q2;B1:M1;0))

• SUM + INDEX برای جمع ستون/ردیف انتخابی

  =SUM(INDEX(B2:M20;0;3))

• IF + INDEX برای شرطی کردن خروجی

  =IF(F2="";"";INDEX(D2:D100;F2))

• IFERROR + INDEX برای جلوگیری از خطا و نمایش پیام دلخواه

  =IFERROR(INDEX(B2:B100;MATCH(E2;A2:A100;0));"یافت نشد")

• INDEX + LARGE/SMALL برای پیدا کردن n-اُمین مقدار بر اساس ترتیب

  =INDEX(A2:A100;MATCH(LARGE(C2:C100;1);C2:C100;0))

خطاهای رایج و روش رفع آنها

1) خطای #REF!

علت رایج: شماره سطر یا ستون خارج از محدوده وارد شده است (مثلاً row_num=10 ولی محدوده فقط 5 سطر دارد).

راه‌حل: تعداد سطرها و ستون‌های محدوده را بررسی کنید. اگر شماره‌ها از سلول می‌آیند، مطمئن شوید مقدارشان از 1 تا حداکثر مجاز است.

2) خطای #VALUE!

علت رایج: شماره سطر/ستون عددی نیست (مثلاً متن یا مقدار خالی نامناسب)، یا در برخی سناریوها آرگومان‌ها ناسازگار هستند.

راه‌حل: مطمئن شوید row_num و column_num عدد هستند. اگر از سلول می‌خوانید، با ISNUMBER کنترل کنید یا با VALUE تبدیل انجام دهید.

3) خروجی اشتباه (ولی بدون خطا)

علت رایج: محدوده را اشتباه انتخاب کرده‌اید (مثلاً یک ردیف/ستون کم یا زیاد)، یا row_num/column_num را نسبت به «داخل محدوده» اشتباه در نظر گرفته‌اید.

راه‌حل: یادآوری مهم: INDEX داخل محدوده از 1 شروع می‌کند. برای تست، یکبار محدوده را هایلایت کنید و تقاطع سطر/ستون را دستی چک کنید.

4) مشکل جداکننده آرگومان‌ها (ویرگول/نقطه‌ویرگول)

علت رایج: تنظیمات منطقه‌ای (Regional) اکسل شما جداکننده را «;» قرار داده اما فرمول با «,» نوشته شده (یا برعکس).

راه‌حل: در اکسل فارسی معمولاً «;» درست است. فرمول را با جداکننده صحیح بازنویسی کنید.

5) MATCH مقدار پیدا نمی‌کند و INDEX هم خطا می‌دهد

علت رایج: در ترکیب INDEX+MATCH، اگر MATCH چیزی پیدا نکند خطای #N/A تولید می‌شود و INDEX هم نتیجه نمی‌دهد.

راه‌حل: از IFERROR استفاده کنید و همچنین مطمئن شوید نوع داده‌ها یکی است (عدد با عدد، متن با متن) و فاصله‌های اضافی وجود ندارد.

=IFERROR(INDEX(B2:B100;MATCH(E2;A2:A100;0));"یافت نشد")

نکات حرفه‌ای و ترفندهای مهم

• برای خوانایی، محدوده‌ها را نام‌گذاری کنید (Named Range). مثال: SalesTable به جای B2:M20؛ فرمول‌ها بسیار قابل فهم‌تر می‌شوند.
• INDEX + MATCH را به جای VLOOKUP یاد بگیرید چون محدودیت «ستون کلید باید سمت چپ باشد» را ندارد.
• جستجوی دوطرفه را استاندارد کنید: یک MATCH برای ردیف‌ها، یک MATCH برای ستون‌ها.
• کنترل خطا را فراموش نکنید: در گزارش‌ها بهتر است خروجی تمیز باشد.

  =IFERROR(INDEX(B2:B100;MATCH(E2;A2:A100;0));"")

• در نسخه‌های جدید از توابع داینامیک کمک بگیرید مثل XLOOKUP، FILTER، SEQUENCE تا فرمول‌ها کوتاه‌تر و منعطف‌تر شوند.
• اگر نیاز به «ارجاع پویا» دارید، INDEX می‌تواند جایگزین OFFSET باشد چون معمولاً سبک‌تر و پایدارتر است (OFFSET فرّار/Volatile است و می‌تواند فایل را کند کند).

تفاوت تابع INDEX با توابع مشابه

• INDEX vs VLOOKUP
  • VLOOKUP فقط از چپ به راست می‌گردد، INDEX+MATCH در هر جهتی کار می‌کند.
  • در تغییر ساختار جدول (اضافه/کم شدن ستون‌ها)، INDEX+MATCH معمولاً مقاوم‌تر است.
• INDEX vs XLOOKUP
  • XLOOKUP برای «جستجو» ساده‌تر است و قابلیت‌های بیشتری مثل جستجوی تقریبی/معکوس دارد.
  • INDEX وقتی عالی است که «محل» را از قبل دارید (شماره سطر/ستون) یا می‌خواهید جستجوی دوطرفه بسازید.
• INDEX vs OFFSET
  • OFFSET محدوده جابجا شده برمی‌گرداند ولی فرّار است و می‌تواند سرعت را کم کند.
  • INDEX در بسیاری از سناریوهای ارجاع پویا، عملکرد بهتر و پایدارتری دارد.
• INDEX vs INDIRECT
  • INDIRECT با متن کار می‌کند و به آدرس متنی تبدیل می‌شود؛ اما با تغییر نام شیت/فایل ممکن است شکننده شود.
  • INDEX ارجاع مستقیم و امن‌تری به محدوده‌ها می‌دهد.

سازگاری با نسخه‌های مختلف اکسل

• INDEX از نسخه‌های بسیار قدیمی اکسل تا جدیدترین نسخه‌ها پشتیبانی می‌شود (Excel 2007, 2010, 2013, 2016, 2019, 2021 و Microsoft 365).
• خود INDEX مشکلی از نظر نسخه ندارد، اما توابع ترکیبی ممکن است وابسته به نسخه باشند:
  • XLOOKUP، FILTER، SEQUENCE مخصوص Microsoft 365 و Excel 2021+ هستند.
  • فرمول‌های آرایه‌ای در نسخه‌های قدیمی ممکن است نیاز به Ctrl+Shift+Enter داشته باشند.

سؤالات پرتکرار درباره تابع INDEX

آیا INDEX فقط یک مقدار برمی‌گرداند؟

در حالت معمول یک مقدار برمی‌گرداند، اما در بعضی کاربردها می‌تواند یک ردیف یا ستون (محدوده) برگرداند که داخل توابعی مثل SUM استفاده می‌شود.

شماره سطر و ستون در INDEX از چند شروع می‌شود؟

از 1 (نه صفر). یعنی row_num=1 اولین سطرِ همان محدوده است.

آیا INDEX از VLOOKUP بهتر است؟

برای کارهای حرفه‌ای و جدول‌های پویا معمولاً بله، چون محدودیت‌های VLOOKUP را ندارد و انعطاف بیشتری می‌دهد (به‌خصوص با MATCH).

اگر MATCH چیزی پیدا نکند چه می‌شود؟

MATCH خطای #N/A می‌دهد. معمولاً آن را با IFERROR مدیریت می‌کنند تا پیام مناسب نمایش داده شود.

برای جستجوی دوطرفه بهترین روش چیست؟

INDEX به همراه دو MATCH (یکی برای سطر، یکی برای ستون) استانداردترین و قابل‌اعتمادترین روش است.

جمع‌بندی و پیشنهاد یادگیری بعدی

تابع INDEX یک ابزار کلیدی برای استخراج مقدار از جدول‌هاست و وقتی با MATCH ترکیب می‌شود، به یکی از قدرتمندترین روش‌های جستجو در اکسل تبدیل می‌گردد. با INDEX می‌توانید جستجوهای دقیق، دوطرفه، و گزارش‌های پویا بسازید و در کنار توابعی مثل IFERROR، SUM و حتی XLOOKUP خروجی‌های حرفه‌ای‌تری تولید کنید.

پیشنهاد یادگیری بعدی:

• تابع MATCH (برای تکمیل INDEX+MATCH)
• تابع XLOOKUP (روش مدرن جستجو)
• تابع FILTER (فیلتر کردن داده‌ها در نسخه‌های جدید)
• مفهوم Named Range و ساخت جدول با Format as Table (برای فرمول‌های خواناتر و پایدارتر)