VLOOKUP לעומת XLOOKUP: איזו מהן כדאי לבחור?
שתי הפונקציות מוצאות ערך בעמודה אחת ומחזירות ערך תואם מאותה שורה. XLOOKUP היא החדשה יותר ומתקנת רוב המגבלות של VLOOKUP, אבל VLOOKUP עדיין נמצאת בכל מקום בחוברות עבודה קיימות.
המדריך מציג את ההבדלים שחשובים ודרך פשוטה לבחור.
VLOOKUP: חיפוש ימינה, ספירת עמודות
VLOOKUP תמיד מחפשת בעמודה הראשונה של הטבלה שנתת לה, ומחזירה ערך מהעמודה שמספרה ספרת. הארגומנט האחרון צריך להיות FALSE להתאמה מדויקת; אם משמיטים אותו, מקבלים התאמה משוערת שדורשת נתונים ממוינים.
דוגמה: מחיר לפי מזהה מוצר עם VLOOKUP
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | מזהה מוצר | מוצר | מחיר | מלאי | מזהה מוצר | P-102 | |
| 2 | P-101 | מנורת שולחן | 24.5 | 40 | מחיר | 149 | |
| 3 | P-102 | כיסא משרד | 149 | 12 | |||
| 4 | P-103 | מעמד למסך | 39.9 | 25 | |||
| 5 | P-104 | מקלדת | 59 | 0 |
המחיר נמצא בעמודה השלישית של A2:D5. אם מישהו יוסיף עמודה בתוך הטבלה, המספר 3 עלול להצביע על הנתונים הלא נכונים.
XLOOKUP: לכל כיוון, בלי מספרי עמודות
XLOOKUP מקבלת את העמודה לחיפוש ואת העמודה להחזרה כשני טווחים נפרדים. הם יכולים להיות בכל מקום, ולכן אפשר לחפש גם שמאלה, והוספת עמודות לא שוברת את הנוסחה. התאמה מדויקת היא ברירת המחדל.
דוגמה: חיפוש שמאלה עם XLOOKUP
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | מזהה מוצר | מוצר | מחיר | מלאי | מזהה של המקלדת | |
| 2 | P-101 | מנורת שולחן | 24.5 | 40 | P-104 | |
| 3 | P-102 | כיסא משרד | 149 | 12 | ||
| 4 | P-103 | מעמד למסך | 39.9 | 25 | ||
| 5 | P-104 | מקלדת | 59 | 0 |
המזהה נמצא בעמודה A, משמאל לשם שבעמודה B. VLOOKUP לא יכולה להחזיר עמודה שנמצאת משמאל לעמודת החיפוש שלה.
הבדלים נוספים
ערכים חסרים: VLOOKUP מחזירה #N/A, ולכן עוטפים אותה ב-IFNA או ב-IFERROR. ל-XLOOKUP יש ארגומנט רביעי מובנה להודעה במקרה כזה.
כיוון: XLOOKUP יכולה לחפש מהפריט האחרון (מצב חיפוש -1), וזה שימושי לרשומה האחרונה. VLOOKUP מוצאת רק את ההתאמה הראשונה.
התאמות משוערות: VLOOKUP דורשת נתונים ממוינים בסדר עולה. מצבי ההתאמה -1 ו-1 של XLOOKUP עובדים גם על נתונים לא ממוינים.
גרסאות: XLOOKUP קיימת ב-Excel 2021, ב-Excel 2024, ב-Microsoft 365 וב-Excel for the web. היא לא קיימת ב-Excel 2019 או בגרסאות ישנות יותר, והקובץ מציג שם #NAME?. VLOOKUP עובדת בכל גרסה.
כלל פשוט
השתמש ב-XLOOKUP בעבודה חדשה, כשכל מי שפותח את הקובץ משתמש ב-Excel 2021 ומעלה. השתמש ב-VLOOKUP (או ב-INDEX עם MATCH) כשהקובץ חייב להיפתח בגרסאות ישנות, וכשאתה מתחזק נוסחאות VLOOKUP קיימות, שממשיכות לעבוד.
טעויות נפוצות
השמטת FALSE ב-VLOOKUP. ההתאמה הופכת למשוערת, והיא עלולה להחזיר שורה שגויה בלי שום שגיאה.
תיקון: כתוב תמיד FALSE (או 0) כארגומנט האחרון, אלא אם אתה רוצה התאמה משוערת.
מספרי עמודות קשיחים. הוספה או מחיקה של עמודה בתוך הטבלה משנה לאיזו עמודה המספר מצביע.
תיקון: השתמש ב-XLOOKUP, או ב-MATCH כדי לקבל את מספר העמודה, כך שהנוסחה תעקוב אחרי הכותרות.
שאלות נפוצות
האם VLOOKUP עומדת לצאת משימוש?
לא. Microsoft שומרת עליה לתאימות, והיא עדיין החיפוש הנפוץ ביותר בחוברות עבודה קיימות. כדאי להכיר אותה גם לבדיקות ולקבצים משותפים.
האם XLOOKUP מהירה יותר מ-VLOOKUP?
בגיליונות היומיומיים ההבדל לא מורגש. בחר לפי הגמישות ולפי גרסאות Excel שצריכות לפתוח את הקובץ.
פונקציות קשורות
- VLOOKUPVLOOKUP מחפשת ערך בעמודה הראשונה של טבלה, ומחזירה ערך מאותה שורה בעמודה אחרת.
- XLOOKUPXLOOKUP מחפשת בעמודה או בשורה אחת, ומחזירה את הערך המתאים מעמודה אחרת, לכל כיוון, עם הודעת "לא נמצא" מובנית.
- INDEXINDEX מחזירה את הערך בשורה ובעמודה נתונות של טווח.
- MATCHMATCH מחזירה את המיקום של ערך ברשימה, למשל שהשם "לנה" הוא השלישי.
- IFERRORIFERROR מחזירה ערך שתבחר כשנוסחה מייצרת שגיאה כמו #N/A או #DIV/0!, ואחרת את התוצאה הרגילה.
עוד על VLOOKUP ו-XLOOKUP
המדריך משווה בין שתי הפונקציות ומציע כלל אצבע. החלק הזה מוסיף מקרים מעובדים שבהם אחת עדיפה על השנייה, איך להעביר VLOOKUP ישן בבטחה, והטעויות השקטות ששתיהן מאפשרות.
המרת VLOOKUP שלב אחרי שלב
קח VLOOKUP שמחפש מזהה ב-A2 בטבלה E2:G50 ומחזיר את עמודה 3. כדי להמיר אותו, החלף את הטבלה בשני טווחים נפרדים: העמודה לחיפוש והעמודה להחזרה. מספר העמודה נעלם.
=VLOOKUP(A2,E2:G50,3,FALSE)
=XLOOKUP(A2,E2:E50,G2:G50,"not found")בדוק את הנוסחה החדשה על כמה מזהים, כולל מזהה שלא קיים, לפני שמחליפים את הישנה בכל מקום.
טעויות שנשארות בשקט
- השמטה של הארגומנט הרביעי של VLOOKUP, או כתיבה של TRUE, פירושה התאמה משוערת. בנתונים לא ממוינים היא מחזירה ערך שגוי במקום שגיאה.
- הוספת עמודה בתוך טבלה של VLOOKUP משנה בשקט לאיזו עמודה המספר 3 מצביע.
- רווח בסוף, או מזהה ששמור כטקסט בצד אחד ומספר בצד השני, גורמים לשתי הפונקציות להחזיר לא נמצא. בדוק את הסוגים.
- שתי הפונקציות מחזירות את ההתאמה הראשונה. אם מפתחות חוזרים, השורות האחרות לעולם לא מגיעות.
מתי VLOOKUP עדיין בסדר
XLOOKUP דורשת Excel 2021. ב-Excel 2019 אין אותה, וגם לא בגרסאות שקודמות לה. אם קובץ ייפתח על ידי עמיתים בגרסה ישנה, VLOOKUP או זוג INDEX ו-MATCH בטוחים יותר. להתאמה משוערת ולהתאמה עם תווים כלליים, ראה את [תווים כלליים ב-Excel](/guides/wildcards).