كيفية عكس vlookup في excel باستخدام index-match؟

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

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

لكن دعونا نتعلم بالتفصيل كيف يمكننا الجمع بين هاتين الوظيفتين. يوجد أدناه بناء جملة INDEX كما تعلم.

INDEX(صفيف،row_num،[column_num])

في الدالة INDEX، تخبرها الوسيطةrow_num بالصف الذي يجب أن تقوم بإرجاع القيمة منه. لنفترض أنه إذا قمت بإدخال 4، فسوف يُرجع قيمة الصف الرابع.

لإنشاء صيغة بحث عكسي، نحتاج إلى استبدال MATCH بـrow_argument الخاص بـ INDEX.

عندما نستخدم MATCH، فإنه يبحث عن القيمة في عمود البحث ويعيد رقم الخلية الخاص بتلك القيمة. وبعد ذلك، يستخدم INDEX هذا الرقم لتحديد موضع الخلية من عمود القيمة.

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

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

 =INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

دعونا نقسم هذه الصيغة إلى قسمين لفهمها.

الجزء 1 : في الجزء الأول، استخدمنا دالة المطابقة للبحث عن القيمة “مومباي” وأرجعت الرقم “5” وهو موضع الخلية التي لديك فيها القيمة “مومباي” في عمود المدن.

الجزء 2 : في الجزء الثاني، استخدمنا INDEX وأشرنا إلى عمود اسم الموظف للعثور على القيمة. هنا تعرف وظيفة الفهرس أنك تريد قيمة الخلية الخامسة من العمود. فأعاد “سيا” في النتيجة.

المزيد من الأمثلة على INDEX والمطابقة

لدينا بعض المشاكل الشائعة التي قمنا بحلها باستخدام صيغة INDEX MATCH. الوصول إلى نماذج الملفات: تأكد من تنزيل نماذج الملفات هذه من هنا لمتابعة كل مثال.

1. البحث الأساسي باستخدام INDEX – MATCH

يعد البحث العادي أحد أهم المهام التي يتعين عليك القيام بها باستخدام صيغ البحث ويعتبر INDEX MATCH مثاليًا لذلك. لدينا هنا جدول بيانات يحتوي على معرف الموظف واسمه. كل معرف فريد وتحتاج إلى البحث عن اسم الموظف باستخدام معرفه.

لنفترض أنك تريد البحث عن الاسم EMP-132. ولهذا ستكون الصيغة:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

هذه هي الطريقة التي تعمل بها هذه الصيغة

أولاً ، تقوم MATCH بمطابقة المعرف emp في عمود emp id وإرجاع رقم خلية المعرف الذي تبحث عنه. هنا رقم السطر هو 6.

بعد ذلك يقوم INDEX بإرجاع اسم الموظف من عمود الاسم باستخدام نفس رقم الخلية.

2. انظر إلى اليسار

لا يمكن لـ VLOOKUP الانتقال إلى اليسار عند البحث عن قيمة. كما ذكرت، في INDEX وMATCH يمكنك البحث في أي اتجاه. في جدول البيانات أدناه، يوجد عمود رقم الفاتورة بعد عمود المبلغ.

لذا، إذا كنت تريد البحث عن مبلغ فاتورة معينة، فهذا غير ممكن باستخدام VLOOKUP. في VLOOKUP، عندما تحدد جدولاً، سيكون العمود الأول في هذا الجدول هو عمود البحث.

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

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

…هذه هي الطريقة التي تعمل بها هذه الصيغة

• بادئ ذي بدء ، قمت بالإشارة إلى عمود المبلغ في وظيفة الفهرس. هذا هو العمود الذي نحتاج للحصول على القيمة منه.
• ثانيًا ، في الوسيطةrow_number الخاصة بوظيفة الفهرس، استخدمت دالة المطابقة وحددت رقم الفاتورة، وأشرت إلى عمود الفاتورة واستخدمت الصفر للمطابقة التامة.
• ثالثًا ، تقوم وظيفة المطابقة بإرجاع رقم الخلية الخاص بالفاتورة من النطاق.

