سارة تطابق 980 رمز منتج بأسعار عقودها في مخزن أدوات مكتبية بالإسكندرية. عمل الجدول سنتين كاملتين، ثم أدرج زميل عمودا لرمز الباركود، فصار كل سعر في الجدول رقما لوزن الطرد بالغرام.
لم يظهر أي خطأ. هذا هو العطل الخاص بدالة VLOOKUP، ولهذا السبب بقيت تستحق الفهم ولم تعد تستحق أن تُكتب من جديد.
لماذا يفشل عد الأعمدة
=VLOOKUP(A2,Stock!A:G,5,FALSE) يعني: ابحث عن قيمة A2 في أول عمود من ذلك النطاق، ثم أعد ما في العمود الخامس.
العمود الخامس موضع، لا اسم. إدراج عمود إلى يسار الجدول يجعل الخامس شيئا آخر، وتستمر المعادلة في العمل، والجواب خطأ بثقة تامة. لا تظهر قيمة خطأ لأن شيئا غير صحيح لم يحدث من منظور إكسل.
وهناك قيد آخر: مفتاح البحث يجب أن يكون في أول عمود من النطاق، فلا يمكنها النظر يسارا أبدا. والوسيط الرابع، FALSE، هو ما يفرض المطابقة الدقيقة؛ إسقاطه يمنح مطابقة تقريبية، تُعيد في بيانات غير مرتبة نتائج قريبة تبدو صحيحة وليست كذلك.
XLOOKUP، وهو ما يُكتب الآن
=XLOOKUP(A2,Stock[الرمز],Stock[السعر]) يسمّي عمود البحث وعمود الإرجاع، كل بمفرده، كنطاقين منفصلين.
ولأن كليهما مُسمّى لا معدود، لا يغيّر إدراج عمود شيئا. ولأنهما منفصلان، يمكن أن يكون العمود المُعاد إلى يسار المفتاح. وهي تطابق مطابقة دقيقة افتراضيا، فيختفي العطل الأشيع.
وسيط رابع يحدد ما يُعرض حين لا توجد مطابقة، فتصير =XLOOKUP(A2,Stock[الرمز],Stock[السعر],"لا يوجد عقد") أفيد من N/A#، ولا تحتاج لفّها بشيء آخر.
وصلت XLOOKUP إلى Microsoft 365 عام 2019 وهي في إكسل 2021 وما بعده. في إصدار أقدم، أو ملف يجب أن يُفتح فيه، البديل هو INDEX وMATCH.
INDEX وMATCH، ولماذا يستحقان المعرفة
=INDEX(Stock[السعر],MATCH(A2,Stock[الرمز],0)) يُقرأ من الداخل إلى الخارج. تجد MATCH الصف الذي يقع فيه A2. وتعيد INDEX ذلك الصف من عمود السعر. الصفر هو وسيط المطابقة الدقيقة، وهو ما ينساه أغلب الناس.
تعمل في كل إصدار من إكسل صدر يوما، وتنجو من إدراج الأعمدة، وتستطيع النظر يسارا. إنها دالتان بدل واحدة، وهذا هو السبب الوحيد لظهور XLOOKUP بديلا عنها.
الأسباب الأربعة لفشل المطابقة
اختلاف النوع. جانب يحمل الرقم 4021 والآخر يحمل النص "4021"، ويبدوان متطابقين وليسا كذلك. =ISNUMBER() على كل جانب يحسم الأمر خلال ثوان.
مسافة زائدة في آخر النص. المفاتيح المستوردة تحملها باستمرار، وتزيلها TRIM().
وأحرف غير مرئية من ملف مصدره صفحة ويب أو نظام آخر، لا تلمسها TRIM ولا تزيلها إلا CLEAN().
وغياب حقيقي، وهو الحالة التي تكون فيها N/A# الإجابة الصحيحة، وعلى الجدول أن يقولها لا أن يخفيها.
أخطاء سارة كانت 22 رمزا من أصل 980: أربعة عشر بمسافة زائدة، خمسة محفوظة كنص، وثلاثة بلا عقد فعلا حتى الآن.
المطابقة على أمرين معا
رمز المنتج فريد، لكن السعر حسب الفئة والمورد ليس كذلك. الحل الموثوق هو بناء مفتاح واحد من الجزأين: عمود مساعد بصيغة =A2&"|"&B2 في الجدولين معا، ثم البحث عن ذلك.
الفاصل مهم. بلا فاصل، يلتحم "12" و"34" بنفس النص الذي يلتحم منه "1" و"234"، فتنتج مطابقات خاطئة لا مطابقات مفقودة.
ما يُكتب بدل VLOOKUP، حتى 2026
XLOOKUP، في Microsoft 365 وإكسل 2021 وما بعده، حلت محل عدّ الأعمدة في VLOOKUP ومحل مطابقتها التقريبية الافتراضية. هي الجواب ما لم يجب أن يُفتح الملف في إصدار أقدم.
INDEX وMATCH للتوافق، في كل إصدار، وما زالتا الشيء الذي يجب معرفته في جدول ورثه أحد.
غوغل شيتس تحمل XLOOKUP أيضا، فتنتقل المعادلة نفسها بينهما دون تغيير.
نموذج ذكاء اصطناعي يحوّل VLOOKUP إلى XLOOKUP بصحة، مجانا على كل مساعد رئيسي، وهو استخدام جيد لنصف دقيقة في جدول ورثه أحد. لا يرى إن كان عمودا المفتاح من النوع نفسه، وهذا فعلا ما يكسر عمليات البحث.
الجدول الذي تُبنى عليه هذه المطابقات نفسه هو موضوع أساسيات إكسل، والنص المستورد الذي يسبب اختلاف النوع تعالجه تنظيف الجداول، وتلخيص 980 نتيجة مطابقة في جدول واحد هو الجداول المحورية.