ابحث عن أسعار المنتجات في أوراق Google مع وظائف Vlookup وتطابق

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

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

في مثال قهوة Starbuck ، يتم تخزين أسعار القهوة في النطاق B2

. يتم تخزين اسم مشروب العميل (Caffè Mocha في هذا المثال) في الخلية G3. ما يلي MATCH ستعود الوظيفة إلى الموضع النسبي للمشروبات المختارة من قائمة المشروبات.

=MATCH(G3, $B$2:$B$11, 0)

يتم تعيين المعلمة الثالثة لوظيفة المطابقة على 0 لأننا نريد المطابقة الدقيقة ولم يتم فرز قائمة الأسعار الخاصة بنا.

وبالمثل ، التالي MATCH ستعود الوظيفة إلى الموضع النسبي للعمود الذي يحتوي على سعر المشروب بناءً على الكمية المحددة. يتم تخزين أحجام الكأس في النطاق C2

. يتم تخزين حجم الكوب المحدد في الخلية H3.

=MATCH(H3, $B$2:$E$2, 0)

الآن وبعد أن عرفنا الصف النسبي وموضع العمود لقيمة السعر التي نبحث عنها ، يمكننا استخدام INDEX وظيفة للعثور على السعر الفعلي من الجدول.

=INDEX($B$2:$E$11, H5, H7)

استخدم Vlookup مع ArrayFormula و Match

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

ومع ذلك ، سيتعين علينا إعادة النظر في نهجنا منذ INDEX لا يمكن استخدام الوظيفة المستخدمة في المثال السابق مع صيغ الصفيف لأنها لا يمكن إرجاع قيم متعددة. سنحل محل INDEX مع مماثلة VLOOKUP الوظيفة وجمعها و MATCH وظيفة لإجراء البحث في اتجاهين (ابحث عن المشروبات بالاسم ثم ابحث عن حجم الكأس المحدد).

بناء جملة وظيفة Vlookup ، باللغة الإنجليزية البسيطة ، هو:

=VLOOKUP(
  What you want to look for (beverage name),
  Where you want to look for it (price table range),
  The column number containing the matching value (chosen cup size),
  Return an approximate or exact match (True or False)
)

ستبحث الوظيفة عن اسم المشروبات في النطاق السعري المحدد (B2

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

لا يتم فرز نطاق السعر ، لذلك سنضع خطأ للمعلمة الرابعة.

ال MATCH ستعود الوظيفة إلى الموضع النسبي للعمود الذي يحتوي على سعر الكمية المحددة للمشروبات المطابقة:

=MATCH(
  What are you looking for (cup size),
  Where are you looking for it (cup size header range),
  0 if you want to find the exact value (default is 1)
)

إذا لم يكن هناك صف يحتوي على اسم المشروبات ، فسترجع الصيغة #N/A وبالتالي نلتف القيمة في IFNA لمنع الصيغة من إعادة أي أخطاء.

وبالتالي فإن صيغتنا الأخيرة ستبدو مثل:

=ARRAYFORMULA(IFNA(VLOOKUP(B14:B, $B$2:$E$11, MATCH(C14:C, $B$2:$E$2, 0), FALSE)))

قم بتنزيل ملف Excel – ورقة بحث السعر