XLOOKUP مقابل VLOOKUP: الفروق الرئيسية وأمثلة وأيهما تستخدم

عند المفاضلة بين XLOOKUP وVLOOKUP، استخدم XLOOKUP إذا كان كل من يفتح الملف يملك Excel لـ Microsoft 365 أو Excel 2021 أو أحدث أو Excel للويب. فهي تؤدي كل ما تؤديه VLOOKUP بأخطاء أقل: مطابقة تامة افتراضيًا، ويمكنها البحث نحو اليسار، ولا تتعطل عند إدراج عمود. واستخدم VLOOKUP فقط عندما يجب أن يعمل المصنف في Excel 2019 أو أقدم.

نقطة التوافق هذه هي المحظور الرئيسي. فـ XLOOKUP غير موجودة في Excel 2016 أو 2019، لذا يعرض الملف المليء بصيغ XLOOKUP أخطاء #NAME? لكل من ما زال يستخدم تلك الإصدارات.

تنبيه بخصوص التسمية: يحتفظ Excel العربي بأسماء الدوال الإنجليزية، فاسما الدالتين هما نفسهما VLOOKUP (البحث الرأسي) وXLOOKUP (البحث الموسّع) في كل اللغات، وكل الصيغ في هذه المقالة معروضة كما في النسخة الإنجليزية من Excel.

الصيغتان جنبًا إلى جنب

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

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

عملية البحث نفسها مكتوبة بالطريقتين

هذه قائمة منتجات صغيرة في A1:D6، مع معرّف منتج مكتوب في G2:

للعثور على سعر P-104:

=VLOOKUP(G2, A2:D6, 4, FALSE)
=XLOOKUP(G2, A2:A6, D2:D6)

كلتاهما تُرجع $59.00. تبحث VLOOKUP في العمود الأول من A2:D6 وتُرجع العمود الرابع؛ وFALSE تفرض مطابقة تامة. أما XLOOKUP فتبحث في A2:A6 وتُرجع الخلية المطابقة من D2:D6، مع المطابقة التامة كخيار افتراضي.

إذا كنت ستنسخ الصيغة لأسفل العمود، فثبّت الجدول بمراجع مطلقة، مثل =XLOOKUP(G2, $A$2:$A$6, $D$2:$D$6). اضغط F4 على Windows أو Cmd+T على Mac والمؤشر داخل مرجع لإضافة علامات $.

مشكلتا VLOOKUP الكلاسيكيتان

1. نسيان FALSE (أو 0)

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

جرّب =VLOOKUP("P-110", A2:D6, 4) على الجدول أعلاه. المعرّف P-110 غير موجود، لكنك بدلاً من #N/A تحصل على $32.75، سعر P-105، لأن «P-105» هي أقرب قيمة أصغر. وفي البيانات غير المرتبة قد تكون النتائج عشوائية أكثر.

أنهِ دائمًا VLOOKUP ذات المطابقة التامة بـ FALSE أو 0. وتتجنب XLOOKUP هذه المشكلة لأنها تستخدم المطابقة التامة ما لم تطلب غير ذلك.

2. رقم العمود المكتوب يدويًا

الرقم 4 في صيغة VLOOKUP مجرد رقم. فإذا أدرج أحدهم عمود Supplier بين Product وCategory، يصبح الجدول A:E وينتقل Price إلى العمود E، وتظل الصيغة تُرجع العمود الرابع الذي أصبح الآن Category. فسترى «Video» بدلاً من $59.00.

أما XLOOKUP فتشير إلى عمود السعر نفسه (D2:D6)، فيحدّثه Excel تلقائيًا إلى E2:E6 وتبقى الإجابة صحيحة.

ما تستطيع XLOOKUP فعله ولا تستطيعه VLOOKUP

البحث نحو اليسار

لا تستطيع VLOOKUP إرجاع سوى الأعمدة التي على يمين عمود البحث. أما XLOOKUP فلا تهتم بالاتجاه. للعثور على معرّف المنتج لاسم منتج موجود في G2:

=XLOOKUP(G2, B2:B6, A2:A6)

مع وجود «Desk Lamp» في G2، تُرجع هذه الصيغة P-105، رغم أن العمود A يقع على يسار العمود B.

عرض رسالة ودّية عندما لا يُعثر على شيء

=XLOOKUP(G2, A2:A6, D2:D6, "Not found")

ما يعادلها في VLOOKUP يحتاج إلى غلاف: =IFNA(VLOOKUP(G2, A2:D6, 4, FALSE), "Not found").

إرجاع عدة أعمدة في صيغة واحدة

=XLOOKUP(G2, A2:A6, B2:D6)

