شاهد الفيديو (دقيقة واحدة):
إذا كنت تستخدم VLOOKUP منذ مدة، فأنت تعرف مشكلاتها. فهي تحتاج إلى رقم عمود، وتتعطّل عندما يُدرج أحدهم عمودًا جديدًا، ولا تستطيع البحث نحو اليسار، وتملأ ورقتك بأخطاء #N/A. أما XLOOKUP فتحلّ كل هذه المشكلات، وهي أسهل في القراءة أيضًا.
ورقة العمل النموذجية
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Name | Department | Ext | Find | Result | |
| 2 | E101 | Abd Almouhimen Shamout | IT Support | 2101 | E103 | ||
| 3 | E102 | Abd Shamout | Network | 2102 | |||
| 4 | E103 | Mouhimen Dev | Web Dev | 2103 | |||
| 5 | E104 | Abd A. Shamout | Help Desk | 2104 |
نزّلها بصيغة CSV: excel-xlookup-sample.csv (أرفق الملف عند النشر)
الطريقة القديمة: VLOOKUP
=VLOOKUP(F2,A1:D5,2,FALSE)
تُرجع هذه الصيغة Mouhimen Dev. لكن الرقم 2 هنا رقم عمود مكتوب يدويًا (Hard-coded). فإذا أدرجت عمودًا جديدًا بين A وB (مثل عمود البريد الإلكتروني)، فستُرجع الصيغة البريد الإلكتروني بدلًا من الاسم دون أي تنبيه. لن يظهر خطأ، بل بيانات خاطئة فقط. كذلك لا تستطيع VLOOKUP إرجاع قيمة من عمود يقع على يسار العمود الذي تبحث فيه.
XLOOKUP في 3 أجزاء
=XLOOKUP(F2,A2:A5,B2:B5)
- ما الذي تبحث عنه:
F2(المعرّف، أي E103) - أين تبحث:
A2:A5(عمود المعرّفات) - ما الذي تُرجعه:
B2:B5(عمود الأسماء)
النتيجة: Mouhimen Dev. لا يوجد رقم عمود تحتاج إلى عدّه. وإذا أدرجت عمودًا جديدًا، تتحرّك النطاقات (Ranges) معه، فتستمر الصيغة في العمل بشكل صحيح.
وتبحث نحو اليسار أيضًا
ضع اسمًا في الخلية F2، ثم بدّل موضعي النطاقين:
=XLOOKUP(F2,B2:B5,A2:A5)
تبحث الصيغة في عمود الأسماء، وتُرجع المعرّف من العمود A الذي يقع على اليسار. وهذا ما لا تستطيع VLOOKUP فعله.
ميزة إضافية: استبدل #N/A بنص من اختيارك
الوسيط (Argument) الرابع هو ما يُعرض عندما لا يُعثر على نتيجة:
=XLOOKUP(F2,A2:A5,B2:B5,"Not found")
ابحث عن E999 وستحصل على Not found بدلًا من #N/A. لم تعد بحاجة إلى تغليف كل صيغة بالدالة IFERROR.
الصيغة الكاملة
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
match_mode: القيمة 0 للتطابق التام (الافتراضي)، و-1 للتطابق التام أو القيمة الأصغر التالية، و1 للتطابق التام أو القيمة الأكبر التالية، و2 للبحث باستخدام أحرف البدل (Wildcards).search_mode: القيمة 1 للبحث من الأول إلى الأخير (الافتراضي)، و-1 للبحث من الأخير إلى الأول (وهو مفيد جدًا لإيجار "أحدث إدخال").
ما إصدارات Excel التي تدعم XLOOKUP؟
تعمل XLOOKUP في Microsoft 365 وExcel 2021 وما بعده وExcel للويب. وهي غير متوفرة في Excel 2019 أو Excel 2016. إذا كنت تشارك ملفاتك مع أشخاص يستخدمون إصدارات أقدم، فسيظهر لهم خطأ. استخدم INDEX مع MATCH في هذه الملفات.
قائمة تحقق سريعة
- استبدل
VLOOKUP(x, table, n, FALSE)بـXLOOKUP(x, key_column, result_column) - استخدم نطاقات كاملة بالحجم نفسه لعمود البحث وعمود الإرجاع
- أضف
"Not found"(أو"") وسيطًا رابعًا - تأكد من أن كل من يفتح الملف لديه Excel 2021 أو Microsoft 365
المصادر
- دعم Microsoft: الدالة XLOOKUP
شاهد الفيديو:
عبد المهيمن شموط، Take away support: حلول تقنية تصلك في أقل من دقيقتين. المزيد على mouhimen.dev.
هذا المقال ترجمة عربية للمقال الأصلي باللغة الإنجليزية للكاتب عبد المهيمن شموط على mouhimen.dev.
