لماذا يفشل VLOOKUP أحيانًا

يُعد VLOOKUP موثوقاً، لكنه يفشل بطرق محدودة ويمكن التنبؤ بها. ومعرفة القواعد تجعل معظم الأخطاء غير غامضة.

ستتعلم في هذا الدرس الأسباب الشائعة لتعطل عملية البحث وكيفية إصلاح كل منها تحديداً. ومعرفة هذه الأسباب تحول أخطاء #N/A و#REF! المربكة إلى إصلاحات سريعة وسهلة.

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

إذا كانت معرّفاتك في العمود C والاسم الذي تريده في العمود A، فلا يستطيع VLOOKUP الرجوع إلى اليسار. أمامك خياران: أعد ترتيب الأعمدة بحيث يصبح عمود البحث أولاً، أو استخدم INDEX-MATCH أو XLOOKUP، إذ يمكنهما البحث في أي اتجاه.

يُحتسب col_index_num من يسار table_array، وليس من الورقة. ومن الأخطاء الشائعة استخدام حرف عمود الورقة باعتباره الرقم.

إذا كان نطاقك هو C1:F10 وتريد العمود F، فهو العمود الرابع في النطاق، ولذلك يكون الفهرس 4 وليس 6. ويؤدي العد من الحافة الخاطئة إلى إرجاع الحقل الخاطئ، أو إلى ظهور خطأ #REF! إذا تجاوز الرقم عرض النطاق.

إذا كان col_index_num أكبر من عدد الأعمدة في table_array، فإن VLOOKUP يُرجع #REF!.

فعلى سبيل المثال، من المستحيل طلب العمود 5 من نطاق مكوّن من 3 أعمدة، وهو A1:C10:

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

يؤدي حذف الوسيط الرابع إلى استخدام TRUE افتراضياً، أي التطابق التقريبي. وفي قائمة غير مرتبة، يُرجع ذلك بصمت قيمة مجاورة خاطئة بدلاً من ظهور خطأ، مما يصعب اكتشافه.

والحل بسيط وينبغي أن يصبح عادة: أضف FALSE دائماً لعمليات البحث عن تطابق تام.

لن تتطابق قيمة البحث "A100" مع "A100 " التي تحتوي على مسافة زائدة في نهايتها. فالبيانات المستوردة مليئة بهذه الاختلافات غير المرئية.

الأعراض: توجد القيمة بوضوح، ومع ذلك تحصل على #N/A. نظّف الطرفين باستخدام TRIM لإزالة المسافات الزائدة:

إذا كانت قيمة البحث رقماً 100، بينما يخزّن الجدول الرموز كنص "100"، أو العكس، فلن تتطابق القيمتان وستحصل على #N/A.

ابحث عن المثلث الأخضر الصغير أو الأرقام المحاذاة إلى اليسار، فهي تشير إلى أن القيمة نصية. أصلح ذلك بالتحويل: أحط النص بـ VALUE() لتحويله إلى رقم، أو أضف سلسلة فارغة إلى رقم باستخدام &"" لتحويله إلى نص، بحيث يكون النوع نفسه في الطرفين.

إذا نسيت تثبيت table_array، فإن نسخ الصيغة إلى الأسفل يزيح النطاق بعيداً عن بياناتك. يصبح النطاق A1:C100 في الصف 2 هو A2:C101 في الصف 3، ثم A3:C102، فتفقد صفوفاً في الطريق.

أصلح ذلك باستخدام مراجع مطلقة حتى يظل الجدول ثابتاً، بينما تتحرك قيمة البحث فقط:

يشير كل خطأ إلى سبب معيّن:

• #N/A - لم يتم العثور على القيمة (بسبب عدم تطابق أو مسافات أو نوع خاطئ، أو لأنها مفقودة فعلاً)
• #REF! - قيمة col_index_num أكبر من النطاق، أو تم حذف خلية مشار إليها
• #VALUE! - أحد الوسائط من نوع خاطئ، مثل فهرس عمود سالب أو يساوي صفراً
• #NAME? - اسم الدالة مكتوب بشكل خاطئ، مثل VLOOKP

طابق الخطأ مع معناه، وستكون قد حللت نصف المشكلة بالفعل.

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

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

عندما لا تعمل عملية البحث كما ينبغي، راجع قائمة التحقق السريعة التالية:

• هل توجد قيمة البحث في العمود الأول من النطاق؟
• هل تم احتساب col_index_num من الحافة اليسرى للنطاق، وهل يقع ضمن عرض النطاق؟
• هل أضفت FALSE للحصول على تطابق تام؟
• هل النوع نفسه لدى الطرفين، أي نص مقابل رقم، وهل يخلو الطرفان من المسافات الزائدة؟
• هل تم تثبيت table_array باستخدام علامات الدولار؟

تؤدي مراجعة هذه القائمة من أولها إلى آخرها إلى حل الغالبية العظمى من مشكلات البحث خلال ثوانٍ.

شخّص عملية البحث الفاشلة هذه.

الأسباب المعتادة وحلولها:

• حدود العمود الأيسر - أعد الترتيب، أو استخدم INDEX-MATCH / XLOOKUP
• col_index_num خاطئ أو أكبر من اللازم - احسبه من الحافة اليسرى للنطاق، ووسّع النطاق
• عدم إضافة FALSE - استخدم دائماً التطابق التام للمعرّفات
• المسافات والفرق بين النص والرقم - نظّف باستخدام TRIM، وحوّل باستخدام VALUE أو &""
• الجدول غير المثبّت - استخدم $ كي يظل النطاق ثابتاً

اقرأ رمز الخطأ، وطابقه مع سببه، ثم طبّق الحل. لديك الآن مجموعة أدوات كاملة لإجراء عمليات بحث موثوقة.