وفي النهاية، يستخدم INDEX هذا الرقم لإرجاع المبلغ عن طريق وضع الخلية من عمود المبلغ.

3. بحث غامض

تمامًا مثل VLOOKUP، يمكنك أيضًا استخدام INDEX/MATCH لإجراء بحث تقريبي.

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

وإذا كنت ترغب في الحصول على درجة 79، يمكنك استخدام الصيغة أدناه.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

…هذه هي الطريقة التي تعمل بها هذه الصيغة

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

بالنسبة إلى 79، أول قيمة أقل هي 75، وبالنسبة إلى 75، فإن الدرجة هي B. ولهذا السبب تحصل على B في النتيجة.

4. البحث الأفقي

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

وستكون الصيغة:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

…هذه هي الطريقة التي تعمل بها هذه الصيغة

في الصيغة أعلاه، بدلاً من استخدام MATCH في الوسيطةrow_num للفهرس، استخدمناها في column_num. وتقوم المطابقة بإرجاع رقم العمود لشهر مايو.

ثم تقوم INDEX بإرجاع قيمة عمود النتيجة بناءً على رقم الموضع.

5. البحث في اتجاهين

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

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

في تركيبة عادية من INDEX وMATCH، يمكنك استخدام MATCH لرقم الصف، ولكن في البحث ثنائي الاتجاه، يجب أيضًا استخدامه لرقم العمود. الصيغ ستكون:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

…هذه هي الطريقة التي تعمل بها هذه الصيغة

في الصيغة أعلاه، للحصول على مبلغ المبيعات من الجدول، استخدمت دالة الفهرس ثم دالة المطابقة للوسيطتينrow_num وcolumn_num. تقوم دالة المطابقة، وهي الوسيطة column_num، بإرجاع 5 كقيمة المنتج-D في الصف الخامس من النطاق الذي أشرت إليه.

وتقوم دالة المطابقة الموجودة في الوسيطةrow_num بإرجاع 2 لأن قيمة المنطقة الشمالية موجودة في العمود الثاني من النطاق الذي أشرت إليه.

الآن، باستخدام هذه القيم، قامت دالة الفهرس بإرجاع القيمة الموجودة في العمود الثاني والصف الخامس: 1456.

6. حساس لحالة الأحرف

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

وفي البداية، هناك أسماء متشابهة ولكن في حالات نصية مختلفة. على سبيل المثال، جون باركر وجون ماثيو. لنفترض أنك تريد البحث عن العلامات التجارية لـ “JOHN” وليس “John”، فيمكنك إنشاء بحث مطابق تمامًا باستخدام INDEX وMATCH. وستكون الصيغة:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

…هذه هي الطريقة التي تعمل بها هذه الصيغة

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

يمكنها مقارنة قيمتين وإرجاع TRUE إذا كانتا متطابقتين تمامًا (بما في ذلك حالة الأحرف)، ولكن يجب عليك إدخال هذه الصيغة في شكل جدول لأنك تحتاج إلى مقارنة العمود بأكمله بقيمة واحدة في EXACT. عند إدخاله، سيُرجع مصفوفة مثل هذا.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

من هناك تحتاج إلى استخدام وظيفة المطابقة للحصول على موضع TRUE من المصفوفة.

عند هذه النقطة لديك رقم الخلية (رقم الصف) للقيمة التي تحتاج إلى البحث عنها. ويمكنك استخدام INDEX لقيمة العمود هذه.

نقطة مهمة: إذا حصلت على أكثر من TRUE واحد مع EXACT، فإن المطابقة ستعيد ببساطة الرقم الخاص بـ TRUE الأول

7. مع أحرف البدل مع INDEX MATCH

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

ما عليك سوى إلقاء نظرة على قائمة الأسماء أدناه حيث يوجد الاسم الأول والأخير للموظفين وأعمارهم.

من هذه القائمة تحتاج إلى الحصول على عمر موظف معين (سوندرا). لكن الحقيقة هي أنك تعرف الاسم الأول فقط.

