=MATCH(E2,A2:A6,0) מחזירה את המיקום של הערך שב-E2 בתוך A2:A6: 1 לתא הראשון, 2 לשני, וכן הלאה. ה-0 מבקש התאמה מדויקת. MATCH מחזירה מספר, לא את הערך עצמו, ולכן היא בדרך כלל משולבת עם INDEX.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Position | |
| 2 | Apple | Fruit | $1.20 | Bread | 4 | |
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Bread הוא התא הרביעי של A2:A6, ולכן F2 מציג 4. הספירה מתחילה בתא הראשון של הטווח, לא בשורה 1 של הגיליון: שנו את הטווח ל-A1:A6 והתשובה הופכת ל-5. הקלידו Kiwi ב-E2 ו-MATCH תחזיר #N/A.
התחביר של MATCH וסוגי ההתאמה
=MATCH(lookup_value, lookup_array, [match_type])
| match_type | מוצאת | הרשימה חייבת להיות |
|---|---|---|
0 | את הערך הראשון ששווה בדיוק ל-lookup_value. מותרים תווים כלליים בטקסט. | בכל סדר |
1 או מושמט | את הערך הגדול ביותר שקטן מ-lookup_value או שווה לו. | ממוינת בסדר עולה |
-1 | את הערך הקטן ביותר שגדול מ-lookup_value או שווה לו. | ממוינת בסדר יורד |
ברירת המחדל היא 1, לא 0. MATCH בלי הארגומנט השלישי שלה, על רשימת שמות לא ממוינת, לא מתלוננת: היא מחזירה מיקום שעשוי להיות של שורה אחרת. כתבו את ה-0 בכל פעם שאתם מחפשים טקסט, קודים או מזהים.
התאמה משוערת: באיזה טווח נמצא ערך
עם סוג התאמה 1, MATCH עונה על "באיזו רמה": המיקום של הסף האחרון שהערך הגיע אליו. כאן ציון של 75 עבר את 0, 60 ו-70, ולכן הוא ברמה 3, ציון C.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Min score | Grade | Score | Band | Grade | |
| 2 | 0 | F | 75 | 3 | C | |
| 3 | 60 | D | 59 | 1 | F | |
| 4 | 70 | C | 90 | 5 | A | |
| 5 | 80 | B | 100 | 5 | A | |
| 6 | 90 | A |
59 עדיין מתחת ל-60, ולכן הוא ברמה 1 (F). 90 הוא התאמה מדויקת לסף האחרון, ו-100 מעליו, ולכן שניהם נוחתים ברמה 5 (A). הספים צריכים להישאר בסדר עולה: זה מה שמאפשר ל-MATCH לעצור במקום הנכון.
סוג התאמה -1 הוא תמונת הראי לרשימה שממוינת מהגדול לקטן. הוא מחזיר את המיקום של הערך הקטן ביותר שעדיין גדול מערך החיפוש או שווה לו: עם הגדלים {50;25;12;5} בעמודה, =MATCH(18,D2:D5,-1) מחזירה 2, הגודל 25, הקטן ביותר שמכיל 18.
תווים כלליים וגודל אותיות
MATCH מתעלמת מגודל האותיות, ועם סוג התאמה 0 היא מקבלת את התווים הכלליים * (כל תווים) ו-? (תו אחד בדיוק). להתאמה רגישה לאותיות, השוו את הרשימה עם EXACT וחפשו את ה-TRUE הראשון.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Category | Price | Starts with "C" | 3 |
| 2 | Apple | Fruit | $1.20 | Lower case "milk" | 5 |
| 3 | Pear | Fruit | $1.50 | Exactly "milk" | #N/A |
| 4 | Carrot | Vegetable | $0.80 | ||
| 5 | Bread | Bakery | $2.40 | ||
| 6 | Milk | Dairy | $1.10 |
"C*" מתאים ל-Carrot, מיקום 3. "milk" מוצא את Milk במיקום 5, כי גודל האותיות לא נחשב. גרסת ה-EXACT משווה כל שם ל-"milk" עם התחשבות בגודל האותיות, לא מוצאת אף TRUE ומחזירה #N/A. שנו את A6 ל-milk והיא מחזירה 5. ב-Excel 2019 ומטה, אשרו את נוסחת ה-EXACT עם Ctrl+Shift+Enter (במק Cmd+Shift+Enter); ב-Excel 2021 וב-Microsoft 365 מספיק Enter. טילדה הופכת תו כללי לתו רגיל: "~*" מחפשת כוכבית אמיתית.
בדיקה אם ערך נמצא ברשימה
MATCH מחזירה מספר כשהיא מוצאת את הערך ו-#N/A כשלא. ISNUMBER הופכת את זה ל-TRUE או FALSE, וזו בדיקה מהירה של "האם זה ברשימה".
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ordered | In stock? | In stock | |
| 2 | Pear | Yes | Apple | |
| 3 | Kiwi | No | Pear | |
| 4 | Milk | Yes | Bread | |
| 5 | Plum | No | Milk | |
| 6 | Bread | Yes |
Pear, Milk ו-Bread נמצאים ב-D2:D5, ולכן הם מציגים Yes. Kiwi ו-Plum מציגים No. COUNTIF($D$2:$D$5,A2)>0 נותנת את אותה תשובה; MATCH עוצרת בהתאמה הראשונה, COUNTIF סופרת את כולן.
MATCH, XMATCH ו-INDEX
MATCH היא חצי מהחיפוש הקלאסי =INDEX(C2:C6,MATCH(E2,A2:A6,0)), שמוסבר בעמוד INDEX ו-MATCH. Excel 2021 ו-Microsoft 365 מוסיפים את XMATCH, שברירת המחדל שלה היא התאמה מדויקת, שיכולה לחפש מהפריט האחרון כלפי מעלה, ושמטפלת ב"הגדול הבא" בלי מיון יורד. MATCH עדיין עובדת בכל מקום, כולל בקבצים שצריכים להיפתח ב-Excel 2019.
תרגול: המיקום של עובד
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Team | Start year | Name | Position | |
| 2 | Ana | Sales | 2019 | Dev | ||
| 3 | Ben | Support | 2021 | |||
| 4 | Cara | Sales | 2018 | |||
| 5 | Dev | Finance | 2022 | |||
| 6 | Eli | Support | 2020 | |||
| 7 | Fay | Finance | 2023 |
תורכם: ב-F2, החזירו את המיקום של השם שב-E2 בתוך הרשימה שבעמודה A.
שאלות נפוצות
מה MATCH מחזירה באקסל?
מיקום, לא ערך: =MATCH("Bread",A2:A6,0) מחזירה 4 כש-Bread הוא התא הרביעי של A2:A6. שלבו אותה עם INDEX כדי לקבל את הערך שבמיקום הזה מעמודה אחרת.
מה ההבדל בין סוגי ההתאמה 0, 1 ומינוס 1?
0 מוצאת התאמה מדויקת בכל סדר. 1 (ברירת המחדל) מוצאת את הערך הגדול ביותר שקטן מערך החיפוש או שווה לו, ודורשת רשימה ממוינת בסדר עולה. -1 מוצאת את הערך הקטן ביותר שגדול ממנו או שווה לו, ודורשת רשימה ממוינת בסדר יורד.
האם MATCH רגישה לאותיות גדולות וקטנות?
לא. =MATCH("milk",A2:A6,0) מוצאת את Milk. להתאמה רגישה לאותיות השתמשו ב-=MATCH(TRUE,EXACT(A2:A6,"milk"),0), שדורשת Ctrl+Shift+Enter (במק Cmd+Shift+Enter) ב-Excel 2019 ומטה.
איך בודקים עם MATCH אם ערך קיים ברשימה?
MATCH מחזירה #N/A כשהערך חסר, אז בדקו אם היא החזירה מספר: =ISNUMBER(MATCH(A2,$D$2:$D$5,0)) נותנת TRUE או FALSE. עטפו אותה ב-IF בשביל טקסט משלכם.
