تبحث =VLOOKUP(F2,A2:D6,3,FALSE) عن القيمة الموجودة في F2 في العمود الأول من A2:D6 وتعيد القيمة من العمود الثالث في الصف نفسه. وتعني FALSE في النهاية "المطابقة التامة فقط". اختر منتجًا آخر في F2 فيتغير السعر.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
انقر G2 لترى الجدول A2:D6 محددًا. غيّر 3 في الصيغة إلى 2 فتعيد G2 الفئة بدلًا من السعر، لأن Category هو العمود الثاني في الجدول. والمطابقة تتجاهل حالة الأحرف: pear تجد Pear.
صيغة دالة VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| الوسيط | ما هو | في المثال |
|---|---|---|
lookup_value | القيمة المراد إيجادها. | F2 (Pear) |
table_array | الجدول المراد البحث فيه. تبحث VLOOKUP في عموده الأول فقط. | A2:D6 |
col_index_num | أي عمود من الجدول يُعاد، بالعد من العمود الأول للجدول (1). | 3 (Price) |
range_lookup | FALSE أو 0 للمطابقة التامة. وTRUE أو 1 أو لا شيء للمطابقة التقريبية. | FALSE |
رقم العمود يُعدّ من بداية الجدول، لا من العمود A في الورقة. في جدول يبدأ من العمود C، يعني col_index_num بالقيمة 2 العمود D. والرقم الأكبر من عرض الجدول يعيد #REF!، والرقم 0 يعيد #VALUE!.
في الاكسل المضبوط على لغة تستخدم الفاصلة العشرية، تُفصل الوسائط بفواصل منقوطة: =VLOOKUP(F2;A2:D6;3;FALSE).
اختيار عمود الإرجاع بالدالة MATCH
الرقم 3 المكتوب يدويًا يتعطل بصمت عندما يُدرج أحدهم عمودًا داخل الجدول: تستمر الصيغة في إرجاع العمود الثالث، الذي صار يحتوي على شيء آخر. دع MATCH تجد رقم العمود من العنوان بدلًا من ذلك. هنا G1 قائمة منسدلة: اختر Stock أو Category فتتبعها G2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
تعيد MATCH(G1,A1:D1,0) موضع "Price" في صف العناوين، أي 3، وتستخدمه VLOOKUP كرقم العمود: 0.8 للمنتج Carrot. هذا بحث في اتجاهين: صف يُختار بالمنتج، وعمود يُختار بالعنوان. والفكرة نفسها مكتوبة بالدالة INDEX بدلًا من VLOOKUP موجودة في صفحة INDEX وMATCH.
المطابقة التقريبية: VLOOKUP مع TRUE
مع TRUE كوسيط أخير، لا تبحث VLOOKUP عن قيمة مساوية. بل تجد أكبر قيمة أقل من قيمة البحث أو مساوية لها. وهذا ما تريده للفئات: شرائح الضرائب، والتقديرات، وأسعار الشحن، ومستويات العمولة. يجب أن يكون العمود الأول مرتبًا من الأصغر إلى الأكبر.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
القيمة 4,200 الخاصة بـ Ben ليست في العمود A. وأكبر قيمة لا تتجاوزها هي 1,000، لذا يحصل على 3%. وقيمة Cara البالغة 5,000 تطابق صف 5,000 تمامًا فتحصل على 5%. وقيمة Dev البالغة 12,500 فوق الفئة الأخيرة فيحصل على النسبة الأخيرة، 8%. والقيمة الأقل من الفئة الأولى (رقم مبيعات سالب هنا) تعيد #N/A، ولهذا يبدأ الجدول من 0.
تُبقي علامات $ في $A$2:$B$5 الجدول في مكانه عند نسخ F2 إلى الأسفل حتى F5. وبدونها، ستبحث F3 في A3:B6 وتتخطى الفئة الأولى.
حذف الوسيط الرابع يساوي TRUE. وعلى قائمة منتجات غير مرتبة يصبح هذا خللًا صامتًا: يبحث الاكسل كما لو كانت القائمة مرتبة وقد يعيد سعرًا من الصف الخطأ، أو #N/A لقيمة موجودة. عندما تبحث عن أسماء أو رموز أو معرّفات، اختم دائمًا بـ FALSE.
لماذا تعيد VLOOKUP الخطأ #N/A
يعني #N/A "غير موجود". توضح الورقة أدناه ثلاثة أسباب شائعة، ويكرر العمود G كل بحث داخل IFNA وTRIM.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A القيمة التي تبحث عنها غير موجودة في نطاق البحث.- القيمة غير موجودة في الجدول. Kiwi ليست في A2:A6. هذا "غير موجود" حقيقي، وتحوّله
IFNA(...,"Not found")إلى نص مقروء. غيّر E2 إلى Apple فيعرض العمودان السعر. - مسافات زائدة. تحتوي E3 على
"Milk "مع مسافة في آخرها، لذا لا تساويMilk. تزيلهاTRIM(E3)فتجد G3 السعر. وإذا كانت المسافات في الجدول بدلًا من ذلك، فنظّف العمود A بالدالة TRIM مرة واحدة بدلًا من استخدامها في كل بحث. - القيمة في عمود آخر. Fruit موجودة، لكن في العمود B. تبحث VLOOKUP في العمود الأول من الجدول فقط، لذا تفشل E4 في العمودين. ابدأ الجدول من العمود الذي تبحث فيه، أو استخدم XLOOKUP، التي تأخذ عمود البحث وعمود الإرجاع بشكل منفصل.
استخدم IFNA بدلًا من IFERROR حول البحث. تلتقط IFNA الخطأ #N/A فقط، لذا يبقى الخطأ #REF! الناتج عن رقم عمود خاطئ ظاهرًا بدلًا من أن يُخفى على أنه "Not found".
سببان آخران:
- أرقام مخزنة كنص. إذا كان العمود A يحتوي على رموز منتجات مكتوبة كنص (غالبًا بعد استيراد، مع مثلث أخضر صغير في الزاوية) وكانت F2 تحتوي على الرقم 101، فإن
=VLOOKUP(F2,A2:B6,2,FALSE)تعيد #N/A مع أن 101 تظهر في القائمة. حوّل أحد الطرفين: تبحث=VLOOKUP(F2&"",A2:B6,2,FALSE)عن النص "101"، وتبحث=VLOOKUP(VALUE(F2),A2:B6,2,FALSE)عن رقم عندما تكون F2 هي النص. - المطابقة التقريبية على بيانات غير مرتبة، كما وُصفت في القسم السابق.
VLOOKUP تعيد 0 بدلًا من خلية فارغة
عندما تكون الخلية التي تصل إليها VLOOKUP فارغة، يعرض الاكسل 0، لا خلية فارغة. وعندها يُقرأ الصفر في عمود Stock على أنه "نفد المخزون" بينما لم يُدخل المخزون أصلًا. أضف &"" إلى الصيغة، أو اختبر طول النتيجة:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
الأولى أقصر لكنها تحوّل كل رقم تعيده إلى نص، فتتخطاه SUM لاحقًا. والثانية تُبقي الأرقام أرقامًا.
VLOOKUP من ورقة أخرى
اكتب اسم الورقة و! قبل الجدول. عندما تبني الصيغة في الاكسل، انقر علامة تبويب الورقة الأخرى وحدد النطاق: سيكتب الاكسل Prices!A2:B6 عنك. هنا تبحث علامة التبويب Orders عن الأسعار في علامة التبويب Prices.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
افتح علامة التبويب Prices وغيّر سعر Apple: يتحدث مجموع الطلب. وتفصيلان:
- اسم الورقة الذي فيه مسافات يحتاج إلى علامتي اقتباس مفردتين:
=VLOOKUP(B2,'Price list'!$A$2:$B$6,2,FALSE). - الجدول الموجود في مصنف آخر يضيف اسم الملف بين قوسين معقوفين،
[Prices.xlsx]Prices!$A$2:$B$6. وعندما يكون ذلك الملف مغلقًا، يعرض الاكسل مساره الكامل في الصيغة ويستمر البحث في العمل من الملف المحفوظ.
VLOOKUP مع أحرف البدل (مطابقة جزئية)
مع FALSE، يمكن أن تحتوي قيمة البحث على أحرف بدل: * تمثل أي عدد من الأحرف و? حرفًا واحدًا بالضبط. تجد "*"&E2&"*" أول منتج يحتوي اسمه على النص الموجود في E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
تطابق "coffee" كلًا من Iced coffee وCoffee beans؛ وتعيد VLOOKUP الأول من الأعلى، $2.90. غيّر E2 إلى bean لتحصل على $8.50، أو إلى juice. وللبحث عن علامة نجمة أو علامة استفهام حقيقية، ضع قبلها علامة التلدة: "~*".
VLOOKUP إلى اليسار
لا تستطيع VLOOKUP إرجاع عمود يقع يسار العمود الذي تبحث فيه: يعدّ col_index_num إلى اليمين فقط، والأرقام السالبة خطأ. لإيجاد المنتج لسعر معين، ابحث في العمود C وأعد العمود A بالدالة XLOOKUP أو INDEX وMATCH:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
تعيد الصيغتان Bread على بيانات الورقة الأولى. وتجد الشرح الكامل في XLOOKUP.
تمرين: تكلفة الشحن حسب الوزن
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
دورك: تنطبق كل تكلفة من وزنها حتى الوزن التالي في القائمة. في E2، استخدم VLOOKUP لإرجاع تكلفة شحن الطرد الذي وزنه في D2.
VLOOKUP بشرطين
تأخذ VLOOKUP قيمة بحث واحدة. للمطابقة على عمودين، أنشئ عمودًا مساعدًا يجمعهما، وضعه أولًا في الجدول، وابحث عن النص المجمّع نفسه. العمود A أدناه هو =B2&"-"&C2 منسوخة إلى الأسفل، لذا يحتوي على Coffee-Small وCoffee-Large وهكذا.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
دورك: يجمع العمود A المنتج والحجم بشرطة. في G2، أعد سعر المنتج الموجود في E2 بالحجم الموجود في F2.
الفاصل مهم: "Tea"&"Large" تعطي TeaLarge، التي لا تطابق شيئًا في العمود A. في Excel 2021 وMicrosoft 365 يمكنك الاستغناء عن العمود المساعد بالصيغة =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6)؛ وتوضح صفحة البحث بعدة شروط هذه الطريقة وطريقة INDEX/MATCH.
الأسئلة الشائعة
كيف أستخدم VLOOKUP في الاكسل؟
اكتب =VLOOKUP( وأعطها أربعة وسائط: القيمة المراد إيجادها، والجدول (يجب أن يحتوي عموده الأول على تلك القيمة)، ورقم العمود المراد إرجاعه، وFALSE للمطابقة التامة. تجد =VLOOKUP("Pear",A2:D6,3,FALSE) القيمة Pear في العمود A وتعيد القيمة من العمود C في ذلك الصف.
ماذا تعني TRUE أو FALSE في نهاية VLOOKUP؟
تطلب FALSE (أو 0) مطابقة تامة وتعيد #N/A عندما تكون القيمة مفقودة. وتطلب TRUE (أو 1، أو حذف الوسيط) مطابقة تقريبية: أكبر قيمة أقل من قيمة البحث أو مساوية لها، وهذا لا يعمل إلا عندما يكون العمود الأول مرتبًا تصاعديًا.
لماذا تعيد VLOOKUP الخطأ #N/A؟
لأن القيمة لم يُعثر عليها في العمود الأول من الجدول. الأسباب المعتادة خطأ إملائي، أو مسافة زائدة ("Milk " ليست "Milk")، أو رقم مخزن كنص في أحد الطرفين فقط، أو قيمة موجودة في عمود آخر. ضع الصيغة داخل IFNA لعرض نصك الخاص: =IFNA(VLOOKUP(F2,A2:D6,3,FALSE),"Not found").
هل تستطيع VLOOKUP البحث إلى اليسار؟
لا. تعيد VLOOKUP فقط الأعمدة الواقعة على يمين العمود الأول من الجدول. استخدم =XLOOKUP(F2,C2:C6,A2:A6) في Excel 2021 أو Microsoft 365، أو =INDEX(A2:A6,MATCH(F2,C2:C6,0)) في أي إصدار.
كيف أستخدم VLOOKUP من ورقة أخرى؟
ضع اسم الورقة وعلامة تعجب قبل النطاق: =VLOOKUP(B2,Prices!$A$2:$B$6,2,FALSE). وإذا كان في اسم الورقة مسافة، فضعه بين علامتي اقتباس مفردتين: 'Price list'!$A$2:$B$6.
