ترکیب دو تابع INDEX و MATCH در اکسل

ترکیب دو تابع INDEX و MATCH در اکسل

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

A B
1 کد محصول نام محصول
2 1282 قطعه شماره 1
3 1464 قطعه شماره 2
4 1398 قطعه شماره 3
5 1339 قطعه شماره 4
6 1286 قطعه شماره 5

اگر بخوایم در این جدول بر اساس جستجوی کد محصول 1398، نام محصول رو بدست بیاریم میتونیم از تابع VLOOKUP که قبلاً در موردش نوشتم به شکل (VLOOKUP(1398;A:B;2;FALSE استفاده کنیم. اما اگر جدول ما مثل جدول زیر باشه:

A B
1 نام محصول کد محصول
2 قطعه شماره 1 1282
3 قطعه شماره 2 1464
4 قطعه شماره 3 1398
5 قطعه شماره 4 1339
6 قطعه شماره 5 1286

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

  1. ابتدا تابع INDEX رو مینویسیم و پارامتر اول اون رو A:A قرار میدیم، چون میخوایم نام محصول رو مشخص کنیم و نام محصول هم در ستون A قرار داره.
  2. در حالت عادی میتونستیم شماره سطر رو به تابع INDEX بدیم. شماره سطر عدد 1398 برابر 4 هست اما ما نمیخوایم این شماره سطر رو دستی وارد کنیم. بلکه میخوایم شماره سطر به صورت خودکار توسط اکسل پیدا بشه. اینکار رو با تابع MATCH انجام میدیم. تابع (MATCH(1398;B:B;0 دقیقاً اینکار رو برای ما انجام میده. یعنی عدد 1398 رو در ستون B پیدا میکنه و شماره سطر اون رو که برابر 4 هست برمیگردونه.
  3. در نهایت تابع بخش 2 رو در تابع INDEX خودمون قرار میدیم تا تابعمون به شکل زیر کامل بشه.

INDEX(A:A;MATCH(1398;B:B;0))

میتونیم به جای عدد 1398 یک سلول یا هر کد دیگه‌ای رو قرار بدیم و تابع بالا نام محصول متناظر با اون کد رو برای ما پیدا میکنه.

 

 

99 Comments on “ترکیب دو تابع INDEX و MATCH در اکسل”

  1. با سلام و احترام
    من جدولی دارم که 2 ستون دارد و در ستون A نام افراد و در ستون B کد هر فرد نوشته شده است و میخواهم تابعی بنویسم که در سلول D2 نام یک فرد را در ستون A که دارای 200 ردیف هست پیدا کنه و کد متعلق به اون فرد که در ستون B نوشته شده را در سلول E2 بنویسه از Search و Lookup هم استفاده کردم نتوانستم لطفا راهنمایی بفرمایید متشکرم

  2. با سلام
    ضمنا در خصوص مطالب فوق ممکن است نام تکراری هم داشته باشم ولی کد آن فرق کند و میخواهم تابع مربوطه هر 2 یا 3 مورد را نشان دهد
    متشکرم

  3. سلام به شما آقای یاسین اسماعیل پور!
    مشکل در قسمت رپلاس یک ستون کود از به کود جدید هست با استفاده از چه فورمل پیش برم
    property_physical_location/nahia_id property_physical_location/guzar_id property_physical_location/block_id property_physical_location/parcel_no property_physical_location/unit_no
    04 04 002 002 001
    04 04 003 001 001
    04 04 003 003 001
    04 04 003 004 001
    04 04 003 005 001
    04 04 003 006 001
    04 04 003 011 001
    04 04 003 013 001
    04 04 004 001 001
    همین کود بالا به کود جدید پایین تبدیل نمایم همه چیز در جایش برود تغییر نکند
    New_Code Block new parcel
    10-01-04-04-001-001 001 001
    10-01-04-04-001-002 001 002
    10-01-04-04-001-003 001 003
    10-01-04-04-002-001 002 001
    10-01-04-04-002-002 002 002
    10-01-04-04-002-003 002 003
    10-01-04-04-002-004 002 004
    10-01-04-04-002-005 002 005

  4. سلام
    وقت بخیر
    بنده دو تا ستون دارم که همه اعداد غیر تکراری اند
    حالا میخوام جمع چند تا عدد رو در ستون اول با جمع چندتا از اعداد در ستون دوم مچ کنم
    یعنی میخوام اعداد دو ستون رو باهم پاس کنم ولی اعداد مشابه در دو ستون وجود ندارد.
    ممنون میشم راهنماییم کنید.

  5. سلام دوست عزیز
    ممنون از آموزش
    یه سوال من =INDEX(L14:ZA14;MATCH(;L14:ZA14;-1)) برام مقدار میاره حالا میخوام مقدار ستون قبلی رو بیارم ستون قبلی امکان داره یکی قبل باشه یا ده تا شرطش پر بودن هست مممنون میشم راهنمایی کنید

  6. سلام
    يك فايل با 3 شيت داريم
    دوشيت اول شامل اطلاعاتي است كه روزانه افزايش پيدا ميكند فرمت دو شيت يكي مي باشد فقط ديتاي وارد شده فرق ميكند ديتاي ستون اول هر دوشيت نيز تاريخ مي باشد. مي خواهيم كل ديتاهاي دوشيت در شيت سوم زير هم و براسا سورت تاريخ قرارگيرد آيا بدون ماكرونويسي امكان اين كار وجود دارد مثلا باكمك توابع index و match و…
    با تشكر

  7. سلام
    با توجه به اينكه بعد از گذشت چند روز بابت سوالم هيچ جوابي دريافت نكردم با احترام به بقيه دوستان از اين سايت خداحافظي ميكنم.

  8. چطوری میشه دو شرط اگر را در اکسل تعریف کرد مثلا اگر سلول b1 مساوی true بود و سلول a1 مساوی d بود شرط اول را قبول کند در غیر اینصورت شرط دوم

  9. سلام وخسته نباشید و ممنون بابت مطالب مفیدتون
    من یه سوال داشتم اگه کسی میدونه ممنون میشم راهنمایی کنه
    من یه جدول دارم که یه سری کد کالا با تاریخ های انقضاء مختلف توش هست که هر کدوم یه موجودی متفاوت داره از یه جدول دیگه که همون کد کالاها و تاریخ های انقضاء دیگه هست میخوام از جدول شماره یک کد کالا و تاریخ انقضاء رو مقایسه کنه دقیقا همون عدد موجودی رو توی جدول شماره دو برگردونه آیا همچین امکانی توی اکسل هست مثال زیر
    جدول یک جدول دو
    کد تاریخ موجودی کد تاریخ
    12 2017 50 12 2018
    12 2018 60 14 2017
    14 2018 30
    همونطور که میبینید از کد 12 توی جدول یک دوتا هستا که تاریخ ها فرق میکنن من میخوام توی جدول دو کد 12 رو پیدا کنه و با تاریخ روبروش مقایسه کنه اگر تاریخ جدول یک با جدول 2 برابر بود مقدار موجودی رو توی جدول دو برگردونه مثلا توی این مثال عدد 60 رو توی جدول دو برگردونه جلوی کد 12 آیا همچین امکانی هست؟
    ممنون میشم راهنمایی کنید.

  10. سلام من میخوام چندتا تاریخ از قبل تعین شده رو با تاریخ امروز مقایسه کنه اگه هر کدوم درست بود آن روز رو نشون بده می تونید کمک کنید ممنون

    1. میتونید از تابع IF استفاده کنید شرط اول رو تاریخ امروز بزارید درصورت درست بودن خودشو نشون بده درصورت نبودن بنویسه مثلا غلط بعد از طریق فیلتر درستهارو تفکیک کنید.

  11. سلام روز بخیر
    می خواستم در اکسل فرمولی بنویسم که همانند vlookup در یک محدوده جست و جو کند و موارد مشابه هم محاسبه کند .
    میشه راهنمایی کنید از کدوم فرمول استفاده کنم

  12. با عرض سلام و وقت بخیر
    من تعدادی داده 2 ستونی شامل x و y دارم که هرکدام تشکیل نموداری را می دهد، حالا میخواهم پارامترهای x و y های مورد نظر خود را در آن پیدا کنم، درصورتی که تقاطع x و y های مورد جستجو به یکی از خطوط برسد (با 5 درصد خطا)، عنوان آن خط را که از نوع عدد می باشد را برای من بازگرداند، در غیر این صورت با توجه به خطوط مجاور درون یابی کند و عنوان خط درون یابی شده را که حاصل از درون یابی خطوط مجاور می باشد را برگرداند. برای این منظور از چه فرمولهایی می توان استفاده نمود.

  13. سلام
    من میخوام از تابعی مثل IF برای متن استفاده کنم. امکانش هست؟ یا تابعی که کار مشابه انجام بده
    مثلا میخوام اگر مقدار سلول A1 امیر بود در سلول B1 کلمه حسینی نوشته بشه.
    و اینو چند شرطی میخوام. یعنی در عین این که شرط بالا هست مثلا اگر توی همون A1 نوشته شده بود محمد سلول B1 بشه احمدی.
    و این که اگر هیچی نبود خالی باشه.
    چنین چیزی توی اکسل وجود داره یا نه?

  14. با سلام و خسته نباشید خدمت شما.
    ببخشید یه سوالی داشتم خدمتتون.
    من میخوام یه ستونی رو به کارم که پیوستش کردم اضافه کنم. به اینصورت:
    فرضا ما یه دیوار داریم که از 1 درب و 1 پنجره تشکیل شده ودر ضلع شمالی قرار گرفته.
    ردیف اول فرضا دیوار من هست که در ضلع شمالی قرار گرفته.
    ردیف دوم پنجره هست که زیر مجموعه موقعیت شمالیه.
    ردیف سوم یه درب هست که زیر مجموعه دیوار شرقی هست.
    حالا من میخوام یه فرمولی بنویسم که تمام ستون “موقعیت” و “عنصر” رو بخونه. اگه موقعیتم شمالی بود و درب یا پنجره بود بیاد از ردیفی که موقعیتش شمالی هست و عنصرش دیوار هست کم بکنه. نتونستم با تابع and و or اینکار رو انجام بدم.
    ممنون میشم اگه کمکم کنین و به ایمیلم جواب بدین.

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *