نحوه استفاده از تابع INDEX و MATCH در اکسل

2024 نویسنده: Abigail Brown | [email protected]. آخرین اصلاح شده: 2023-12-17 06:46

چه باید بدانیم

• تابع INDEX را می توان به تنهایی استفاده کرد، اما تودرتو کردن تابع MATCH در داخل آن یک جستجوی پیشرفته ایجاد می کند.
• این تابع تو در تو نسبت به VLOOKUP انعطاف پذیرتر است و می تواند سریعتر نتیجه دهد.

این مقاله نحوه استفاده از توابع INDEX و MATCH را در همه نسخه‌های اکسل، از جمله Excel 2019 و Microsoft 365 توضیح می‌دهد.

توابع INDEX و MATCH چیست؟

INDEX و MATCH توابع جستجوی اکسل هستند. در حالی که آنها دو تابع کاملا مجزا هستند که می توانند به تنهایی مورد استفاده قرار گیرند، همچنین می توان آنها را برای ایجاد فرمول های پیشرفته ترکیب کرد.

تابع INDEX یک مقدار یا ارجاع به یک مقدار را از داخل یک انتخاب خاص برمی گرداند. برای مثال، می‌توان از آن برای یافتن مقدار در ردیف دوم یک مجموعه داده یا در ردیف پنجم و ستون سوم استفاده کرد.

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

INDEX and MATCH syntax & Arguments

برای درک اکسل هر دو تابع باید به این ترتیب نوشته شوند:

=INDEX(آرایه, ردیف_شماره, [ستون_شماره])

• آرایه محدوده سلول هایی است که فرمول استفاده می کند. این می تواند یک یا چند ردیف و ستون باشد، مانند A1:D5. لازم است.
• row_num ردیفی در آرایه است که از آن مقداری مانند 2 یا 18 برمی گردد. این مورد ضروری است مگر اینکه ستون_num وجود داشته باشد.
• column_num ستونی در آرایه است که از آن مقداری مانند 1 یا 9 برمی گردد. اختیاری است.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value مقداری است که می‌خواهید در آرایه_نمایش مطابقت دهید. این می تواند یک عدد، متن یا مقدار منطقی باشد که به صورت دستی تایپ می شود یا از طریق یک مرجع سلول به آن ارجاع می شود. این لازم است.
• lookup_array محدوده سلول‌هایی است که باید از طریق آن نگاه کرد. این می تواند یک ردیف یا یک ستون باشد، مانند A2:D2 یا G1:G45. این لازم است.
• match_type می تواند -1، 0، یا 1 باشد. مشخص می کند که چگونه lookup_value با مقادیر در lookup_array مطابقت داده می شود (به زیر مراجعه کنید). اگر این آرگومان حذف شود، 1 مقدار پیش‌فرض است.

از کدام نوع تطابق استفاده شود
نوع مطابقت	چه کار می کند	قانون	مثال
1	بزرگترین مقدار را می یابد که کمتر یا مساوی با lookup_value است.	مقادیر آرایه_جستجو باید به ترتیب صعودی قرار گیرند (به عنوان مثال، -2، -1، 0، 1، 2؛ یا A-Z؛، یا FALSE، TRUE.	lookup_value 25 است، اما از آرایه_پیمایش غایب است، بنابراین موقعیت کوچکترین عدد بعدی، مانند 22، به جای آن برگردانده می شود.
0	اولین مقداری را که دقیقاً برابر با lookup_value است را پیدا می کند.	مقادیر آرایه_جستجو می تواند به هر ترتیبی باشد.	lookup_value 25 است، بنابراین موقعیت 25 را برمی گرداند.
-1	کوچکترین مقدار را که بزرگتر یا مساوی با lookup_value است را پیدا می کند.	مقادیر آرایه_جستجو باید به ترتیب نزولی قرار گیرند (به عنوان مثال، 2، 1، 0، -1، -2).	lookup_value 25 است اما در آرایه_پیش‌بینی وجود ندارد، بنابراین موقعیت بزرگترین عدد بعدی، مانند 34، به جای آن برگردانده می‌شود.

برای مواقعی که نیاز به جستجوی تقریبی در یک مقیاس دارید، از 1 یا -1 استفاده کنید، مانند زمانی که با اعداد سروکار دارید و زمانی که تقریب ها مشکلی ندارند. اما به یاد داشته باشید که اگر match_type را مشخص نکنید، 1 پیش‌فرض خواهد بود، که اگر واقعاً می‌خواهید مطابقت دقیق داشته باشید، می‌تواند نتایج را تغییر دهد.

نمونه فرمول های INDEX و MATCH

قبل از اینکه به نحوه ترکیب INDEX و MATCH در یک فرمول نگاه کنیم، باید بدانیم که چگونه این توابع به تنهایی کار می کنند.

نمونه‌های INDEX

=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEX(B1:B2, 1)





در این مثال اول، چهار فرمول INDEX وجود دارد که می توانیم از آنها برای بدست آوردن مقادیر مختلف استفاده کنیم:

=INDEX(A1:B2, 2, 2) از طریق A1:B2 نگاه می کند تا مقدار را در ستون دوم و ردیف دوم پیدا کند که Stacy است.
=INDEX(A1:B1, 1) از طریق A1:B1 نگاه می کند تا مقدار را در ستون اول پیدا کند که Jon است.
=INDEX(2:2, 1) همه چیز را در ردیف دوم بررسی می کند تا مقدار ستون اول را که Tim است، پیدا کند.
=INDEX(B1:B2, 1) از طریق B1:B2 نگاه می کند تا مقدار را در ردیف اول قرار دهد، که Amy است.

مثالهای MATCH
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)