وإذا استخدمت علامة النجمة، فيمكنك البحث عن عمر Sondra باستخدام الاسم الأول. ولهذا ستكون الصيغة:

 =INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

…هذه هي الطريقة التي تعمل بها هذه الصيغة

العلامة النجمية هي حرف بدل يمكنه استبدال عدد n من الأحرف. لذلك عندما استخدمته بعد الاسم الأول، فقد حل محل الاسم الأخير.

8. أدنى قيمة

لنفترض أن لديك قائمة بالطلاب مع نتائجهم كما هو موضح أدناه. والآن من هذه القائمة تريد البحث عن اسم الطالب الذي حصل على أقل الدرجات.

لهذا، يمكنك استخدام الدالة MIN مع الفهرس والمطابقة وستكون الصيغة:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

لديك أسماء الطلاب في العمود (أ) ونتائجهم في العمود (ب).

لذا، عند إدراج هذه الصيغة في خلية والضغط على Enter، فإنها ستعيد اسم الطالب الحاصل على أقل درجة، أي Librada Bastian.

توضيح

في هذه الصيغة لدينا ثلاثة أجزاء مختلفة.

في الجزء الأول ، تقوم الدالة MIN بإرجاع أدنى درجة.

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

في النهاية، تقوم دالة الفهرس بإرجاع قيمة عمود اسم الطالب باستخدام نفس موضع الخلية الذي يتم إرجاعه عن طريق المطابقة.

نصيحة: بنفس الطريقة، يمكنك أيضًا الحصول على اسم الطالب الذي حصل على أعلى الدرجات.

9. أفضل نتيجة n

الآن فكر بهذه الطريقة، لديك قائمة بالطلاب مع درجات امتحاناتهم ومن هذه القائمة تريد الحصول على اسم الطالب الذي حصل على ثاني أعلى الدرجات.

المشكلة هي أنك لا تعرف ما هي ثاني أعلى نتيجة.

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

ولهذا يمكنك دمج دالة كبيرة مع فهرس ومطابقتها. ستساعدك الدالة الكبيرة على تحديد ثاني أعلى قيمة في النطاق.

وستكون الصيغة:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

…هذه هي الطريقة التي تعمل بها هذه الصيغة

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

بعد أن تقوم الدالة الكبيرة بإرجاع ثاني أكبر قيمة، تستخدم دالة المطابقة تلك القيمة وترجع رقم الخلية المقابل.

وفي النهاية، تستخدم وظيفة الفهرس رقم الخلية هذا وترجع اسم الطالب.

10. معايير متعددة

عادةً ما يكون الهدف من الجمع بين الفهرس والمطابقة هو البحث عن قيمة واحدة. ولهذا السبب تستخدم نطاقًا واحدًا فقط في وظيفة المطابقة.

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

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

ومن هذه البيانات تريد الحصول على سعر منتج معين باستخدام جميع المعايير.

وبالتالي فإن الصيغة ستكون:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

ملاحظة: هذه صيغة مصفوفة، لذا عليك إدخالها باستخدام ctrl + Shift + enter.

…هذه هي الطريقة التي تعمل بها هذه الصيغة

في هذه الصيغة لديك ثلاث صفائف مختلفة لمطابقة ثلاث قيم مختلفة وتقوم هذه الصفائف بإرجاع TRUE وFALSE حيث تتطابق القيم.

بعد ذلك عندما تقوم بضربهم مع بعضهم البعض تحصل على مصفوفة أو شيء من هذا القبيل.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

وتقوم دالة المطابقة بإرجاع الموضع 1 للمصفوفة.

في النهاية، يقوم INDEX بإرجاع سعر عمود السعر باستخدام الرقم الذي تم إرجاعه بواسطة المطابقة.

تلميح: إذا كنت لا تريد استخدام صيغة صفيف، فيمكنك استخدام شرط SUMPRODUCT .

11. القيمة العددية الأولى للنطاق

لنفترض أن لديك قائمة تحتوي على قيم نصية ورقمية، والآن تريد الحصول على القيمة الرقمية الأولى من هذه القائمة.

