دالة 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: البحث عن مطابقة تقريبية أو تامة

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

• للعثور على مطابقة تامة، يجب عليك استخدام لا في الوسيطة الأخيرة.
• للعثور على مطابقة تقريبية، استخدم نعم كوسيط أخير، أو اتركها فارغة.

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

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

اضغط على مفتاح Enter للحصول على النتيجة

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

البنية والوسيطات

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

• قيمة البحث (مطلوبة): القيمة (قيمة فعلية أو مرجع خلية) التي تبحث عنها. تذكر أن هذه القيمة يجب أن تكون في العمود الأول من نطاق الجدول.
• نطاق الجدول (مطلوب): نطاق من الخلايا يحتوي على كل من عمود قيمة البحث وعمود قيمة الإرجاع.
• مؤشر العمود (مطلوب): عدد صحيح يمثل رقم العمود الذي يحتوي على قيمة الإرجاع. يبدأ بالرقم1 للعمود الأيسر من نطاق الجدول.
• البحث في النطاق (اختياري): قيمة منطقية تحدد ما إذا كنت تريد من VLOOKUP البحث عن مطابقة تقريبية أو مطابقة تامة.
  • مطابقة تقريبية - عيّن هذه الوسيطة إلى نعم, 1 أو اتركها فارغة.
    هام: للبحث عن مطابقة تقريبية، يجب ترتيب القيم في العمود الأول من نطاق الجدول تصاعديًا لتجنب نتائج خاطئة من VLOOKUP.
  • مطابقة تامة - عيّن هذه الوسيطة إلى لا أو0.

أمثلة

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

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

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

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

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

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

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

مطابقة تقريبية في VLOOKUP

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

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

نظرًا لأن الدرجة98 غير موجودة، تبحث VLOOKUP عن أكبر قيمة أقل من98، وهي95، وتُرجع اسم صاحب الدرجة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 أنها تسمح بالبحث فقط من اليسار إلى اليمين. لذا يجب أن تكون قيم البحث في العمود الأول من نطاق الجدول.

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

الحلول

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

• إعادة ترتيب الأعمدة
  
  يمكنك إعادة ترتيب الأعمدة لجعل عمود البحث هو العمود الأول في نطاق الجدول.
• استخدم دالتي INDEX وMATCH معًا
  
  هنا نستخدم دالتي INDEX وMATCH معًا كبديل لـ VLOOKUP لحل هذه المشكلة.

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

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

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

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

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

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

الحلول

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

• إذا كنت تريد أن تبحث VLOOKUP عن أكبر قيمة أقل من قيمة البحث، غيّر الوسيطة الأخيرة من لا (مطابقة تامة) إلى نعم (مطابقة تقريبية). لمزيد من المعلومات، راجع مثال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: نطاق الجدول غير ثابت عند سحب صيغة 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 (متوفرة في Excel365 وExcel2021 والإصدارات الأحدث):
  

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

السبب2: وسيطة مؤشر العمود أقل من1

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

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

الحل

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

ظهور خطأ #REF

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

السبب: وسيطة مؤشر العمود أكبر من عدد الأعمدة

كما ترى في لقطة الشاشة أدناه، يحتوي نطاق الجدول على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، وتُستخدم كمؤشر عمود في دالة VLOOKUP.

ظهور قيمة غير صحيحة

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

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

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

الحل

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

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 أول تطابق تجده في نطاق الجدول، بناءً على ترتيب الصفوف في نطاق الجدول.

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