دالة INDEX و MATCH للبحث المتقدم في إكسل

ربما تجد صعوبة في العثور على بيانات معينة داخل جداول الإكسل، أو تلاحظ بعض القيود مثل ضرورة البحث من اليسار إلى اليمين أو الحاجة إلى ترتيب البيانات بطريقة معينة، ولكن تأتي دالة INDEX و MATCH في إكسل لتمنحك مرونة ودقة غير مسبوقة في البحث عن القيم داخل الجداول.

أهمية دالة INDEX و MATCH في إكسل

دالة INDEX و MATCH في إكسل من أقوى الدوال المستخدمة للبحث عن البيانات في جداول Excel.

أهمية دالة INDEX:

تستخدم لاسترجاع قيمة من نطاق معين بناءً على رقم الصف والعمود.
توفر مرونة كبيرة عند التعامل مع الجداول الكبيرة حيث يمكنها البحث في أي اتجاه.
أكثر كفاءة من VLOOKUP خاصة عند التعامل مع جداول ذات نطاقات واسعة.

أهمية دالة MATCH:

تستخدم للعثور على مكان عنصر معين داخل نطاق من البيانات.
يمكن استخدامها بمفردها أو مع INDEX للحصول على نتائج ديناميكية.
كما أنها تدعم البحث التام أو التقريبي مما يجعلها أكثر مرونة.

دالة INDEX

تستخدم دالة INDEX في إكسل لاسترجاع قيمة من نطاق بيانات محدد بناءً على أرقام الصفوف والأعمدة.

أي أنها تعيد القيمة الواقعة عند نقطة التقاطع بين صف وعمود معينين داخل الجدول.

الصيغة العامة للدالة:

INDEX(array, row_num, [column_num])

حيث أن:

المصفوفة (Array) هى النطاق الذي يحتوي على البيانات المراد البحث فيها (مطلوب).
رقم الصف (row_num) يحدد الصف الذي سيتم البحث فيه عن القيمة (مطلوب).
رقم العمود (column_num) يحدد العمود الذي سيتم البحث فيه عن القيمة (اختياري).

مثال عملي:

في هذا الجدول التالي لدينا بيانات قروض عملاء أحد المصارف، ونرغب في معرفة المبلغ المسدَّد من قِبل العميل محمد قاسم.

لتحديد الخلية التي سيتم عرض النتيجة فيها نضع المؤشر داخل الخلية E16، ثم ندخل الصيغة التالية: INDEX(B2:I14,10,8)=

يمثل الرقم 10 رقم الصف داخل النطاق B2:I14، حيث يبدأ العد من الصف الأول داخل هذا النطاق، وليس من بداية ورقة العمل.

أما الرقم 8 فيشير إلى العمود المقابل داخل نفس النطاق.

دالة MATCH

تستخدم دالة MATCH في إكسل للبحث عن قيمة معينة داخل نطاق من الخلايا، ثم تُرجع موضعها النسبي داخل هذا النطاق.

الصيغة العامة للدالة:

MATCH(lookup_value, lookup_array, [match_type])

حيث أن:

• lookup_value (مطلوبة) تمثل القيمة التي نبحث عنها داخل lookup_array.
  – يمكن أن تكون هذه القيمة رقمًا، نصًا، قيمة منطقية، أو مرجع خلية يحتوي على إحدى هذه القيم.
  – على سبيل المثال عند البحث عن رقم هاتف في دليل، فإن اسم الشخص هو lookup_value الذي نستخدمه للعثور على الرقم المطلوب.
• lookup_array (مطلوبة) هو نطاق الخلايا الذي يتم البحث داخله عن lookup_value.
• match_type (اختيارية) تحدد طريقة البحث عن lookup_value داخل lookup_array.
  – تأخذ القيم -1 أو 0 أو 1، حيث تكون القيمة الافتراضية 1.

مثال عملي:

باستخدام المثال السابق نرغب في تحديد رقم الصف الذي يحتوي على بيانات العميل محمد قاسم.

لتحديد الخلية التي سيتم عرض النتيجة فيها نضع المؤشر داخل الخلية E16، ثم نكتب الصيغة التالية:

=MATCH(“محمد قاسم”, B2:B14, 0)

تقوم الدالة بالبحث داخل النطاق B2:B14 عن الاسم المحدد، ثم تُرجع رقم الصف الذي يظهر فيه أول تطابق. في هذا المثال تعيد الدالة الرقم 10 وهو موقع الصف الذي يحتوي على اسم العميل.

اقرأ أيضا: دوال الجمع: الفرق بين SUM و SUMIF و SUMIFS.

كيفية الجمع بين دالة INDEX و MATCH

يعد الجمع بين دالة INDEX و MATCH في إكسل أحد أقوى أساليب البحث داخل Excel، حيث يتيح لك البحث عن القيم في أي اتجاه داخل الجدول.

عند دمج دالة INDEX و MATCH في إكسل يصبح البحث أكثر دقة ومرونة، حيث يمكن البحث عن القيم في أي اتجاه وليس فقط في الأعمدة المجاورة.

الصيغة الأساسية للجمع بين الدالتين:

INDEX(value_range, MATCH(search_value, search_range, 0))=

تقوم MATCH بتحديد موضع “search_value” قيمة_البحث داخل ” search_range” نطاق_البحث، ثم تستخدم INDEX هذا الموضع لاستخراج القيمة من “value_range” نطاق_القيم.

مثال عملي:

لنفترض أن لدينا جدولًا يحتوي على أسماء المنتجات في العمود A وأسعارها في العمود B، ونريد معرفة سعر منتج معين (مثل “phone”):

الصيغة:
=INDEX(B2:B10, MATCH(“phone”, A2:A10, 0))

MATCH(“phone”, A2:A10, 0) تعثر على موقع المنتج “phone” في العمود A.
INDEX(B2:B10, النتيجة) تسترجع السعر المقابل من العمود B.

فيديو تفصليلي INDEX و MATCH هنا

اقرأ أيضا: دالة CHOOSE: كيفية استخدامها لاختيار القيم بسهولة.