للقيام بذلك، يمكنك دمج الدالة ISNUMBER مع الفهرس/المطابقة. يمكن أن يساعدك ISNUMBER في تحديد القيمة التي تمثل رقمًا والقيمة التي تمثل نصًا.

الصيغة ستكون:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

يجب عليك إدخال هذه الصيغة في شكل جدول (باستخدام Ctrl + Shift + Enter).

…هذه هي الطريقة التي تعمل بها هذه الصيغة

في هذه الصيغة تقوم ISNUMBER بإرجاع مصفوفة مساوية لطول القائمة وفي هذه المصفوفة لديك TRUE للقيم التي هي أرقام وFALSE لبقية القيم.

بعد ذلك استخدمت TRUE في وظيفة المطابقة كقيمة بحث. لذلك تقوم بإرجاع رقم موضع أول TRUE في المصفوفة.

في النهاية، يؤدي استخدام مؤشر رقم الموضع هذا إلى إرجاع القيمة الرقمية الأولى.

12. احصل على القيمة الأولى غير الفارغة

لنفكر بهذه الطريقة، لديك قائمة بالقيم حيث تكون بعض الخلايا الأولى فارغة وتريد الحصول على القيمة الأولى غير الفارغة.

ويمكنك استخدام هذه الصيغة للحصول على هذه القيمة الأولى غير الفارغة.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

…هذه هي الطريقة التي تعمل بها هذه الصيغة

نحن بحاجة إلى تقسيم هذه الصيغة إلى ثلاثة أجزاء مختلفة لفهمها بحكمة.

أولاً ، استخدمت الدالة ISBLANK في دالة المطابقة للحصول على مصفوفة حيث يكون لديك TRUE للخلايا الفارغة وFALSE للخلايا غير الفارغة.

ثانيًا ، تقوم الدالة MATCH بإرجاع رقم موضع القيمة TRUE الأولى في المصفوفة التي يتم إرجاعها بواسطة ISBLANK.

إذن، في هذه المرحلة لديك رقم الخلية للقيمة غير الفارغة الأولى.

ثالثًا ، تقوم دالة الفهرس ببساطة بإرجاع القيمة الأولى غير الفارغة في القائمة.

13. النص الأكثر تكرارا

لنفترض الآن أنه في ضوء قائمة القيم النصية، فإنك تحتاج إلى حساب النص الأكثر تكرارًا.

في القائمة أدناه لديك أسماء.

ولكن، هناك بعض الأسماء التي يتم تكرارها أكثر من مرة.

لذا، أنت الآن بحاجة إلى الحصول على الاسم الذي له أعلى تكرار في القائمة. يمكنك استخدام الصيغة أدناه والتي هي عبارة عن مزيج من MODE وINDEX وMATCH.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

عندما تقوم بإدراج هذه الصيغة فإنها ستعيد “Tamesha” وهو الاسم الأكثر شيوعًا.

…هذه هي الطريقة التي تعمل بها هذه الصيغة

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

لنأخذ مثالاً على اسم “Tamesha” وهو الاسم الأكثر شيوعًا لدينا في القائمة. الآن، إذا نظرت إلى القائمة، ستجد أن هذا حدث أولاً في الخلية الثامنة ثم في الخلية الثانية عشرة.

لكن، إذا نظرت إلى الجدول، فبالنسبة لجميع المواضع التي لدينا فيها “تامشا”، فقد أعادت 8 وهو موضعها الأول.

بعد ذلك، من المصفوفة التي تم إرجاعها بواسطة الدالة MATCH، ستقوم دالة الوضع بإرجاع الرقم الأكثر تكرارًا وهو رقم الخلية لأول ظهور لـ ” Tamesha “.

وفي النهاية، سيُرجع INDEX النص باستخدام رقم الخلية هذا.

14. قم بإنشاء ارتباط تشعبي

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

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

ولهذا يجب علينا استخدام HYPERLINK + Cell مع INDEX وMATCH وستكون الصيغة:

هذه هي الطريقة التي تعمل بها هذه الصيغة

دعونا نقسم هذه الصيغة إلى عدة أجزاء لفهمها بشكل أفضل.

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