دالة VLOOKUP في Excel

دالةالبحث الرأسي (VLOOKUP) في Excelأداة قوية تساعدك في البحث عن قيمة معيّنة من خلال مطابقتها مع العمود الأول من جدول أو نطاق بشكل عمودي، ثم إرجاع القيمة المقابلة من عمود آخر في نفس الصف. وعلى الرغم من أن دالة VLOOKUP مفيدة للغاية، إلا أنها قد تبدو معقّدة أحيانًا للمبتدئين. ويهدف هذا البرنامج التعليمي إلى مساعدتك على إتقان دالة VLOOKUP من خلال تقديمشرح تفصيلي خطوة بخطوة للوُسائط، وأمثلة مفيدة، وحلول للأخطاء الشائعةالتي قد تواجهها عند استخدام دالة VLOOKUP.

• شرح تفصيلي للوسيطات خطوة بخطوة
• الصيغة والوسيطات
• أمثلة على دالة VLOOKUP
  المطابقة التامة مقابل المطابقة التقريبية|VLOOKUP مع شروط متعددة
• الأخطاء الشائعة لدالة VLOOKUP والحلول
  خطأ #N/A|خطأ #VALUE|خطأ #REF|قيمة غير صحيحة

أمثلة البحث الرأسي (VLOOKUP).xlsx أخطاء البحث الرأسي (VLOOKUP).xlsx

مقاطع الفيديو ذات الصلة

شرح تفصيلي للوسيطات خطوة بخطوة

كما يظهر في لقطة الشاشة أعلاه، تُستخدم دالة VLOOKUP للعثور على البريد الإلكتروني بناءً على رقم معرّف معيّن. سأشرح الآن بالتفصيل كيفية استخدام دالة VLOOKUP في هذا المثال، من خلال تحليل كل وسيط على حدة.

الخطوة 1: ابدأ دالة VLOOKUP

اختر خلية (مثل H6 في هذه الحالة) لإظهار النتيجة، ثم ابدأ دالة VLOOKUP بكتابة المحتوى التالي فيشريط الصيغة.

