على الرغم من أن وظيفة VLOOKUP مفيدة في البحث عن القيم في Excel، إلا أنها محدودة. فباستخدام مجموعة من الدالتين INDEX وMATCH، يمكنك البحث عن القيم في أي مكان أو اتجاه في جدول البيانات الخاص بك.
تعيد الدالة INDEX قيمة بناءً على الموقع الذي تدخله في الصيغة، بينما تفعل الدالة MATCH العكس وتعيد موقعًا بناءً على القيمة التي تدخلها. عند الجمع بين هاتين الدالتين، يمكنك العثور على أي رقم أو نص تريده.
VLOOKUP مقابل INDEX وMATCH
الفرق بين هذه الوظائف ووظيفة VLOOKUP هو أن وظيفة VLOOKUP تبحث عن القيم من اليسار إلى اليمين. ومن هنا جاء اسم الوظيفة؛ حيث تقوم وظيفة VLOOKUP بإجراء بحث رأسي.
مايكروسوفت تشرح بشكل أفضل طريقة عمل VLOOKUP:
توجد بعض القيود عند استخدام VLOOKUP—لا يمكن لدالة VLOOKUP البحث عن قيمة إلا من اليسار إلى اليمين. وهذا يعني أن العمود الذي يحتوي على القيمة التي تبحث عنها يجب أن يكون موجودًا دائمًا على يسار العمود الذي يحتوي على قيمة الإرجاع.
وتضيف Microsoft أنه إذا لم يتم إعداد ورقة البيانات الخاصة بك بطريقة تمكنك من العثور على ما تحتاج إليه باستخدام VLOOKUP، فيمكنك استخدام INDEX وMATCH بدلاً من ذلك. لذا، دعنا نلقي نظرة على كيفية استخدام INDEX وMATCH في Excel.
أساسيات دالتي INDEX وMATCH
لاستخدام هذه الوظائف معًا، من المهم فهم غرضها وبنيتها.
صيغة INDEX في نموذج المصفوفة هي
INDEX(array, row_number, column_number) مع أن الحججتين الأوليين مطلوبتين والثالثة اختيارية.
يبحث INDEX عن موضع ويعيد قيمته. للعثور على القيمة في الصف الرابع في نطاق الخلايا من D2 إلى D8، أدخل الصيغة التالية:
=INDEX(D2:D8,4)
النتيجة هي 20,745 لأن هذه هي القيمة الموجودة في الموضع الرابع من نطاق خلايانا.
لمزيد من التفاصيل حول نماذج المصفوفات والمرجع في INDEX بالإضافة إلى طرق أخرى لاستخدام هذه الوظيفة، ألقي نظرة على كيفية استخدام INDEX في Excel.
بناء الجملة لـ MATCH هو MATCH(value, array, match_type) مع أن الحججتين الأوليين مطلوبتين والثالثة اختيارية.
تبحث MATCH عن قيمة وتعيد موقعها. للعثور على القيمة في الخلية G2 في النطاق من A2 إلى A8، أدخل الصيغة التالية:
=MATCH(G2,A2:A8)
النتيجة هي 4 لأن القيمة في الخلية G2 تقع في الموضع الرابع في نطاق خلايانا.
لمزيد من التفاصيل حول match_type لمعرفة المزيد عن MATCH وطرق أخرى لاستخدام هذه الوظيفة، ألق نظرة على البرنامج التعليمي الخاص بنا لـ MATCH في Excel.
كيفية استخدام INDEX وMATCH في Excel
الآن بعد أن تعرفت على وظيفة كل دالة وقواعدها النحوية، حان الوقت لاستخدام هذا الثنائي الديناميكي. فيما يلي، سنستخدم نفس البيانات المذكورة أعلاه لكل من INDEX وMATCH على حدة.
ستضع صيغة دالة MATCH داخل صيغة دالة INDEX بدلاً من الموضع الذي تريد البحث عنه.
للعثور على القيمة (المبيعات) استنادًا إلى معرف الموقع، يمكنك استخدام هذه الصيغة:
=INDEX(D2:D8,MATCH(G2,A2:A8))
النتيجة هي 20,745. يجد MATCH القيمة في الخلية G2 ضمن النطاق من A2 إلى A8 ويقدمها إلى INDEX الذي يبحث في الخلايا من D2 إلى D8 عن النتيجة.
لنلق نظرة على مثال آخر. نريد أن نعرف أي مدينة بها مبيعات تطابق مبلغًا معينًا. باستخدام جدولنا، يمكنك إدخال الصيغة التالية:
=INDEX(B2:B8,MATCH(G5,D2:D8))
النتيجة هي هيوستن. يجد MATCH القيمة في الخلية G5 ضمن النطاق من D2 إلى D8 ويقدمها إلى INDEX الذي يبحث في الخلايا من B2 إلى B8 عن النتيجة.
فيما يلي مثال باستخدام قيمة فعلية بدلاً من مرجع خلية. سنبحث عن القيمة (المبيعات) لمدينة معينة باستخدام الصيغة التالية:
=INDEX(D2:D8,MATCH("Houston",B2:B8))
في صيغة MATCH، قمنا باستبدال مرجع الخلية الذي يحتوي على قيمة البحث بقيمة البحث الفعلية “هيوستن” من B2 إلى B8 مما يعطينا النتيجة 20,745 من D2 إلى D8.
عند استخدام القيمة الفعلية للبحث، بدلاً من مرجع خلية، تأكد من وضعها بين علامتي اقتباس كما هو موضح هنا.
للحصول على نفس النتيجة باستخدام معرف الموقع بدلاً من المدينة، كل ما علينا فعله هو تغيير الصيغة إلى هذه:
=INDEX(D2:D8,MATCH("2B",A2:A8))
لقد قمنا هنا بتغيير صيغة MATCH للبحث عن “2B” في نطاق الخلايا من A2 إلى A8 وتقديم هذه النتيجة إلى INDEX والتي تقوم بعد ذلك بإرجاع 20,745.
من المؤكد أن الوظائف الأساسية في برنامج Excel مثل تلك التي تساعدك على إضافة أرقام في الخلايا أو إدخال التاريخ الحالي مفيدة. ولكن عندما تبدأ في إضافة المزيد من البيانات وتطوير احتياجاتك في إدخال البيانات أو تحليلها، فقد تكون وظائف البحث مثل INDEX وMATCH في برنامج Excel مفيدة للغاية.