للمعرّف P-104 تُنسكب Webcam HD وVideo و**$59.00** في ثلاث خلايا متجاورة. اترك تلك الخلايا فارغة، وإلا ستحصل على الخطأ #SPILL!.

العثور على آخر تطابق بدلاً من الأول

في سجل يظهر فيه العميل نفسه مرات كثيرة، اضبط search_mode على -1 للبحث من الأسفل إلى الأعلى والحصول على أحدث إدخال:

=XLOOKUP(G2, A2:A500, C2:C500, "Not found", 0, -1)

مطابقة تقريبية دون فرز

لأسعار الشرائح أو معدلات العمولة، لا تعمل المطابقة التقريبية في VLOOKUP إلا إذا كان العمود الأول مرتبًا تصاعديًا. أما قيمتا match_mode في XLOOKUP، وهما -1 (مطابقة تامة، أو العنصر الأصغر التالي) و1 (مطابقة تامة، أو العنصر الأكبر التالي)، فلا تتطلبان بيانات مرتبة. مع الحد الأدنى لمبالغ الطلب في J2:J5 والخصومات في K2:K5:

=VLOOKUP(B2, J2:K5, 2, TRUE)
=XLOOKUP(B2, J2:J5, K2:K5, , -1)

أحرف البدل

كلتاهما تدعم * و?، لكن XLOOKUP تحتاج إلى match_mode بقيمة 2 لمعاملتهما كأحرف بدل:

=VLOOKUP("Desk*", B2:D6, 3, FALSE)
=XLOOKUP("Desk*", B2:B6, D2:D6, , 2)

كلتاهما تُرجع $32.75 لـ Desk Lamp.

INDEX/MATCH: البديل الكلاسيكي

قبل XLOOKUP، كان الحل المعتاد لقيود VLOOKUP هو INDEX مع MATCH. فهي تبحث نحو اليسار، وتتحمل إدراج الأعمدة، وتعمل في كل إصدارات Excel، مما يجعلها الخيار الأفضل للملفات التي يجب أن تُفتح في Excel 2019 أو أقدم:

=INDEX(D2:D6, MATCH(G2, A2:A6, 0))
=INDEX(A2:A6, MATCH(G2, B2:B6, 0))

الأولى تُرجع السعر للمعرّف الموجود في G2؛ والثانية هي مثال البحث نحو اليسار. الرقم 0 في MATCH يعني مطابقة تامة، ومثل FALSE في VLOOKUP، من السهل نسيانه. فـ XLOOKUP ببساطة هي النسخة الأقصر والأكثر أمانًا من الفكرة نفسها.

كيفية تحويل صيغ VLOOKUP إلى XLOOKUP

لا يوجد محوّل مدمج، لكن الترجمة آلية:

1. اعثر على صيغ VLOOKUP لديك: اضغط Ctrl+F (أو Cmd+F على Mac)، واكتب VLOOKUP، وانقر على خيارات (Options) إذا كانت الإعدادات الإضافية مخفية، واضبط البحث في (Look in) على صيغ (Formulas)، وانقر على بحث عن الكل (Find All).
2. أبقِ الوسيط الأول (قيمة البحث) كما هو.
3. استبدل الجدول بـعموده الأول ليكون lookup_array.
4. استبدل رقم العمود بـالعمود المقابل ليكون return_array. العمود 5 في جدول يبدأ من العمود A هو العمود E.
5. احذف FALSE. وإذا كانت الصيغة الأصلية تنتهي بـ TRUE أو بلا وسيط أخير، فأضف match_mode بقيمة -1 للحفاظ على سلوك «الأصغر التالي».

على سبيل المثال:

=VLOOKUP(A2, Products!$A$2:$F$500, 5, FALSE)
=XLOOKUP(A2, Products!$A$2:$A$500, Products!$E$2:$E$500)

اختبر بعض النتائج مقارنةً بالصيغة القديمة قبل تحويل ورقة كاملة. فقد تكون صيغ VLOOKUP ذات المطابقة التقريبية على بيانات غير مرتبة تُرجع قيمًا خاطئة منذ البداية، لذا قد تختلف إجابات XLOOKUP اختلافًا مشروعًا.

ماذا يحدث عند فتح ملف XLOOKUP في Excel 2016 أو 2019

يُفتح المصنف، لكن هذه الإصدارات لا تعرف الدالة. تظهر في شريط الصيغة على شكل =_xlfn.XLOOKUP(...). وقد تظل الخلايا تعرض آخر نتائج محفوظة في البداية، لكن بمجرد إعادة حساب الصيغة تُرجع #NAME?.

إذا كنت تشارك الملفات مع أشخاص يستخدمون إصدارات أقدم:

