تجد =INDEX(C2:C6,MATCH(F2,A2:A6,0)) الصف الذي تظهر فيه F2 داخل A2:A6 وتعيد القيمة من الصف نفسه في C2:C6. تجد MATCH الموضع، وتجلب INDEX القيمة الموجودة في ذلك الموضع. تعمل في كل إصدارات الاكسل، وتستطيع البحث إلى اليسار.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | P-101 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
غيّر F2 إلى Milk فتعيد G2 القيمة $1.10. وغيّر C2:C6 إلى B2:B6 فتعيد الفئة بدلًا من ذلك.
كيف تعمل INDEX وMATCH معًا
الصيغة خطوتان في خلية واحدة. وهنا هما في خليتين منفصلتين، لترى ما يعيده كل جزء.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | P-101 | Position | 4 | |
| 3 | Pear | Fruit | $1.50 | P-102 | Price | $2.40 | |
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
تعيد MATCH(G1,A2:A6,0) القيمة 4، لأن Bread هو العنصر الرابع في A2:A6. وتعيد INDEX(C2:C6,4) العنصر الرابع في C2:C6، أي $2.40. ضع MATCH داخل INDEX مكان G2 فتحصل على صيغة الخلية الواحدة. قاعدتان تجعلانها تعمل:
- يجب أن يبدأ النطاقان في الصف نفسه وأن يكون لهما الارتفاع نفسه. تعدّ
MATCH(...,A2:A6,0)من الصف 2، لذا يجب أن تقرأ INDEX النطاقC2:C6، لاC1:C6(الذي سيعيد الصف الذي فوقه). - اختم MATCH بالرقم 0. بدونه تجري MATCH مطابقة تقريبية تفترض أن العمود A مرتب، وعلى قائمة أسماء قد تعيد موضع الصف الخطأ. تتناول صفحة MATCH أنواع المطابقة الثلاثة فيها.
إذا لم تكن القيمة في القائمة، تعيد MATCH الخطأ #N/A وكذلك الصيغة كلها. وتعرض =IFNA(INDEX(C2:C6,MATCH(F2,A2:A6,0)),"Not found") نصًا بدلًا من ذلك.
البحث إلى اليسار
تعيد VLOOKUP الأعمدة الواقعة على يمين العمود الذي تبحث فيه. أما INDEX وMATCH فلا يهمهما الترتيب: ابحث في العمود D، وأعد العمود A.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Code | Code | Product | |
| 2 | Apple | Fruit | $1.20 | P-101 | P-310 | Bread | |
| 3 | Pear | Fruit | $1.50 | P-102 | |||
| 4 | Carrot | Vegetable | $0.80 | P-205 | |||
| 5 | Bread | Bakery | $2.40 | P-310 | |||
| 6 | Milk | Dairy | $1.10 | P-412 |
يعيد الرمز P-310 المنتج Bread. اكتب P-205 في F2 لتحصل على Carrot. ومع VLOOKUP كان عليك أن تنقل عمود Code إلى بداية الجدول أولًا.
البحث في اتجاهين: INDEX مع دالتي MATCH
تأخذ INDEX رقم صف ورقم عمود. أعطها جدولًا كاملًا ودع دالة MATCH تجد الصف وأخرى تجد العمود.
| 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 | East | ||
| 7 | Month | Mar | ||
| 8 | Sales | 5,600 |
East هي الصف 3 في A2:A5 وMar هو العمود 3 في B1:D1، لذا تعيد INDEX الصف 3 والعمود 3 من B2:D5: 5,600. تبحث MATCH الخاصة بالصف نزولًا في العمود الأول، وتبحث MATCH الخاصة بالعمود أفقيًا في صف العناوين، ويتوافق النطاقان كلاهما مع الجدول B2:D5.
لماذا تتفوق INDEX MATCH على VLOOKUP
=VLOOKUP(F2, A2:D6, 3, FALSE)
=INDEX(C2:C6, MATCH(F2, A2:A6, 0))
تعيد الصيغتان السعر. ويظهر الفرق عندما تتغير الورقة:
- إدراج عمود. أدرج عمودًا بين Category وPrice، فتبقى VLOOKUP تطلب العمود 3، وهو الآن العمود الجديد الفارغ. أما في نسخة INDEX فيعدّل الاكسل
C2:C6إلىD2:D6فتستمر في العمل. - البحث إلى اليسار. كما ظهر أعلاه: لا تستطيعه VLOOKUP، وتستطيعه INDEX MATCH.
في Excel 2021 وMicrosoft 365، تفعل XLOOKUP الأمرين في دالة واحدة بوسائط أبسط. وتبقى INDEX MATCH الخيار للملفات التي يجب أن تُفتح في Excel 2019 أو ما قبله، وجزء INDEX مفيد وحده أيضًا. وللبحث بشرطين معًا، راجع البحث بعدة شروط.
تمرين: البحث إلى اليسار
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Price | Stock | Product | Look for | Price | |
| 2 | P-101 | $1.20 | 40 | Apple | Milk | ||
| 3 | P-102 | $1.50 | 25 | Pear | |||
| 4 | P-205 | $0.80 | 60 | Carrot | |||
| 5 | P-310 | $2.40 | 15 | Bread | |||
| 6 | P-412 | $1.10 | 30 | Milk |
دورك: أسماء المنتجات في العمود الأخير. في G2، أعد سعر المنتج الموجود في F2 باستخدام INDEX وMATCH.
تمرين: بحث في اتجاهين
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Math | Science | Art |
| 2 | Ana | 78 | 85 | 92 |
| 3 | Ben | 64 | 71 | 88 |
| 4 | Cara | 95 | 89 | 73 |
| 5 | Dev | 82 | 67 | 79 |
| 6 | ||||
| 7 | Student | Cara | ||
| 8 | Subject | Science | ||
| 9 | Score |
دورك: في B9، أعد درجة الطالب الموجود في B7 في المادة الموجودة في B8.
الأسئلة الشائعة
كيف تعمل INDEX MATCH؟
تجد MATCH موضع قيمة في عمود، وتعيد INDEX القيمة الموجودة في ذلك الموضع في عمود آخر. في =INDEX(C2:C6,MATCH("Pear",A2:A6,0))، تعيد MATCH القيمة 2 لأن Pear هو العنصر الثاني في A2:A6، وتعيد INDEX العنصر الثاني في C2:C6.
لماذا أستخدم INDEX MATCH بدلًا من VLOOKUP؟
لأنها تستطيع إرجاع عمود يقع يسار العمود الذي تبحث فيه، ولا تتعطل عند إدراج عمود داخل الجدول (لا يوجد رقم عمود يصبح قديمًا). في Excel 2021 وMicrosoft 365، تمنح XLOOKUP المزايا نفسها في دالة واحدة.
ماذا يعني الرقم 0 في MATCH؟
يطلب مطابقة تامة. بدونه تستخدم MATCH قيمة match_type 1، وهي مطابقة تقريبية تتوقع أن يكون العمود مرتبًا تصاعديًا، وعلى قائمة غير مرتبة قد تعيد موضع الصف الخطأ.
كيف أبحث في اتجاهين باستخدام INDEX MATCH؟
أعطِ INDEX جدولًا كاملًا ودالتي MATCH، واحدة للصف وأخرى للعمود: تعيد =INDEX(B2:D5,MATCH("South",A2:A5,0),MATCH("Feb",B1:D1,0)) القيمة التي يلتقي فيها صف South بعمود Feb.
