2.2البحث: VLOOKUP، XLOOKUP، INDEX/MATCH
الأساسي في 30 ثانية
- البحث = مفتاح → قيمة: نعرف رمزاً فنستعيد السعر أو المعدّل المقابل — لا نسخ يدوي أبداً.
- XLOOKUP أولاً: تبحث في كل مكان وتُعيد من كل مكان وتدير الغياب — VLOOKUP لا ترى إلا يميناً.
- إدارة عدم الوجود: الوسيط الرابع في XLOOKUP للرسالة — #N/A خام ليس نتيجة أبداً.
حالة مصغّرة متسلسلة — وسيم ومعدّل الضريبة المفقود
وسيم (2.1) يعتمد فواتيره، لكن كل سطر يحمل رمز ضريبة يجب أن يسترجع معدّله من جدول مرجعي — وكان ينسخه يدوياً بأخطاء. يكتب XLOOKUP الأولى: الرمز في A2، يُبحَث عنه في عمود الرمز بالجدول، والمعدّل يُعاد من عمود النسبة، و«للتحقّق» إن غاب الرمز. وعندما يُدرِج عموداً في الجدول، تصمد صيغته — حيث تنكسر VLOOKUP القديمة لزميله بالرقم. في 2.3 سينظّف وسيم الرموز قبل البحث عنها؛ وفي 2.5 سيصبح جدوله جدولاً منظَّماً بأعمدة مسمّاة.
تكملة الخيط الناظم 2.1: شبكة وسيم تسترجع معدّلاتها تلقائياً — وتستمرّ في 2.3 و2.4 و2.5.
1. الحاجة: تقاطع جدولين
غالباً نملك مفتاحاً (رمز ضريبة، رمز منتج) ونريد استرجاع المعلومة المرتبطة به في جدول مرجعي (المعدّل، السعر). هذا هو البحث: «لهذا المفتاح، أعطني القيمة المقابلة». يقطع وسيم أسطر فواتيره مع جدول معدّلاته: صيغة واحدة منسوخة تُغني عن عشرات النسخ اليدوية، دون خطأ كتابة.
2. XLOOKUP، الحديثة
=XLOOKUP(المفتاح ؛ نطاق_البحث ؛ نطاق_النتيجة) يبحث عن المفتاح في عمود ويُعيد القيمة المقابلة من آخر — مهما كان ترتيب الأعمدة. مثال =XLOOKUP(A2 ؛ الضرائب[الرمز] ؛ الضرائب[النسبة] ؛ "للتحقّق"). والوسيط الرابع يعرض رسالة نظيفة عند غياب المفتاح، بدل #N/A الخام. يستعمله وسيم لرصد رموز الضريبة المجهولة الواجب التحقّق منها لدى المورّد.
3. VLOOKUP (القديمة) وINDEX/MATCH
VLOOKUP تبحث فقط في العمود الأول وتُعيد عموداً على اليمين برقمه: =VLOOKUP(A2 ؛ $E$2:$G$50 ؛ 3 ؛ FALSE) — هشّة، لأن إدراج عمود يُزيح الرقم. وINDEX/MATCH هي التركيبة المرنة القديمة: MATCH تجد موضع المفتاح، وINDEX تُعيد القيمة عند ذلك الموضع: =INDEX($G$2:$G$50 ؛ MATCH(A2 ؛ $E$2:$E$50 ؛ 0)). اليوم تحلّ XLOOKUP محلّهما بمزية عند توفّرها (Microsoft 365 / 2021+).
هل تعلم؟
تسمية الأعمدة هي ضمان البحث.
🔎 مثال ملموس — 🔎 مثال ملموس — معدّل ضريبة وسيم
جدول الضرائب (الرمز، البيان، النسبة)؛ الرمز مُدخَل في A2:
=XLOOKUP(A2; الضرائب[الرمز]; الضرائب[النسبة]; "للتحقّق")
مقارنةً (العالم القديم):
=VLOOKUP(A2; $E$2:$G$50; 3; FALSE)
=INDEX($G$2:$G$50; MATCH(A2; $E$2:$E$50; 0))القراءة: نسمّي الأعمدة (جدول منظَّم، انظر 2.5) ← صيغة مقروءة ومحصّنة ضدّ الإدراج.
الدرس: الوسيط الرابع يحوّل خطأ #N/A إلى توجيه عملي: «للتحقّق».
مفردات القسم
- مفتاح البحث
- القيمة المعروفة (رمز ضريبة، رمز منتج) التي تُستخدَم لاسترجاع المعلومة المرتبطة.
مثال بسيط: يبحث وسيم عن رمز الضريبة المُدخَل في A2.
ما ليس كذلك: القيمة المجهولة التي نريد استرجاعها.
رد فعل: مفتاح نظيف ومتجانِس أولاً.
- XLOOKUP
- تبحث مفتاحاً في نطاق وتُعيد القيمة المقابلة من نطاق آخر، مع رسالة إن لم تُوجَد.
مثال بسيط: يُعيد وسيم المعدّل من الضرائب[النسبة].
ما ليس كذلك: بحث محدود نحو اليمين برقم.
رد فعل: الخيار الأول إن توفّرت.
- VLOOKUP
- تبحث في العمود الأول من جدول وتُعيد العمود رقم n إلى اليمين؛ FALSE = بحث تامّ.
مثال بسيط: زميل وسيم يستخدم =VLOOKUP(A2;$E$2:$G$50;3;FALSE).
ما ليس كذلك: بحث في أيّ اتجاه.
رد فعل: FALSE دائماً في نهاية الصيغة.
- INDEX / MATCH
- MATCH تجد موضع مفتاح، وINDEX تُعيد القيمة عند ذلك الموضع: التركيبة المرنة التاريخية.
مثال بسيط: يُنجد وسيم جهازاً قديماً بـ =INDEX($G$2:$G$50;MATCH(A2;$E$2:$E$50;0)).
ما ليس كذلك: دالة تفرز أو تصفّي.
رد فعل: الموضع أولاً، ثم القيمة.
في التطبيق — تطبيق عملي — تقاطع جدولين
- أنشئ جدول ضرائب (الرمز، البيان، النسبة) وحوّله إلى جدول منظَّم.
- في منطقة أخرى، أدخِل رمزاً واسترجع معدّله بـ XLOOKUP.
- أضِف الوسيط الرابع لعرض «للتحقّق» إن لم يوجد الرمز.
- أدرِج عموداً في الجدول وتحقق أن XLOOKUP تصمد دائماً (خلافاً لـ VLOOKUP بالرقم).
نصّ جاهز للقول — برّر XLOOKUP (30 ث)
«سابقاً كنت أنسخ كل معدّل يدوياً فأخطئ. الآن الصيغة تبحث عن الرمز في الجدول وتُعيد المعدّل المقابل، حتى لو أُدرِج عمود. وإن كان الرمز مجهولاً تعرض «للتحقّق» بدل خطأ خام. صفر نسخ، صفر خطأ كتابة.»
قُلها وأنت تعرض عمود معدّلات وسيم يمتلئ وحده.
نموذج جاهز للنسخ — بحث متين
=XLOOKUP(A2; الضرائب[الرمز]; الضرائب[النسبة]; "للتحقّق")
=INDEX($G$2:$G$50; MATCH(A2; $E$2:$E$50; 0))رمز غائب
رمز ضريبة جديد يُعيد خطأ: كان وسيم قد استبق الوسيط الرابع، فيقرأ «للتحقّق» ويهاتف المورّد.
عمود مُدرَج
يُدرِج الزميل عموداً فتُعيد VLOOKUP البيان بدل المعدّل: أما وسيم بـ XLOOKUP المسمّاة فلا يتحرّك.
Excel قديم
جهاز بلا XLOOKUP: يُنجد وسيم بـ INDEX/MATCH، انتظاراً للتحديث إلى Microsoft 365.
مفتاح غير نظيف
رمزان «متطابقان» لا يُوجدان: مسافات غير مرئية — ينظّف وسيم بـ TRIM (انظر 2.3).
✓ ممارسات جيدة
- فضّل XLOOKUP (مرنة، متينة، تدير الغياب).
- FALSE دائماً (تامّ) في VLOOKUP، لا بحث تقريبي افتراضياً.
- ابحث على جداول منظَّمة بأعمدة مسمّاة.
✗ أخطاء شائعة
- VLOOKUP برقم عمود ← تنكسر إذا أُدرِج عمود.
- نسيان إدارة #N/A (رسالة بالوسيط الرابع).
- البحث بمفتاح من نوع مختلف (نصّ « 12 » مقابل رقم 12).
النقاط الأساسية
- البحث = استرجاع القيمة المرتبطة بمفتاح.
- XLOOKUP: تبحث في كل مكان، تُعيد من كل مكان، وتدير الغياب.
- VLOOKUP (العمود الأول، رقم إلى اليمين، FALSE) أكثر هشاشة.
- INDEX/MATCH = بديل مرن على النسخ بلا XLOOKUP.
تحدّي 48 ساعة — طبّقه غداً
على جدولك المرجعي (معدّل، سعر، جدول شرائح): XLOOKUP مسمّاة منسوخة على 10 أسطر، والوسيط الرابع معبّأ، ثم أدرِج عموداً ولاحظ أن كل شيء يصمد.
- أستطيع استرجاع قيمة بمفتاحها بـ XLOOKUP على أعمدة مسمّاة.
- أعرف إدارة المفاتيح الغائبة بنظافة (الوسيط الرابع).
- أعرف VLOOKUP (FALSE) وINDEX/MATCH للحالات المقيّدة.
أسئلة شائعة
لا أملك XLOOKUP، ماذا أفعل؟
تتطلّب XLOOKUP نسخة Microsoft 365 / 2021+. على نسخة أقدم، استخدم INDEX/MATCH (مرنة، تبحث في كل مكان) أو عند اللزوم VLOOKUP مع FALSE — لكن احذر رقم العمود الذي ينكسر عند الإدراج.
لماذا أحصل على #N/A رغم وجود المفتاح ظاهراً؟
غالباً مسافات طفيلية أو أنواع مختلفة (النصّ « 12 » ليس الرقم 12). نظّف بـ TRIM (انظر 2.3)، ووحّد النوع، أو اعرض رسالة عبر الوسيط الرابع في XLOOKUP.
أي دالة بحث هي الأمتن ضدّ إدراج الأعمدة؟