دوال البحث في Excel: VLOOKUP وXLOOKUP وINDEX مع MATCH بأمثلة عملية

عندك ملف فيه 500 طالب، وتريد أن تكتب الرقم المدني أو رقم الطالب فتظهر درجته وفصله تلقائيًا؟ أو تريد نقل درجات من ملف إلى آخر حسب الاسم؟ هذا عمل دوال البحث. نشرح هنا أشهرها: VLOOKUP التقليدية، وXLOOKUP الأحدث والأسهل، والثنائي القوي INDEX مع MATCH.

البيانات في أمثلتنا

جدول الطلاب في النطاق A2:D40: العمود A رقم الطالب، وB الاسم، وC الدرجة، وD الفصل. والخلية F2 نكتب فيها رقم الطالب الذي نبحث عنه.

1. VLOOKUP: البحث العمودي

الصيغة:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: القيمة التي تبحث عنها (رقم الطالب في F2). ويجب أن تكون في العمود الأول من الجدول.
table_array: نطاق الجدول كله.
col_index_num: رقم العمود الذي تريد النتيجة منه، بدءًا من 1 لأول عمود.
range_lookup: اكتب FALSE للمطابقة التامة. وهذا ما تحتاجه دائمًا تقريبًا.

مثال: درجة الطالب

=VLOOKUP(F2,$A$2:$D$40,3,FALSE)

يبحث عن رقم الطالب في العمود الأول، ويعيد ما في العمود الثالث (الدرجة). وللاسم غيّر 3 إلى 2.

أخطاء شائعة في VLOOKUP

نسيت FALSE: إن تركتها فارغة يبحث Excel بمطابقة تقريبية، فيعطي نتائج خاطئة إن لم يكن العمود الأول مرتبًا.
#N/A: القيمة غير موجودة، أو فيها مسافة زائدة، أو الرقم مخزن نصًا.
#REF!: رقم العمود أكبر من عدد أعمدة الجدول.
لا تبحث لليسار: VLOOKUP لا تعيد قيمة من عمود على يسار عمود البحث.
ثبّت نطاق الجدول بعلامات $ حتى لا يتحرك عند النسخ.

مثال دالة VLOOKUP لإظهار درجة الطالب برقمه. لاحظ أن الفاصل في هذا الجهاز هو ; بدل , حسب إعدادات اللغة

2. HLOOKUP: البحث الأفقي

مثل VLOOKUP لكنها تبحث في الصف الأول بدل العمود الأول، وتعيد قيمة من صف تحدده:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

3. XLOOKUP: الدالة الحديثة (الأفضل)

الصيغة:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

مثال: درجة الطالب

=XLOOKUP(F2,A2:A40,C2:C40,"الطالب غير موجود")

لماذا هي أفضل؟

المطابقة التامة هي الافتراضية، فلا داعي لكتابة FALSE.
تبحث في أي اتجاه، يمينًا أو يسارًا.
لا تحتاج رقم عمود؛ تحدد عمود النتيجة مباشرة، فلا تتأثر بإضافة أعمدة.
تكتب رسالة مناسبة بدل خطأ #N/A عبر if_not_found.
يمكنها إعادة أكثر من عمود: =XLOOKUP(F2,A2:A40,B2:D40) تعيد الاسم والدرجة والفصل معًا.

match_mode: 0 مطابقة تامة (افتراضي)، و-1 تامة أو الأصغر التالي، و1 تامة أو الأكبر التالي، و2 رموز بديلة.

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

مثال ذكي: التقدير من جدول الحدود

اكتب جدولًا صغيرًا: في H2:H6 الحدود (0، 50، 65، 80، 90)، وفي I2:I6 التقديرات (راسب، مقبول، جيد، جيد جدًا، ممتاز). ثم:

=XLOOKUP(C2,$H$2:$H$6,$I$2:$I$6,,-1)

الوضع -1 يعني: إن لم يجد الدرجة نفسها، يأخذ الحد الأصغر التالي. فالطالب صاحب 73 يحصل على «جيد». وهذه طريقة أنظف من IF المتداخلة.

مثال دالة XLOOKUP مع رسالة عند عدم العثور

4. INDEX مع MATCH: الثنائي المرن

تنصح Microsoft: إذا لم يكن عمود البحث على يسار عمود النتيجة، فلا تستخدم VLOOKUP، واستخدم INDEX مع MATCH.

MATCH(lookup_value, lookup_array, 0): تعيد موقع القيمة في القائمة (مثلًا 7). والرقم 0 للمطابقة التامة.
INDEX(array, row_num): تعيد القيمة الموجودة في موقع محدد.

مثال: اسم الطالب من رقمه، أيًا كان موقع العمودين:

=INDEX(B2:B40,MATCH(F2,A2:A40,0))

MATCH تجد صف الطالب، وINDEX تعيد الاسم من ذلك الصف. وتعمل في كل إصدارات Excel.

5. XMATCH باختصار

نسخة حديثة من MATCH، مطابقتها تامة افتراضيًا: =XMATCH(F2,A2:A40) تعيد ترتيب الطالب في القائمة. متاحة في مايكروسوفت 365 و2021 وما بعده.

نصائح لنتائج صحيحة

احذف المسافات الزائدة من البيانات بالدالة TRIM.
تأكد أن الأرقام مخزنة أرقامًا لا نصوصًا في الجدولين.
رقم الطالب أفضل من الاسم للبحث، لأن الأسماء قد تتكرر أو تُكتب بطرق مختلفة (أحمد/احمد).
عند نقل درجات بين ملفين، افتح الملفين معًا أثناء كتابة الصيغة.

الخلاصة

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

المصادر

شرح البرامج

القائمة الرئيسية

الصفحات

الاخبار[LastPost]