• استخدم INDEX/MATCH أو VLOOKUP بدلاً منها، أو
• أرسل نسخة تكون نتائجها ملصقة كقيم (نسخ ← لصق خاص ← قيم، Copy ← Paste Special ← Values).

وينطبق الأمر نفسه على الدوال الأحدث الأخرى مثل FILTER وUNIQUE وXMATCH.

XLOOKUP في Google Sheets

تحتوي Google Sheets على XLOOKUP أيضًا، بترتيب الوسائط نفسه: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). تعمل الصيغ في هذه المقالة هناك كما هي، لذا فـ XLOOKUP خيار آمن للملفات التي تنتقل بين Excel وSheets.

لماذا يُرجع البحث #N/A أو قيمة خاطئة

• مسافات زائدة. القيمة «P-104 » بمسافة في النهاية لا تطابق «P-104». نظّف البيانات بـ TRIM.
• أرقام مخزنة كنص. المعرّف المكتوب كنص لن يطابق المعرّف نفسه المخزن كرقم. ابحث عن المثلث الأخضر في زاوية الخلية وحوّل العمود إلى نوع واحد.
• غياب FALSE في VLOOKUP. تحصل على إجابة معقولة لكنها خاطئة بدلاً من #N/A.
• نطاقات تحركت عند نسخ الصيغة. استخدم مراجع $ المطلقة لجدول البحث.
• #REF! من VLOOKUP. رقم العمود أكبر من عدد أعمدة الجدول.
• #VALUE! من XLOOKUP. نطاقا البحث والإرجاع بحجمين مختلفين، مثل A2:A6 وD2:D60.
• #NAME? اسم الدالة مكتوب بشكل خاطئ، أو أن الملف مفتوح في إصدار لا يحتوي على XLOOKUP.

الأسئلة الشائعة

هل XLOOKUP أفضل من VLOOKUP؟ نعم، في كل الاستخدامات تقريبًا. فهي مطابقة تامة افتراضيًا، وتبحث في أي اتجاه، ولا تتعطل عند تحريك الأعمدة، وتتعامل مع القيم المفقودة دون غلاف. والميزة الحقيقية الوحيدة لـ VLOOKUP أنها تعمل في كل إصدارات Excel.

هل XLOOKUP أسرع من VLOOKUP؟ في المصنفات اليومية لن تلاحظ فرقًا. وفي القوائم المرتبة الكبيرة جدًا، قد تسرّع أوضاع البحث الثنائي في XLOOKUP (search_mode بقيمة 2 أو -2) الأداء، لكنها تُرجع نتائج خاطئة إذا لم تكن البيانات مرتبة.

لماذا تعرض XLOOKUP الخطأ #NAME? على حاسوبي؟ إصدار Excel لديك لا يتضمن XLOOKUP. فهي موجودة في Microsoft 365 وExcel 2021 وما بعده وExcel للويب، لكنها غير موجودة في Excel 2019 أو 2016. استخدم INDEX/MATCH بدلاً منها.

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

هل تستطيع XLOOKUP إرجاع كل التطابقات وليس الأول فقط؟ لا، فهي تُرجع تطابقًا واحدًا (أو صفًا واحدًا من الأعمدة). وللحصول على كل الصفوف المطابقة، استخدم FILTER، مثل =FILTER(A2:D6, C2:C6="Office").

هل ما زال علي أن أتعلم VLOOKUP؟ يستحق الأمر أن تتعرف عليها، لأنك ستجدها في المصنفات والقوالب الأقدم. أما للصيغ الجديدة، فتعلّم XLOOKUP، وضع INDEX/MATCH في بالك للملفات التي يجب أن تُفتح في Excel القديم.

دع الذكاء الاصطناعي يكتب صيغة البحث نيابةً عنك

صيغ البحث هي المكان الذي تتسبب فيه التفاصيل الصغيرة، مثل FALSE ناقصة أو نطاق منزاح، بأكثر الأخطاء الصامتة. يتيح لك CoreGPT for Excel وصف ما تحتاجه، مثل «جد السعر لمعرّف المنتج في G2» أو «علّم كل صف بأنه فوق الهدف أو دونه»، ويكتب الصيغة في ورقتك مع شرح موجز. ويمكنك البدء مجانًا، ويعمل في Excel لأنظمة Windows وMac وعلى الويب.

جرّبه: CoreGPT for Excel

قراءات ذات صلة:

• How to Remove Duplicates in Excel: 5 Ways (With and Without Deleting Data)
• Microsoft 365 Excel Tips That Save Time Every Week
• How to Use ChatGPT in Excel: A Complete Guide