=VLOOKUP(

الخطوة 2: حدد قيمة البحث

أولاً، حدد قيمة البحث (وهي القيمة التي تبحث عنها) في دالة VLOOKUP. هنا، أشير إلى الخلية G6 التي تحتوي على رقم المعرّف 1005.

=VLOOKUP(G6

الخطوة 3: حدد مصفوفة الجدول

بعد ذلك، حدد نطاق الخلايا الذي يحتوي على كلٍ من القيمة التي تبحث عنها والقيمة التي تريد إرجاعها. في هذه الحالة، نختار النطاق B6:E12، لتصبح الصيغة كما يلي:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

الخطوة 4: حدّد العمود الذي تريد إرجاع قيمة منه

ثم حدد العمود الذي ترغب في استرداد القيمة منه.

في هذا المثال، وبما أنني بحاجة إلى استرجاع البريد الإلكتروني استنادًا إلى رقم المعرِّف، أُدخل الرقم 4 هنا لأُوجِّه دالة VLOOKUP إلى إرجاع القيمة من العمود الرابع في نطاق البيانات.

=VLOOKUP(G6,B6:E12,4

الخطوة 5: ابحث عن تطابق تقريبي أو تطابق تام

أخيرًا، حدد ما إذا كنت تبحث عن تطابق تقريبي أو تطابق تام.

• لإيجادمطابقة تامة، عليك استخدامFALSE كآخر وسيطة.
• لإيجادمطابقة تقريبية، استخدمTRUE كآخر وسيطة، أو اتركها فارغة.

في هذا المثال، أستخدم FALSE للحصول على تطابق تام. والآن أصبحت الصيغة كما يلي:

=VLOOKUP(G6,B6:E12,4,FALSE

اضغط مفتاح الإدخال (Enter) للحصول على النتيجة

بفضل شرح كل وسيط على حدة في المثال أعلاه، أصبحت بنية دالة VLOOKUP ووُسائطهاMuch أسهل للفهم بكثير.

الصيغة والوسيطات

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

• Lookup_value (مطلوبة): القيمة التي تبحث عنها (سواءً كانت قيمة فعلية أو مرجع خلية). تذكّر أن هذه القيمة يجب أن تكون موجودة في العمود الأول منtable_array.
• Table_array (مطلوبة): نطاق الخلايا الذي يحتوي على عمود القيمة المراد البحث عنها وعمود القيمة المراد إرجاعها.
• Col_index (مطلوبة): عدد صحيح يُعبّر عن رقم العمود الذي يحتوي على القيمة المراد إرجاعها. يبدأ العد من الرقم 1 للعمود الأيسر فيtable_array.
• Range_lookup(اختيارية): قيمة منطقية تحدد ما إذا كنت تريد أن تبحث دالة VLOOKUP عن مطابقة تقريبية أو مطابقة تامة.
  • مطابقة تقريبية – عيّن هذا المعامل إلىTRUE أو1، أو اتركهفارغًا.
    هام: للعثور على مطابقة تقريبية، يجب أن تكون القيم في العمود الأول من table_array مرتبة بترتيب تصاعدي؛ وإلا فقد تُرجع دالة VLOOKUP نتيجةً خاطئة.
  • مطابقة تامة – عيِّن هذا المعامل إلىFALSE أو0.

أمثلة

يعرض هذا القسم بعض الأمثلة لمساعدتك على فهم دالة VLOOKUP بشكل أعمق.

المثال 1: التطابق التام مقابل التطابق التقريبي في VLOOKUP

إذا كنت مرتبكًا بشأن الفرق بين التطابق التام والتطابق التقريبي عند استخدام VLOOKUP، فإن هذا القسم سيساعدك على توضيح هذا اللبس.

التطابق التام في VLOOKUP

في هذا المثال، سنعثر على الأسماء المقابلة استنادًا إلى الدرجات المذكورة في النطاق E6:E8. أدخل الصيغة التالية في الخلية F6، ثم اسحب مقبض التعبئة التلقائية لأسفل حتى الخلية F8. في هذه الصيغة، تم تعيين الوسيط الأخير كـFALSE لإجراء بحث يعتمد على تطابق تام.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

ومع ذلك، وبما أن الدرجة 98 غير موجودة في العمود الأول من نطاق البيانات، تُرجع دالة VLOOKUP نتيجة خطأ #N/A.

التطابق التقريبي في VLOOKUP

باستخدام المثال نفسه أعلاه، إذا غيّرت الوسيط الأخير إلىTRUE، فستُجري دالة VLOOKUP بحثًا عن تطابق تقريبي. وفي حال عدم العثور على تطابق، ستبحث عن أكبر قيمة أقل من قيمة البحث وترجع النتيجة المقابلة لها.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

بما أن الدرجة 98 غير موجودة، تبحث دالة VLOOKUP عن أكبر قيمة أقل من 98، وهي 95، وترجع اسم الدرجة المرتبطة بها كأقرب نتيجة.

المثال 2: استخدام VLOOKUP مع معايير متعددة

يعرض هذا القسم كيفية استخدام VLOOKUP مع شروط متعددة في Excel. كما هو موضح في لقطة الشاشة أدناه، إذا كنت تحاول العثور على راتب استنادًا إلى اسم معيّن (في الخلية H5) وقسم (في الخلية H6)، فاتبع الخطوات التالية لإتمام ذلك.

الخطوة 1: أضف عمود مساعد لدمج القيم من أعمدة البحث

في هذه الحالة، نحتاج إلى إنشاء عمود مساعد لدمج القيم منالاسموالقسم.

1. أضف عمود مساعد إلى يسار نطاق البيانات وأعطِ رأسًا لهذا العمود. انظر لقطة الشاشة:
   
   
2. في عمود المساعدة هذا، حدد الخلية الأولى أسفل الرأس، ثم أدخل الصيغة التالية فيشريط الصيغةواضغط علىEnter.
   

   =C6&" "&D6

   
   ملاحظات: في هذه الصيغة، نستخدم علامة العطف (&) لدمج النص الموجود في عمودين لإنشاء جزء واحد من النص.

   • C6 هو الخلية الأولى في عمودالاسمالذي تريد ربطه، وD6 هي الخلية الأولى في عمودالقسمالذي تريد ربطه.
   • تندمجان قيمتا الخليتين مع ترك مسافة بينهما.
3. حدد خلية النتيجة هذه، ثم اسحبمقبض الملء التلقائيلأسفل لتطبيق هذه الصيغة على الخلايا الأخرى في نفس العمود.
   
   

الخطوة 2: طبّق دالة VLOOKUP مع المعايير المحددة

اختر خلية لإظهار النتيجة (هنا اخترنا I7)، وأدخل الصيغة التالية فيشريط الصيغة، ثم اضغطEnter.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

النتيجة

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

يعرض هذا القسم الأخطاء الشائعة التي قد تواجهك عند استخدام VLOOKUP، ويقدّم الحلول الفعّالة لإصلاحها.

ظهور خطأ #N/A

الخطأ الأكثر شيوعًا مع VLOOKUP هو خطأ #N/A، والذي يعني أن Excel لم يستطع العثور على القيمة التي كنت تبحث عنها. فيما يلي بعض الأسباب التي قد تجعل VLOOKUP تُرجع خطأ #N/A.

السبب 1: قيمة البحث غير موجودة في العمود الأول من مصفوفة الجدول

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

كما هو موضح في لقطة الشاشة أدناه، أريد استرجاع اسمٍ بناءً على المسمى الوظيفي المُعطى. هنا، تقع قيمة البحث ()مدير المبيعات) في العمود الثاني من مصفوفة الجدول، بينما تقع القيمة المراد إرجاعها إلى يسار عمود البحث، ولذلك تُرجع دالة VLOOKUP خطأ #N/A.

الحلول

يمكنك تطبيق أيٍّ من الحلول التالية لإصلاح هذا الخطأ.

• أعد ترتيب الأعمدة
  
  يمكنك إعادة ترتيب الأعمدة بحيث يصبح عمود البحث في العمود الأول من table_array.
• استخدم دالتَي INDEX وMATCH معًا
  
  هنا نستخدم دالتَي INDEX وMATCH معًا كبديلٍ أقوى وأكثر مرونة لدالة VLOOKUP لحل هذه المشكلة.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• استخدم دالة XLOOKUP (المتاحة في Excel 365 وExcel 2021 والإصدارات الأحدث)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

السبب 2: قيمة البحث غير موجودة في عمود البحث (تطابق تام)

أحد الأسباب الأكثر شيوعًا لظهور خطأ #N/A من VLOOKUP هو أن القيمة التي تبحث عنها غير موجودة.

كما هو موضح في المثال أدناه، سنقوم بالعثور على الاسم استنادًا إلى الدرجة المعيّنة 98 في E6. ومع ذلك، هذه الدرجة غير موجودة في العمود الأول من نطاق البيانات، لذا تُرجع دالة VLOOKUP نتيجة خطأ #N/A.

حلول

لإصلاح هذا الخطأ، يمكنك تجربة أحد الحلول التالية.

• إذا أردت أن تبحث دالة VLOOKUP عن القيمة الأكبر التالية الأقل من قيمة البحث، غيّر آخر وسيطةFALSE (مطابقة تامة) إلىTRUE (مطابقة تقريبية). لمزيد من المعلومات، يُرجى مراجعةالمثال 1: المطابقة التامة مقابل المطابقة التقريبية باستخدام VLOOKUP.
• لتجنب تغيير آخر وسيطة والحصول على تذكير في حال عدم العثور على قيمة البحث، يمكنك وضع دالة VLOOKUP داخل دالة IFERROR:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

السبب 3: قيمة البحث أصغر من أصغر قيمة في عمود البحث (تطابق تقريبي)

كما يظهر في لقطة الشاشة أدناه، أنت تقوم بإجراء بحث تطابق تقريبي. وبما أن القيمة التي تبحث عنها (وهي رقم المعرّف 1001 في هذه الحالة) أصغر من أصغر قيمة في عمود البحث (وهي 1002)، فإن دالة VLOOKUP تُرجع خطأ #N/A.

حلول

إليك حلّان لك.

• تأكد من أن قيمة البحث القيمة أكبر من أو تساوي هي القيمة الأصغر في عمود البحث.
• إذا أردت أن يذكّرك Excel بعدم العثور على قيمة البحث، قم بتضمين دالة VLOOKUP داخل دالة IFERROR كما يلي:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

السبب 4: الأرقام منسَّقة كنص

كما ترى في لقطة الشاشة أدناه، يعود سبب ظهور خطأ #N/A في هذا المثال إلى عدم تطابق نوع البيانات بين خلية البحث (G6) وعمود البحث (B6:B12) في الجدول الأصلي. هنا، القيمة في G6 هي رقم، بينما القيم في النطاق B6:B12 هي أرقام منسَّقة كنص.

حلول

لحل هذه المشكلة، تحتاج إلى تحويل قيمة البحث مرة أخرى إلى رقم. إليك طريقتان لك.

• طبّق ميزة تحويل إلى رقم
  
  انقر على الخلية التي تريد تحويل النص إلى القيمة فيها، وحدد هذا الزرالموجود بجانب الخلية، ثم اخترتحويل إلى رقم.
  
• طبّق أداة مفيدة لتحويل دفعة من التحويل بين النص والقيمة
  
  ميزةالتحويل بين النص والقيمةفيKutools لـ Excelتساعدك على تحويل نطاق من الخلايا بسهولة من النص إلى القيمة والعكس بالعكس.احصل الآن على تجربة مجانية بكامل الميزات لمدة 30 يومًا!

السبب 5: إن table_array ليس ثابتًا عند سحب صيغة VLOOKUP إلى خلايا أخرى

كما هو موضح في لقطة الشاشة أدناه، يوجد اثنان من نطاق قيمة البحث في الخليتين E6 وE7. بعد الحصول على النتيجة الأولى في F6، عند سحب صيغة VLOOKUP من الخلية F6 إلى F7، تظهر نتيجة خطأ #N/A. وذلك لأن مراجع الخلايا (B6:C12) نسبية بشكل افتراضي، وتتغيّر تلقائيًا عند التنقّل لأسفل عبر الصفوف. ولذلك، يصبح نطاق الجدول B7:C13، الذي لم يعد يحتوي على قيمة البحث 73.

الحل

لإبقاء نطاق الجدول ثابتًا، تحتاج إلى تثبيته بإضافة علامة$ قبل أرقام الصفوف والأعمدة في مراجع الخلايا. لمعرفة المزيد حول المرجع المطلق في Excel، راجع هذا البرنامج التعليمي: المرجع المطلق في Excel (كيفية إنشائه واستخدامه).

ظهور خطأ #VALUE

قد تؤدي الشروط التالية إلى ظهور نتيجة خطأ #VALUE من دالة VLOOKUP.

السبب 1: تتجاوز قيمة البحث 255 حرفًا

كما هو موضح في لقطة الشاشة أدناه، فإن قيمة البحث في الخلية H4 تتجاوز 255 حرفًا، ولذلك تُرجع دالة VLOOKUP نتيجة خطأ #VALUE.

الحلول

لتخطي هذا القيد، يمكنك استخدام دالة بحث بديلة تدعم السلاسل الأطول. جرّب إحدى الصيغ التالية.

• INDEX وMATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• دالة XLOOKUP(المتاحة في Excel 365 وExcel 2021 والإصدارات الأحدث):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

السبب 2: معامل col_index أقل من 1

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

إذا أدخلت رقم عمود أقل من 1 (مثل الصفر أو أي رقم سالب)، فلن تتمكّن دالة VLOOKUP من تحديد موقع العمود ضمن نطاق الجدول.

الحل

لإصلاح هذه المشكلة، تأكد من أن معامل مؤشر العمود في صيغة VLOOKUP الخاصة بك عددٌ موجب يتوافق مع عمودٍ صالح ضمن نطاق الجدول.

ظهور خطأ #REF

يعرض هذا القسم سببًا واحدًا لظهور خطأ #REF من دالة VLOOKUP ويقدّم حلولًا لهذه المشكلة.

السبب: معامل col_index أكبر من عدد الأعمدة

كما ترى في لقطة الشاشة أدناه، يحتوي نطاق الجدول على 4 أعمدة فقط. ومع ذلك، فإن رقم العمود الذي حددته في صيغة VLOOKUP هو 5، وهو أكبر من عدد الأعمدة في نطاق الجدول. ونتيجةً لذلك، لن تتمكن دالة VLOOKUP من تحديد موقع العمود وستُرجع في النهاية خطأ #REF.

الحلول

• حدّد رقم عمود صحيح
  تأكد من أن وسيطة رقم العمود في صيغة VLOOKUP الخاصة بك تمثّل رقمًا يتوافق مع عمود صالح في مصفوفة الجدول.
• احصل تلقائيًا على رقم العمود بناءً على رأس العمود المحدد
  إذا كان جدولك يحتوي على عددٍ كبير من الأعمدة، فقد يصعب عليك تحديد رقم فهرس العمود الصحيح بدقة. لحل هذه المشكلة، يمكنك دمج دالة MATCH داخل دالة VLOOKUP للعثور على موقع العمود تلقائيًا استنادًا إلى عنوانه.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  ملاحظة: في الصيغة أعلاه، تُستخدم دالةMATCH(«Email»,B5:E5, 0) للحصول على رقم العمود الخاص بعمود "Email" ضمن النطاق B6:E12. تكون النتيجة هنا 4، وتُستخدم مباشرةً كقيمةcol_index في دالةVLOOKUP.

إرجاع قيمة غير صحيحة

إذا لاحظت أن دالة VLOOKUP لا تُرجع النتيجة الصحيحة، فقد يكون ذلك ناتجًا عن الأسباب التالية

السبب 1: عمود البحث غير مرتب بترتيب تصاعدي

إذا قمت بتعيين المعامل الأخير علىTRUE(أو)تركته فارغًا) للحصول على مطابقة تقريبية، وكان عمود البحث غير مرتب بترتيب تصاعدي، فقد تكون القيمة الناتجة غير صحيحة.

الحل

يمكنك حل هذه المشكلة بفرز عمود البحث بترتيب تصاعدي. وللقيام بذلك، يُرجى اتباع الخطوات التالية:

1. حدد خلايا البيانات في عمود البحث، ثم انتقل إلى تبويببيانات، وانقر علىفرز من الأصغر إلى الأكبرفي مجموعةفرز وتصفية.
2. في مربع حوارتحذير الفرز، اختر الخيارتوسيع التحديد، ثم انقر علىموافق.

السبب 2: تم إدراج عمود أو حذفه

كما يظهر في لقطة الشاشة أدناه، كانت القيمة التي أردت إرجاعها في الأصل تقع في العمود الرابع من نطاق الجدول، لذا حددت رقم مؤشر العمود بـ 4. ولكن بعد إدراج عمود جديد، أصبح عمود النتيجة هو العمود الخامس في نطاق الجدول، ما تسبب في إرجاع دالة VLOOKUP لنتيجة من عمود خاطئ.

الحلول

إليك حلين لك.

• يمكنك تغيير رقم فهرس العمود يدويًا ليتطابق مع موقع عمود الإرجاع. يجب تغيير الصيغة هنا إلى:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• إذا أردت دائمًا إرجاع النتيجة من عمود معين، مثل عمود البريد الإلكتروني في هذا المثال، يمكن للصيغة التالية أن تساعدك على مطابقة فهرس العمود تلقائيًا بناءً على رأس العمود المحدد، بغض النظر عما إذا تم إدراج أعمدة أو حذفها من مصفوفة الجدول.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

ملاحظات حول دوال أخرى

• تدعم دالة VLOOKUP البحث عن القيمة من اليسار إلى اليمين فقط.
  يجب أن تكون قيمة البحث في العمود الأيسر، بينما تظهر قيمة النتيجة في أي عمود يقع إلى يمين عمود البحث.
• إذا تركت آخر وسيطة فارغة، تستخدم دالة VLOOKUP المطابقة التقريبية افتراضيًا.
• تقوم دالة VLOOKUP بعملية بحث لا تأخذ حالة الأحرف بعين الاعتبار.
• في حالة وجود تطابقات متعددة، تُرجع دالة VLOOKUP أول تطابق تجده في مصفوفة الجدول، بناءً على ترتيب الصفوف في مصفوفة الجدول.

مقالات ذات صلة