تبحث =XLOOKUP(F2,A2:A6,C2:C6) عن القيمة الموجودة في F2 في A2:A6 وتعيد القيمة من الصف نفسه في C2:C6. تبحث عن مطابقة تامة افتراضيًا، ويمكن أن يكون عمود البحث في أي مكان، وتحتاج إلى Excel 2021 أو Microsoft 365 (في Excel 2019 وما قبله استخدم INDEX وMATCH). اكتب منتجًا آخر في F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Bread | $2.40 | |
| 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: يُحدَّد نطاق البحث ونطاق الإرجاع كلٌّ على حدة. غيّر C2:C6 إلى B2:B6 فتعيد G2 الفئة. لا يوجد رقم عمود تعدّه، لذا لا يؤدي إدراج عمود بين A وC إلى تعطيل الصيغة: ينقل الاكسل النطاقين كليهما.
صيغة دالة XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| الوسيط | ما يفعله | القيمة الافتراضية |
|---|---|---|
lookup_value | القيمة المراد إيجادها. | مطلوب |
lookup_array | العمود (أو الصف) المراد البحث فيه. | مطلوب |
return_array | العمود أو الصف أو الكتلة المراد الإرجاع منها. بارتفاع lookup_array نفسه. | مطلوب |
if_not_found | ما يُعرض عندما لا يتطابق شيء. | #N/A |
match_mode | 0 تامة، و-1 تامة أو الأصغر التالية، و1 تامة أو الأكبر التالية، و2 أحرف البدل. | 0 |
search_mode | 1 من الأول إلى الأخير، و-1 من الأخير إلى الأول، و2 و-2 بحث ثنائي في بيانات مرتبة. | 1 |
الثلاثة الأولى وحدها مطلوبة. لتخطي وسيط اختياري وضبط وسيط لاحق، اتركه فارغًا بين الفاصلتين: تضبط =XLOOKUP(F2,A2:A6,C2:C6,,0,-1) الوسيط search_mode وتترك if_not_found على قيمته الافتراضية.
إرجاع عدة أعمدة دفعة واحدة
أعطِ XLOOKUP نطاق إرجاع بعرض عدة أعمدة فيعود الصف كله. تمتد النتيجة في الخلايا المجاورة للصيغة.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Price | Stock |
| 2 | Apple | Fruit | $1.20 | 40 |
| 3 | Pear | Fruit | $1.50 | 25 |
| 4 | Carrot | Vegetable | $0.80 | 60 |
| 5 | Bread | Bakery | $2.40 | 15 |
| 6 | Milk | Dairy | $1.10 | 30 |
| 7 | Look for | Carrot | ||
| 8 | Result | Vegetable | $0.80 | 60 |
صيغة واحدة في B8 تملأ B8:D8 بالقيم Vegetable و$0.80 و60. اكتب شيئًا في C8 فتعرض B8 الخطأ #SPILL!، لأن النتيجة لا تجد مكانًا؛ احذفه فتعود النتيجة. ولإرجاع الأعمدة بترتيب آخر، ضع نطاق الإرجاع داخل CHOOSECOLS: تعطي =XLOOKUP(B7,A2:A6,CHOOSECOLS(B2:D6,3,1)) القيمة Stock ثم Category.
XLOOKUP إلى اليسار، ورسالة عندما لا يتطابق شيء
لا يجب أن يأتي عمود البحث أولًا. هنا تبحث XLOOKUP في الأسعار في العمود C وتعيد اسم المنتج من العمود A، وهذا ما لا تستطيعه VLOOKUP. ويحدد الوسيط الرابع ما يُعرض عندما لا يكون لأي منتج هذا السعر.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | Product | |
| 2 | Apple | Fruit | $1.20 | 40 | $2.40 | Bread | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
يعيد السعر $2.40 المنتج Bread. غيّر F2 إلى 3 فتعرض G2 "No product" بدلًا من #N/A. و"" كوسيط رابع تعرض خلية تبدو فارغة. يغطي if_not_found حالة "غير موجود" فقط: نطاق الإرجاع ذو الارتفاع الخاطئ يعطي #VALUE! مع ذلك، وهذا ما تريد رؤيته.
إيجاد آخر تطابق
تعيد XLOOKUP أول تطابق من الأعلى. اضبط search_mode، الوسيط السادس، على -1 فتبحث من الأسفل، فتعيد آخر تطابق: آخر طلب، أو أحدث سعر، أو آخر حالة.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | First | Last | |
| 2 | 2026-03-02 | Ben | 120 | Ben | 120 | 60 | |
| 3 | 2026-03-05 | Ana | 80 | ||||
| 4 | 2026-03-09 | Ben | 45 | ||||
| 5 | 2026-03-12 | Cara | 200 | ||||
| 6 | 2026-03-20 | Ben | 60 | ||||
| 7 | 2026-03-24 | Ana | 95 |
أول طلب لـ Ben قيمته 120 وآخره 60. غيّر E2 إلى Ana: 80 و95. يعتمد هذا على أن الصفوف مرتبة حسب التاريخ. وإن لم تكن كذلك، فابحث عن أحدث تاريخ للعميل بدلًا من ذلك: =XLOOKUP(1,(B2:B7=E2)*(A2:A7=MAXIFS(A2:A7,B2:B7,E2)),C2:C7).
المطابقة التقريبية: الأصغر التالية أو الأكبر التالية
يعيد match_mode بالقيمة -1 مطابقة تامة، أو القيمة الأصغر التالية إذا لم توجد. وهذه قاعدة الفئات: مستوى عمولة، أو شريحة ضريبية، أو تقدير. وبخلاف VLOOKUP مع TRUE، لا يلزم ترتيب الجدول. الفئات أدناه بلا ترتيب معين عن قصد.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 5000 | 5% | Ana | 750 | 0% | |
| 3 | 0 | 0% | Ben | 4,200 | 3% | |
| 4 | 10000 | 8% | Cara | 5,000 | 5% | |
| 5 | 1000 | 3% | Dev | 12,500 | 8% |
القيمة 4,200 الخاصة بـ Ben تقع بين 1,000 و5,000، لذا يحصل على نسبة فئة 1,000، أي 3%. وقيمة Cara البالغة 5,000 مطابقة تامة، 5%. ويعمل match_mode بالقيمة 1 بالاتجاه الآخر، تامة أو الأكبر التالية، وهو ما يجيب عن "أصغر صندوق يتسع" أو "موعد التوصيل التالي": تعيد =XLOOKUP(18,{5;12;25;50},{"S";"M";"L";"XL"},,1) القيمة L.
XLOOKUP مع أحرف البدل
يحوّل match_mode بالقيمة 2 العلامة * (أي أحرف) و? (حرف واحد) إلى أحرف بدل. بدونه تبحث XLOOKUP عن الأحرف نفسها، وهذا عكس VLOOKUP (التي تقبل مطابقتها التامة أحرف البدل)، وهو السبب المعتاد لإرجاع XLOOKUP بأحرف البدل الخطأ #N/A أو نص if_not_found:
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None") None: no product is named *coffee*
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None",2) 2.9, the price of Iced coffee
| 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 أولًا، $2.90. أضف -1 كوسيط سادس فتجد Coffee beans، $8.50. ومثل كل بحث في الاكسل، تتجاهل المطابقة حالة الأحرف. وللبحث عن علامة نجمة أو استفهام حقيقية في match_mode 2، ضع قبلها علامة التلدة: "~*".
XLOOKUP في اتجاهين
يمكن أن تكون XLOOKUP التي تعيد صفًا كاملًا نطاق الإرجاع لدالة XLOOKUP ثانية. الداخلية تختار الصف حسب المنطقة، والخارجية تختار عمود الشهر من ذلك الصف.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | South | ||
| 7 | Month | Feb | ||
| 8 | Sales | 3,600 |
تعيد XLOOKUP(B6,A2:A5,B2:D5) صف South، أي 3100 و3600 و3300. وتجد XLOOKUP الخارجية Feb في B1:D1 وتأخذ القيمة المقابلة من ذلك الصف: 3,600. اختر منطقة وشهرًا آخرين في B6 وB7. ونسخة INDEX وMATCH من البحث نفسه موجودة في صفحة INDEX وMATCH.
XLOOKUP في الإصدارات الأقدم وGoogle Sheets
توجد XLOOKUP في Excel 2021 وExcel 2024 وMicrosoft 365 وExcel للويب وتطبيقات الهاتف. إذا فتحت ملفًا يستخدمها في Excel 2019 أو ما قبله، تعرض الصيغ #NAME? بمجرد إعادة حسابها. وعندما يجب أن يعمل الملف في كل مكان، اكتب البحث بالدالتين INDEX وMATCH، اللتين يفهمهما كل إصدار:
=XLOOKUP(F2, A2:A6, C2:C6, "Not found")
=IFNA(INDEX(C2:C6, MATCH(F2, A2:A6, 0)), "Not found")
يحتوي Google Sheets على XLOOKUP منذ 2022، بالوسائط نفسها. ولمقارنة الفروق جنبًا إلى جنب، راجع VLOOKUP مقابل XLOOKUP. وللمطابقة على عمودين معًا (المنتج والحجم، الاسم والتاريخ)، يُشرح النمط =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6) في صفحة البحث بعدة شروط.
تمرين: سعر، أو "Not found"
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | ||
| 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، أعد سعر المنتج الموجود في F2، أو النص Not found عندما لا يكون في القائمة.
تمرين: الخصم حسب حجم الطلب
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order from | Discount | Order | Discount | |
| 2 | $0 | 0% | $320 | ||
| 3 | $100 | 5% | |||
| 4 | $250 | 10% | |||
| 5 | $500 | 15% |
دورك: ينطبق كل خصم بدءًا من مبلغ الطلب الخاص به فما فوق. في E2، استخدم XLOOKUP لإرجاع الخصم لمبلغ الطلب الموجود في D2.
الأسئلة الشائعة
كيف أستخدم XLOOKUP في الاكسل؟
أعطها ثلاثة وسائط: ما تبحث عنه، والعمود الذي تبحث فيه، والعمود الذي تُرجع منه. تجد =XLOOKUP("Pear",A2:A6,C2:C6) القيمة Pear في A2:A6 وتعيد القيمة من الصف نفسه في C2:C6. وتبحث عن مطابقة تامة ما لم تحدد غير ذلك.
أي إصدارات الاكسل فيها XLOOKUP؟
Excel 2021 وExcel 2024 وMicrosoft 365 وExcel للويب. في Excel 2019 وما قبله تعرض الصيغة #NAME?؛ استخدم هناك =INDEX(C2:C6,MATCH(F2,A2:A6,0)). ويحتوي Google Sheets أيضًا على XLOOKUP.
كيف أجعل XLOOKUP تعيد خلية فارغة أو نصًا بدلًا من #N/A؟
استخدم الوسيط الرابع، if_not_found: =XLOOKUP(F2,A2:A6,C2:C6,"Not found")، أو "" لخلية تبدو فارغة. يستبدل حالة عدم العثور فقط؛ وتبقى الأخطاء الأخرى ظاهرة.
كيف أجد آخر تطابق باستخدام XLOOKUP؟
اضبط الوسيط السادس، search_mode، على -1 ليجري البحث من الأسفل إلى الأعلى: تعيد =XLOOKUP("Ben",B2:B7,C2:C7,,0,-1) آخر مبلغ لـ Ben بدلًا من أوله.
هل يمكن أن تعيد XLOOKUP أكثر من عمود؟
نعم. أعطها نطاق إرجاع بعرض عدة أعمدة، مثل =XLOOKUP(F2,A2:A6,B2:D6)، فتمتد النتيجة في الخلايا المجاورة. يجب أن تكون الخلايا التي تمتد إليها فارغة، وإلا يعرض الاكسل #SPILL!.
