دالة XLOOKUP في إكسل: شرح كامل بالأمثلة وبديل VLOOKUP
إذا كنت تستخدم VLOOKUP منذ سنوات وتعاني من أخطائها المتكررة، فدالة XLOOKUP ستغيّر طريقة عملك. في هذا الدليل نشرحها من الصفر بأمثلة يمكنك تطبيقها فوراً على ملفاتك.
ما هي دالة XLOOKUP؟
XLOOKUP دالة بحث تبحث عن قيمة معيّنة داخل عمود (أو صف)، ثم تُرجع القيمة المقابلة لها من عمود آخر. فكّر فيها كأنك تسأل إكسل: «ابحث لي عن رقم الموظف 105، وأعطني راتبه». متوفرة في إكسل 365 وإكسل 2021 وما بعده، وكذلك في إكسل للويب المجاني.
ميزتها الكبرى أنها تجمع ما كانت تفعله عدة دوال معاً: VLOOKUP وHLOOKUP وINDEX/MATCH، مع صيغة أوضح وأخطاء أقل.
صيغة الدالة ومعنى كل جزء
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])| الجزء | معناه | إلزامي؟ |
|---|---|---|
lookup_value | القيمة التي تبحث عنها (مثلاً رمز المنتج) | نعم |
lookup_array | العمود الذي تبحث فيه | نعم |
return_array | العمود الذي تريد النتيجة منه | نعم |
if_not_found | ما يظهر إذا لم توجد القيمة | لا |
match_mode | نوع المطابقة: 0 تامة (افتراضي)، -1 أو 1 للأقرب، 2 لأحرف البدل | لا |
search_mode | اتجاه البحث: 1 من الأعلى (افتراضي)، -1 من الأسفل | لا |
المثال الأول: البحث عن سعر منتج
لديك جدول منتجات: العمود A فيه رمز المنتج، والعمود B اسمه، والعمود C سعره. تريد أن تكتب رمز المنتج في الخلية F2 فيظهر سعره تلقائياً في G2.
| A: الرمز | B: المنتج | C: السعر | |
|---|---|---|---|
| 2 | P-101 | لوحة مفاتيح | 45 |
| 3 | P-102 | فأرة لاسلكية | 30 |
| 4 | P-103 | شاشة 24 بوصة | 520 |
اكتب في الخلية G2:
=XLOOKUP(F2, A2:A4, C2:C4, "غير موجود")إذا كتبت P-103 في F2 ستظهر النتيجة 520. وإذا كتبت رمزاً غير موجود ستظهر عبارة «غير موجود» بدلاً من الخطأ المزعج #N/A.
Ctrl + T. عندها تتوسع المعادلة تلقائياً عند إضافة منتجات جديدة دون تعديل النطاقات.المثال الثاني: البحث إلى اليسار
هذه أشهر مشكلة في VLOOKUP: لا يمكنها إرجاع قيمة من عمود يقع على يسار عمود البحث. مع XLOOKUP لا يهم الترتيب أبداً. لو أردت معرفة رمز المنتج من اسمه:
=XLOOKUP("فأرة لاسلكية", B2:B4, A2:A4)النتيجة: P-102. عمود البحث B وعمود النتيجة A، والدالة تعمل بلا أي حيلة إضافية.
المثال الثالث: إرجاع أكثر من عمود دفعة واحدة
إذا أردت الاسم والسعر معاً، اجعل نطاق النتيجة يشمل العمودين:
=XLOOKUP(F2, A2:A4, B2:C4)ستمتد النتيجة تلقائياً إلى خليتين متجاورتين (الاسم ثم السعر). هذه الميزة تسمى «الصفائف الديناميكية» وتوفر عليك كتابة معادلتين.
البحث عن آخر ظهور لقيمة
لديك سجل مبيعات وتريد آخر سعر بيع لعميل معين؟ استخدم وضع البحث من الأسفل:
=XLOOKUP("أحمد", A:A, D:D, "لا يوجد", 0, -1)الرقم -1 في النهاية يجعل البحث يبدأ من آخر صف صعوداً، فتحصل على أحدث عملية.
الأخطاء الشائعة وحلولها
- ظهور
#N/Aرغم أن القيمة موجودة: غالباً بسبب مسافة زائدة في نهاية النص، أو أن الرقم مخزّن كنص. استخدمTRIMلتنظيف النصوص، أو حوّل النص إلى رقم. - ظهور
#VALUE!: نطاق البحث ونطاق النتيجة ليسا بنفس عدد الصفوف. تأكد أنA2:A100يقابلهC2:C100وليسC2:C90. - ظهور
#SPILL!: عند إرجاع أكثر من عمود، توجد بيانات في الخلايا المجاورة تمنع امتداد النتيجة. أفرغ تلك الخلايا. - ظهور
#NAME?: نسختك من إكسل قديمة لا تدعم الدالة. استخدم إكسل للويب مجاناً، أو استعمل INDEX مع MATCH.
مقارنة سريعة مع VLOOKUP
| الميزة | VLOOKUP | XLOOKUP |
|---|---|---|
| البحث إلى اليسار | ❌ | ✅ |
| المطابقة التامة افتراضياً | ❌ (يجب كتابة FALSE) | ✅ |
| رسالة مخصصة عند عدم الإيجاد | تحتاج IFERROR | ✅ مدمجة |
| تتأثر بإضافة أعمدة جديدة | نعم، تتعطل | لا |
| البحث من الأسفل | ❌ | ✅ |
الخلاصة
إذا كانت نسختك تدعم XLOOKUP فلا يوجد سبب حقيقي للاستمرار في VLOOKUP. ابدأ بالصيغة البسيطة ذات الأجزاء الثلاثة، ثم أضف رسالة «غير موجود»، وبعدها جرّب البحث من الأسفل وإرجاع عدة أعمدة. خلال أسبوع ستصبح أسرع أداة في يدك.
تمرين: أنشئ جدولاً لعشرة موظفين (الرقم، الاسم، القسم، الراتب)، ثم اصنع خلية بحث تُظهر القسم والراتب معاً بمجرد كتابة رقم الموظف.