انتقل إلى المحتوى الرئيسي

Vlookup وإرجاع قيم متعددة بناءً على معيار واحد أو عدة معايير

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

Vlookup وإرجاع جميع القيم المقابلة عموديًا

Vlookup وإرجاع جميع القيم المقابلة أفقيا

Vlookup وإرجاع جميع القيم المقابلة في خلية واحدة


Vlookup وإرجاع جميع القيم المقابلة عموديًا

لإرجاع جميع القيم المطابقة عموديًا استنادًا إلى معيار معين ، يرجى تطبيق صيغة الصفيف التالية:

1. أدخل هذه الصيغة أو انسخها في خلية فارغة حيث تريد إخراج النتيجة:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

ملاحظات: في الصيغة أعلاه ، C2: C20 هو العمود الذي يحتوي على السجل المطابق الذي تريد إرجاعه ؛ A2: A20 هل يحتوي العمود على المعيار ؛ و E2 هو المعيار المحدد الذي تريد إرجاع القيم بناءً عليه. يرجى تغييرها حسب حاجتك.

2. ثم اضغط كترل + شيفت + إنتر مفاتيح معًا للحصول على القيمة الأولى ، ثم اسحب مقبض التعبئة لأسفل للحصول على جميع السجلات المقابلة كما تريد ، انظر لقطة الشاشة:

نصيحة:

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

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )


Vlookup وإرجاع جميع القيم المقابلة أفقيا

إذا كنت ترغب في عرض القيم المطابقة بترتيب أفقي ، يمكن أن تساعدك صيغة الصفيف أدناه.

1. أدخل هذه الصيغة أو انسخها في خلية فارغة حيث تريد إخراج النتيجة:

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

ملاحظات: في الصيغة أعلاه ، C2: C20 هو العمود الذي يحتوي على السجل المطابق الذي تريد إرجاعه ؛ A2: A20 هل يحتوي العمود على المعيار ؛ و F1 هو المعيار المحدد الذي تريد إرجاع القيم بناءً عليه. يرجى تغييرها حسب حاجتك.

2. ثم اضغط كترل + شيفت + إنتر مفاتيح معًا للحصول على القيمة الأولى ، ثم اسحب مقبض التعبئة إلى اليمين للحصول على جميع السجلات المقابلة كما تريد ، انظر لقطة الشاشة:

نصيحة:

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

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")


Vlookup وإرجاع جميع القيم المقابلة في خلية واحدة

لبحث وإرجاع جميع القيم المطابقة في خلية واحدة ، يجب عليك تطبيق صيغة الصفيف التالية.

1. أدخل الصيغة أدناه أو انسخها في خلية فارغة:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

ملاحظات: في الصيغة أعلاه ، C2: C20 هو العمود الذي يحتوي على السجل المطابق الذي تريد إرجاعه ؛ A2: A20 هل يحتوي العمود على المعيار ؛ و F1 هو المعيار المحدد الذي تريد إرجاع القيم بناءً عليه. يرجى تغييرها حسب حاجتك.

2. ثم اضغط كترل + شيفت + إنتر مفاتيح معًا للحصول على جميع القيم المطابقة في خلية واحدة ، انظر لقطة الشاشة:

نصيحة:

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

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

ملحوظة: تم تطبيق هذه الصيغة بنجاح فقط في Excel 2016 والإصدارات الأحدث. إذا لم يكن لديك Excel 2016 ، فيرجى عرض هنا للحصول عليه.

المزيد من مقالات Vlookup النسبية:

  • Vlookup وإرجاع بيانات مطابقة بين قيمتين في Excel
  • في Excel ، يمكننا تطبيق وظيفة Vlookup العادية للحصول على القيمة المقابلة بناءً على بيانات معينة. لكن في بعض الأحيان نرغب في البحث وإرجاع قيمة المطابقة بين قيمتين ، كيف يمكنك التعامل مع هذه المهمة في Excel؟
  • البحث وإرجاع قيم متعددة من القائمة المنسدلة
  • في Excel ، كيف يمكنك البحث عن عدة قيم مقابلة وإرجاعها من قائمة منسدلة ، مما يعني أنه عند اختيار عنصر واحد من القائمة المنسدلة ، يتم عرض جميع قيمه النسبية مرة واحدة كما تظهر لقطة الشاشة التالية. في هذه المقالة ، سأقدم الحل خطوة بخطوة.
  • Vlookup لإرجاع فارغ بدلاً من 0 أو N / A في Excel
  • عادةً ، عند تطبيق وظيفة vlookup لإرجاع القيمة المقابلة ، إذا كانت الخلية المطابقة فارغة ، فستُرجع 0 ، وإذا لم يتم العثور على القيمة المطابقة ، فستتلقى خطأ # N / A value. بدلاً من عرض القيمة 0 أو # N / A ، كيف يمكنك إظهارها في خلية فارغة؟
  • Vlookup لإرجاع أعمدة متعددة من جدول Excel
  • في ورقة عمل Excel ، يمكنك تطبيق وظيفة Vlookup لإرجاع القيمة المطابقة من عمود واحد. ولكن ، في بعض الأحيان ، قد تحتاج إلى استخراج القيم المتطابقة من عدة أعمدة كما هو موضح في لقطة الشاشة التالية. كيف يمكنك الحصول على القيم المقابلة في نفس الوقت من عدة أعمدة باستخدام وظيفة Vlookup؟
  • قيم Vlookup عبر أوراق عمل متعددة
  • في Excel ، يمكننا بسهولة تطبيق وظيفة vlookup لإرجاع القيم المطابقة في جدول واحد من ورقة العمل. ولكن ، هل فكرت يومًا في كيفية البحث عن القيمة عبر ورقة عمل متعددة؟ لنفترض أن لدي أوراق العمل الثلاثة التالية مع مجموعة من البيانات ، والآن ، أريد الحصول على جزء من القيم المقابلة بناءً على المعايير من أوراق العمل الثلاثة هذه.

  • سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى
  • دمج الخلايا / الصفوف / الأعمدة وحفظ البيانات. تقسيم محتوى الخلايا ؛ اجمع الصفوف المكررة والمجموع / المتوسط... منع تكرار الخلايا؛ قارن النطاقات
  • حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
  • نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
  • المفضلة وإدراج الصيغ بسرعةوالنطاقات والمخططات والصور ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
  • اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF
  • تجميع الجدول المحوري حسب رقم الأسبوع واليوم من الأسبوع والمزيد ... إظهار الخلايا غير المؤمنة والمغلقة بألوان مختلفة قم بتمييز الخلايا التي لها صيغة / اسم
علامة تبويب kte 201905
  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
أوفيسيتاب القاع
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations