=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, שהיא עכשיו העמודה הריקה החדשה. אקסל מתאים את
C2:C6בגרסת ה-INDEX ל-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.
