دمج INDEX وMATCH

أصبحت تعرف الآن جانبي عملية البحث. تحدد MATCH موضع القيمة، بينما تُرجع INDEX القيمة الموجودة في ذلك الموضع.

عند الجمع بينهما تحصل على عملية بحث متكاملة: تحدد MATCH الصف، ثم تستخرج INDEX البيانات من ذلك الصف في أي عمود تختاره.

النمط بسيط بعد أن تراه: ضع MATCH داخل INDEX، في الموضع الذي يوضع فيه رقم الصف عادةً.

إليك الصيغة التي ستستخدمها مرارًا:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

اقرأها من الداخل إلى الخارج. تُشغّل MATCH أولًا وتُرجع رقم موضع. ثم يصبح هذا الرقم هو row_num في INDEX، التي تُرجع القيمة من نطاق الإرجاع.

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

تخيل جدولًا يحتوي العمود A فيه على أسماء المنتجات، والعمود C على الأسعار. تريد سعر "Cherry".

أولًا، تبحث MATCH عن Cherry: تُرجع =MATCH("Cherry", A2:A20, 0)، على سبيل المثال، القيمة 3.

بعد ذلك، تستخدم INDEX الرقم 3: تُرجع =INDEX(C2:C20, 3) السعر الموجود في الصف الثالث من العمود C.

وعند دمجهما، تحصل على النتيجة دفعة واحدة: =INDEX(C2:C20, MATCH("Cherry", A2:A20, 0)).

يُعد تثبيت "Cherry" في الصيغة مناسبًا للتعلم، لكن الصيغ الواقعية تشير إلى خلية بدلًا من ذلك. ضع مصطلح البحث في E1 وأشِر إليه.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

والآن، سيُرجع أي منتج تكتبه في E1 سعره فورًا. اكتب Banana، فتحصل على سعر Banana؛ واكتب Date، فتتحدث الإجابة.

وهكذا تتحول صيغة واحدة إلى أداة بحث قابلة لإعادة الاستخدام، تتحكم فيها خلية الإدخال بالكامل.

إليك الحيلة التي تجعل INDEX-MATCH مميزًا. عمود البحث وعمود الإرجاع مستقلان، لذا يمكن أن تكون القيمة التي تُرجعها إلى يسار القيمة التي تبحث عنها.

لنفترض أن الأسعار موجودة في العمود A وأسماء المنتجات في العمود C. للعثور على سعر منتج باستخدام اسمه، تكتب =INDEX(A2:A20, MATCH(E1, C2:C20, 0)).

لقد بحثت في العمود C، لكنك أرجعت القيمة من العمود A. لا يستطيع VLOOKUP إجراء ذلك من دون مساعدة.

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

للعثور على البريد الإلكتروني لعميل: =INDEX(D2:D50, MATCH(E1, A2:A50, 0)).

وللعثور بدلًا من ذلك على مدينة العميل نفسه: =INDEX(F2:F50, MATCH(E1, A2:A50, 0)).

يبقى جزء MATCH مطابقًا تمامًا؛ ولا يتغير سوى نطاق INDEX لاختيار إجابة مختلفة.

يمكنك تزويد INDEX برقم عمود أيضًا، ويُعثر عليه باستخدام MATCH ثانٍ. وهذا يحدد قيمة عند تقاطع صف وعمود.

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

يعثر MATCH الأول على الصف من التسميات في A، بينما يعثر الثاني على العمود من العناوين في الصف 1. وتُرجع INDEX الخلية الموجودة عند تقاطعهما. ستتناول درسًا متقدمًا هذا النمط بالتفصيل لاحقًا.

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

إذا بحث MATCH في A2:A20 (أي 19 صفًا)، بينما أرجع INDEX قيمة من C2:C19 (أي 18 صفًا)، فستنحرف المواضع وستحصل على إجابة خاطئة.

من العادات الموثوقة استخدام الامتداد نفسه للصفوف في كليهما، مثل A2:A20 وC2:C20. كما تظل مراجع الأعمدة بأكملها، مثل A:A وC:C، متحاذية تلقائيًا.

إذا تعذر على MATCH العثور على قيمة البحث، فإنه يُرجع #N/A، وتعرض صيغة INDEX-MATCH بأكملها هذا الخطأ. استخدم IFNA للحصول على نتيجة بديلة مرتبة.

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

الآن سيعرض المنتج غير الموجود النص "Not found" بدلًا من رسالة خطأ مزعجة. ويعمل IFERROR أيضًا، لكن IFNA يستهدف حالة عدم العثور فقط ويتيح ظهور الأخطاء الأخرى.

لنجمع كل ذلك. لديك جدول موظفين: المعرّفات في العمود A، والأسماء في B، والأقسام في C، والرواتب في D. يُدخل المستخدم معرّفًا في G1.

لإرجاع قسم ذلك الموظف: =INDEX(C2:C200, MATCH(G1, A2:A200, 0)).

ولإرجاع راتبه بدلًا من ذلك، استبدل نطاق INDEX بالنطاق D2:D200. لا يتغير منطق البحث مطلقًا، بل يتغير العمود الذي تقرأ منه فقط. وهذه هي أداة العمل الأساسية اليومية لعمليات البحث الديناميكية.

عندما تبدو الصيغة معقدة، قيّمها بالطريقة التي يتبعها جدول البيانات، بدءًا من الدالة الأعمق إلى الخارج.

في الصيغة =INDEX(C2:C20, MATCH(E1, A2:A20, 0)): اقرأ أولًا MATCH(E1, A2:A20, 0)، وتصور أنها تُرجع رقمًا مثل 5، ثم استبدلها ذهنيًا لتحصل على =INDEX(C2:C20, 5).

فجأة تصبح الصيغة مجرد «إرجاع السعر الخامس». وتجعل هذه العادة تصحيح أخطاء أي عملية بحث متداخلة أمرًا سهلًا.

تأكد من فهمك لكيفية اجتماع الدالتين.

لقد جمعت الدالتين في عملية بحث مرنة:

• النمط: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• يعثر MATCH على موضع الصف؛ وتُرجع INDEX القيمة الموجودة في ذلك الموضع
• عمودا البحث والإرجاع مستقلان، لذا يمكنك البحث إلى اليسار بسهولة البحث إلى اليمين
• حافظ على الارتفاع نفسه للنطاقين، واستخدم IFNA للتعامل مع الأخطاء بطريقة واضحة

بعد ذلك، تعرّف بالضبط على سبب تفوق هذا الأسلوب غالبًا على VLOOKUP.