XLOOKUP עושה כל מה ש-VLOOKUP עושה, עם פחות דרכים לטעות: היא מתאימה בדיוק כברירת מחדל, מקבלת את עמודת ההחזרה כטווח במקום כמספר, יכולה לחפש שמאלה ויש לה ארגומנט משלה ל"לא נמצא". השתמשו ב-VLOOKUP כשהקובץ צריך לעבוד ב-Excel 2019 ומטה, שבהן XLOOKUP לא קיימת. הגיליון למטה מריץ את אותו חיפוש בשתי הדרכים.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Pear | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | XLOOKUP | $1.50 | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
שתיהן מחזירות $1.50. לחצו על כל נוסחה: VLOOKUP מסמנת את כל הטבלה A2:D6 וסופרת 3 עמודות לתוכה; XLOOKUP מסמנת רק את העמודה שבה היא מחפשת ואת העמודה שממנה היא מחזירה.
השוואה בין VLOOKUP ל-XLOOKUP
| VLOOKUP | XLOOKUP | |
|---|---|---|
| גרסאות אקסל | כולן | Excel 2021, 2024, Microsoft 365, אינטרנט |
| התאמה כברירת מחדל | משוערת (אם הארגומנט הרביעי מושמט) | מדויקת |
| עמודת ההחזרה | מספר שנספר לתוך הטבלה | טווח |
| הוספת עמודה בתוך הטבלה | מחזירה את העמודה הלא נכונה | ממשיכה לעבוד |
| חיפוש שמאלה | לא | כן |
| ערך שלא נמצא | #N/A, עטפו ב-IFNA | ארגומנט רביעי, "Not found" |
| התאמה אחרונה | לא | search_mode -1 |
| כמה עמודות בבת אחת | אחת לכל נוסחה (או {2,3} כמספר העמודה ב-Microsoft 365) | טווח החזרה של כמה עמודות שופך אותן |
| התאמה משוערת | הקטן הבא, הנתונים חייבים להיות ממוינים | הקטן הבא או הגדול הבא, בכל סדר |
| תווים כלליים | פעילים עם FALSE | רק עם match_mode 2 |
| חיפוש אופקי | צריך HLOOKUP | אותה פונקציה |
שתיהן מתעלמות מגודל האותיות. בהתאמה מדויקת שתיהן מחזירות את ההתאמה הראשונה מלמעלה, אלא אם אומרים ל-XLOOKUP לחפש מלמטה.
לא נמצא: IFNA מול הארגומנט הרביעי
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Kiwi | |
| 2 | Apple | Fruit | $1.20 | 40 | VLOOKUP | #N/A | |
| 3 | Pear | Fruit | $1.50 | 25 | with IFNA | Not found | |
| 4 | Carrot | Vegetable | $0.80 | 60 | XLOOKUP | Not found | |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A הערך שחיפשתם לא נמצא בטווח החיפוש.VLOOKUP רגילה מציגה #N/A. VLOOKUP צריכה עטיפה של IFNA כדי להציג טקסט, ו-XLOOKUP עושה את זה עם הארגומנט הרביעי שלה. הקלידו Milk ב-G1 ושלושתן מסכימות: $1.10.
חיפוש שמאלה והזזת עמודות
שתי המגבלות המבניות של VLOOKUP נובעות ממספר העמודה. היא יכולה לספור רק ימינה מהעמודה שבה היא מחפשת, והמספר לא משתנה כשהטבלה משתנה: הוסיפו עמודה בין Category ל-Price, ו-=VLOOKUP(G1,A2:D6,3,FALSE) עדיין מחזירה את עמודה 3, שהיא עכשיו העמודה החדשה. XLOOKUP מפנה לעמודת ההחזרה כטווח, ולכן אקסל מתאים אותה כמו כל הפניה אחרת, ועמודת החיפוש יכולה להיות בכל מקום.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | $0.80 | |
| 2 | Apple | Fruit | $1.20 | 40 | XLOOKUP | Carrot | |
| 3 | Pear | Fruit | $1.50 | 25 | INDEX MATCH | Carrot | |
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
שתיהן מחזירות Carrot. אין גרסת VLOOKUP: השם נמצא משמאל למחיר. באקסל ישן התשובה היא INDEX MATCH, שמוצגת ב-G3, והיא גם שורדת הוספת עמודות. העמוד INDEX ו-MATCH מסביר אותה.
התאמה משוערת, בשתי הדרכים
לטווחים, VLOOKUP משתמשת ב-TRUE וצריכה שהעמודה הראשונה תהיה ממוינת בסדר עולה. XLOOKUP משתמשת ב-match_mode -1 ולא צריכה מיון, ו-match_mode 1 נותן במקום זאת את הערך הגדול הבא, ש-VLOOKUP לא יכולה לתת.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Sales from | Rate | Sales | 4,200 | |
| 2 | 0 | 0% | VLOOKUP | 3% | |
| 3 | 1000 | 3% | XLOOKUP | 3% | |
| 4 | 5000 | 5% | |||
| 5 | 10000 | 8% |
שתיהן מחזירות 3% עבור 4,200. שנו את E1 ל-10000 ושתיהן מחזירות 8%.
מתי להישאר עם VLOOKUP
- הקובץ משותף עם אנשים שעובדים ב-Excel 2019, 2016 או ישן יותר. XLOOKUP מציגה שם #NAME?. INDEX MATCH היא הבחירה האחרת שעובדת בכל מקום.
- בחוברת העבודה כבר יש מאות פונקציות VLOOKUP שעובדות. כתיבה מחדש שלהן מרוויחה מעט; השתמשו ב-XLOOKUP לנוסחאות חדשות.
מהירות אינה סיבה לבחור באחת מהן. בגיליונות רגילים שתיהן מיידיות, וברשימות ממוינות גדולות מאוד גם החיפוש הבינארי של XLOOKUP (search_mode 2) וגם VLOOKUP עם TRUE מהירים.
Google Sheets תומך בשתי הפונקציות עם אותם ארגומנטים.
תרגול: כתבו מחדש VLOOKUP
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Bread | |
| 2 | Apple | Fruit | $1.20 | 40 | Stock | ||
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
תורכם: =VLOOKUP(G1,A2:D6,4,FALSE) מחזירה את המלאי של המוצר שב-G1. ב-G2, כתבו את אותו חיפוש עם XLOOKUP.
שאלות נפוצות
האם XLOOKUP עדיפה על VLOOKUP?
לעבודה חדשה ב-Excel 2021 או ב-Microsoft 365, כן: ברירת המחדל שלה היא התאמה מדויקת, אין לה מספר עמודה שנשבר כשעמודות זזות, היא מחפשת שמאלה, ויש לה טקסט מובנה ל"לא נמצא". VLOOKUP עדיפה רק כשהקובץ צריך לעבוד ב-Excel 2019 ומטה, שם XLOOKUP מציגה #NAME?.
האם XLOOKUP מהירה יותר מ-VLOOKUP?
לא באופן שתרגישו בגיליונות רגילים; שתיהן מחפשות כמה אלפי שורות מיד. בנתונים ממוינים גדולים מאוד, מצב החיפוש הבינארי של XLOOKUP (search_mode 2) מהיר יותר מחיפוש ליניארי, וגם VLOOKUP עם TRUE מחפשת באופן בינארי.
מה ההבדל בין XLOOKUP ל-INDEX MATCH?
הן יכולות לעשות את אותם חיפושים. XLOOKUP היא פונקציה אחת עם ארגומנטים פשוטים יותר וארגומנט ל"לא נמצא"; INDEX MATCH עובדת בכל גרסה של אקסל. =XLOOKUP(F2,A2:A6,C2:C6) ו-=INDEX(C2:C6,MATCH(F2,A2:A6,0)) מחזירות את אותו ערך.
איך ממירים VLOOKUP ל-XLOOKUP?
השאירו את ערך החיפוש, פצלו את הטבלה לעמודה הראשונה ולעמודה שהחזרתם, והשמיטו את מספר העמודה ואת FALSE: =VLOOKUP(F2,A2:D6,3,FALSE) הופכת ל-=XLOOKUP(F2,A2:A6,C2:C6).
