دالة XLOOKUP وVLOOKUP في Excel: البحث وجلب البيانات بخطوات سهلة
عندك جدول فيه مئات المنتجات أو الموظفين، وتريد أن تكتب الرقم أو الاسم فيجلب Excel باقي المعلومات وحده؟ هذا بالضبط عمل دوال البحث. سنبدأ بـ VLOOKUP المعروفة، ثم ننتقل إلى XLOOKUP التي تحل كل مشاكلها.
سنطبّق كل الأمثلة على هذا الجدول. في الواجهة العربية يبدأ العمود A من اليمين:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | رقم المنتج | المنتج | السعر | الكمية |
| 2 | 101 | طابعة | 400 | 12 |
| 3 | 102 | شاشة | 900 | 7 |
| 4 | 103 | لوحة مفاتيح | 120 | 30 |
| 5 | 104 | فأرة | 60 | 45 |
دالة VLOOKUP: الصيغة ومعنى كل جزء
=VLOOKUP(القيمة المطلوبة, الجدول, رقم العمود, FALSE)
| الجزء | معناه |
|---|---|
| القيمة المطلوبة | ما تبحث عنه، مثل رقم المنتج 103. يمكن أن تكون خلية فيها الرقم. |
| الجدول | النطاق كله، ويجب أن يبدأ بالعمود الذي تبحث فيه: A2:D5. |
| رقم العمود | رقم العمود الذي تريد النتيجة منه، تعدّه من بداية الجدول: المنتج = 2، السعر = 3. |
| FALSE | مطابقة تامة. اكتبها دائمًا، وإلا قد يعطيك Excel نتيجة خاطئة دون أي رسالة. |
مثال: سعر المنتج رقم 103:
=VLOOKUP(103,A2:D5,3,FALSE) ← النتيجة 120
وإذا كتبت رقم المنتج في الخلية G1، استخدم =VLOOKUP(G1,A2:D5,3,FALSE) فتتغير النتيجة كلما غيّرت الرقم.
مشاكل VLOOKUP التي ستواجهها
- تبحث لليسار فقط (أي في الأعمدة بعد عمود البحث). لا تستطيع أن تبحث بالاسم في العمود B وتجلب الرقم من العمود A.
- رقم العمود ثابت: إذا أدرجت عمودًا جديدًا في وسط الجدول، تشير المعادلة إلى العمود الخطأ.
- نسيان FALSE يعطي نتائج خاطئة بصمت.
دالة XLOOKUP: البديل الأسهل والأقوى
متوفرة في Microsoft 365 وExcel 2021 وما بعدها. بدل رقم العمود، تحدد عمود البحث وعمود النتيجة مباشرة:
=XLOOKUP(القيمة المطلوبة, عمود البحث, عمود النتيجة, إذا لم توجد)
نفس المثال: =XLOOKUP(103,A2:A5,C2:C5) ← 120
البحث في أي اتجاه
رقم منتج «شاشة»، والرقم في عمود قبل عمود الاسم — وهذا مستحيل مع VLOOKUP:
=XLOOKUP("شاشة",B2:B5,A2:A5) ← 102
رسالة مفهومة بدل #N/A
=XLOOKUP(G1,A2:A5,B2:B5,"غير موجود")
جلب أكثر من عمود بمعادلة واحدة
اجعل عمود النتيجة نطاقًا من عدة أعمدة، فتنتشر النتيجة وحدها في الخلايا المجاورة:
=XLOOKUP(G1,A2:A5,B2:D5) ← المنتج والسعر والكمية معًا.
المقارنة السريعة
| VLOOKUP | XLOOKUP | |
|---|---|---|
| اتجاه البحث | للأعمدة التي بعد عمود البحث فقط | أي اتجاه |
| المطابقة التامة | يجب كتابة FALSE | افتراضية |
| إدراج عمود في الجدول | قد يخرّب النتيجة | لا يؤثر |
| رسالة عند عدم الوجود | تحتاج IFERROR | مدمجة في الدالة |
| الإصدارات | كل الإصدارات | 365 و2021 وما بعدها |
حل خطأ #N/A
- القيمة غير موجودة فعلًا في عمود البحث — تأكد بـ Ctrl+F.
- مسافة زائدة في آخر النص: «شاشة » ليست «شاشة». نظّف العمود بالدالة
TRIM. - رقم مخزّن كنص: إذا ظهر مثلث أخضر في زاوية الخلية، حوّله إلى رقم.
- النطاق تحرك عند النسخ: ثبّت الجدول بعلامة $ —
$A$2:$D$5— اضغط F4 بعد تحديده.