در اینجا چهار مثال ساده از تابع MATCH آورده شده است:

=MATCH("Stacy", A2:D2, 0) در حال جستجوی Stacy در محدوده A2:D2 است و 3 را به عنوان نتیجه برمی گرداند.
=MATCH(14, D1:D2) 14 را در محدوده D1:D2 جستجو می کند، اما از آنجایی که در جدول یافت نمی شود، MATCH بزرگترین مقدار بعدی را پیدا می کند. این کمتر یا مساوی 14 است که در این مورد 13 است که در موقعیت 1 از lookup_array است.
=MATCH(14, D1:D2, -1) با فرمول بالای آن یکسان است، اما از آنجایی که آرایه به ترتیب نزولی نیست مانند -1، ما یک خطا دریافت می کنیم.
=MATCH(13, A1:D1, 0) به دنبال 13 در ردیف اول برگه است که 4 را برمی گرداند زیرا چهارمین مورد در این آرایه است.


نمونه‌های INDEX-MATCH
در اینجا دو مثال وجود دارد که می توانیم INDEX و MATCH را در یک فرمول ترکیب کنیم:
پیدا کردن مرجع سلول در جدول
=INDEX(B2:B5, MATCH(F1, A2:A5))






این مثال فرمول MATCH را در فرمول INDEX قرار می دهد. هدف شناسایی رنگ مورد با استفاده از شماره مورد است.
اگر به تصویر نگاه کنید، می توانید در ردیف های "جدا شده" ببینید که چگونه فرمول ها به تنهایی نوشته می شوند، اما از آنجایی که ما آنها را تودرتو می کنیم، این چیزی است که اتفاق می افتد:

MATCH(F1, A2:A5) به دنبال مقدار F1 (8795) در مجموعه داده A2:A5 است. اگر ستون را شمارش معکوس کنیم، می‌توانیم ببینیم که 2 است، بنابراین تابع MATCH همین را فهمید.
آرایه INDEX B2:B5 است زیرا ما در نهایت به دنبال مقدار در آن ستون هستیم.
تابع INDEX اکنون می تواند به این شکل بازنویسی شود زیرا 2 همان چیزی است که MATCH یافت: INDEX(B2:B5, 2, [column_num]).
از آنجایی که column_num اختیاری است، می‌توانیم آن را با این حذف کنیم: INDEX(B2:B5, 2).
بنابراین اکنون، این مانند یک فرمول INDEX معمولی است که در آن ما مقدار مورد دوم را در B2:B5 پیدا می کنیم که قرمز است.

جستجو بر اساس عناوین ردیف و ستون
=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))






در این مثال از MATCH و INDEX، ما یک جستجوی دوطرفه انجام می دهیم. ایده این است که ببینیم در ماه می چقدر از اقلام سبز درآمدیم. این واقعاً شبیه مثال بالا است، اما یک فرمول MATCH اضافی در INDEX تو در تو است.

MATCH(G1, A2:A13, 0) اولین موردی است که در این فرمول حل شد. این به دنبال G1 (کلمه "مه") در A2:A13 برای بدست آوردن یک مقدار خاص است. ما اینجا آن را نمی بینیم، اما 5 است.
MATCH(G2, B1:E1, 0) دومین فرمول MATCH است و واقعاً شبیه فرمول اول است اما در عوض به دنبال G2 است (کلمه "سبز") در عناوین ستون در B1:E1. این یکی به 3 حل می شود.
اکنون می‌توانیم فرمول INDEX را به این شکل بازنویسی کنیم تا آنچه را که اتفاق می‌افتد تجسم کنیم: =INDEX(B2:E13, 5, 3). این در کل جدول، B2:E13، به دنبال ردیف پنجم و ستون سوم است که 180 دلار را برمی‌گرداند.


MATCH and INDEX Rules
هنگام نوشتن فرمول با این توابع چندین نکته وجود دارد که باید در نظر داشته باشید:

MATCH به حروف بزرگ و کوچک حساس نیست، بنابراین هنگام تطبیق مقادیر متن با حروف بزرگ و کوچک یکسان رفتار می شود.
MATCH به دلایل متعدد، N/A را برمی‌گرداند: اگر match_type 0 است و مقدار_پیوندی یافت نشد اگر match_type -1 است و آرایه_ lookup به ترتیب نزولی نیست، اگر match_type 1 است و آرایه_ lookup به صورت صعودی نیست. ترتیب، و اگر lookup_array یک ردیف یا ستون نیست.
اگر match_type 0 است و lookup_value یک رشته متن است، می توانید از یک کاراکتر عام در آرگومان lookup_value استفاده کنید. یک علامت سوال با هر کاراکتر و یک ستاره با هر دنباله ای از کاراکترها مطابقت دارد (مثلاًg.، =MATCH("Jo"، 1:1، 0)). برای استفاده از MATCH برای یافتن علامت سوال یا ستاره واقعی، ابتدا ~ را تایپ کنید.
INDEX REF را برمی گرداند! اگر row_num و column_num به سلولی در آرایه اشاره نکنند.


توابع مرتبط اکسل
عملکرد MATCH شبیه LOOKUP است، اما MATCH موقعیت مورد را به جای خود مورد برمی گرداند.
VLOOKUP یکی دیگر از تابع های جستجو است که می توانید در اکسل استفاده کنید، اما برخلاف MATCH که برای جستجوهای پیشرفته به INDEX نیاز دارد، فرمول های VLOOKUP فقط به آن یک تابع نیاز دارند.