VLOOKUP: איך מחפשים נתון באקסל לפי קוד או שם

מה עושה VLOOKUP ומה היא מחזירה

VLOOKUP היא פונקציה שמחפשת ערך בעמודה הראשונה של טווח ומחזירה נתון מעמודה שבחרתם באותה שורה. למשל, אפשר לחפש קוד מוצר ולקבל את המחיר שלו, או לחפש מספר עובד ולקבל את שמו. הפונקציה קיימת באקסל ובגוגל שיטס.

הנוסחה באקסל בנויה מארבעה חלקים: VLOOKUP(ערך לחיפוש, טווח, מספר עמודה, [סוג התאמה]). בגוגל שיטס הרכיבים נקראים search_key, range, index ו־is_sorted, והמשמעות זהה. שלושת הראשונים חובה, והרביעי אופציונלי.

• ערך לחיפוש: הערך עצמו או הפניה לתא שמכיל אותו. הוא חייב להופיע בעמודה הראשונה של הטווח.
• טווח: האזור שבו מחפשים. עמודת החיפוש היא העמודה השמאלית בטווח, ועמודת התוצאה צריכה להיות כלולה בו.
• מספר עמודה: מיקום עמודת התוצאה בתוך הטווח, כשהעמודה הראשונה בטווח היא 1. המספר הזה אינו קשור לאות העמודה בגיליון.
• סוג התאמה: FALSE (או 0) להתאמה מדויקת, TRUE (או 1) להתאמה מקורבת.

אם משמיטים את הרכיב הרביעי, הפונקציה פועלת בהתאמה מקורבת. זה עלול להחזיר נתון שלא התכוונתם אליו, ולכן בחיפוש לפי קוד או מזהה מגדירים FALSE במפורש. המבנה מתואר בתיעוד הרשמי של מיקרוסופט.

אם המושגים תא, עמודה וטווח עדיין חדשים לכם, התחילו בהסבר על עבודה באקסל. לעבודה בדפדפן אפשר להיעזר גם במדריך גוגל שיטס למתחילים.

דוגמה לנוסחה שמחזירה מחיר לפי קוד מוצר

נניח שבטווח A2:C5 נמצאת רשימת מוצרים: בעמודה A קוד המוצר, בעמודה B שמו ובעמודה C מחירו. בתא E2 מזינים את הקוד שרוצים לחפש. כדי לקבל את המחיר, כתבו בתא אחר את הנוסחה:

=VLOOKUP(E2,$A$2:$C$5,3,FALSE)

בדוגמה ההיפותטית הזאת, אם הקוד שבתא E2 נמצא בתא A4, הנוסחה תחזיר את תוכן התא C4. המספר 3 מציין את העמודה השלישית בתוך הטווח שנבחר. אילו רצית לקבל את שם המוצר במקום המחיר, היה מוחלף 3 ב־2.

סימני הדולר מקבעים את טווח המוצרים כשמעתיקים את הנוסחה לשורות נוספות. ההפניה E2 נשארת יחסית, כדי שבשורה הבאה אפשר יהיה לחפש את הקוד שבתא E3. גם מיקרוסופט וגם גוגל ממליצות על הפניות מוחלטות לטווח, כדי שהנוסחה לא תזוז כשמעתיקים אותה למטה. קיבוע טווח החיפוש מופיע בהמלצות השימוש של גוגל.

אפשר לחפש גם טקסט, למשל שם של עובד. אם כותבים את הטקסט ישירות בנוסחה ולא דרך הפניה לתא, מקיפים אותו במרכאות, למשל =VLOOKUP("דנה",$A$2:$C$5,3,FALSE). בלי מרכאות מתקבלת שגיאת #NAME?. כשהשם נמצא בתא נפרד, ההפניה לתא מספיקה והמרכאות מיותרות.

ההבדל בין התאמה מדויקת להתאמה מקורבת

FALSE מתאים לחיפוש קוד או מזהה מסוים. אם אין התאמה, מתקבלת שגיאת #N/A. אין צורך למיין את עמודת החיפוש לצורך התאמה מדויקת.

TRUE מפעיל התאמה מקורבת ודורש מיון עולה של עמודת החיפוש, מספרי או אלפביתי. אם אין התאמה מדויקת, מוחזרת תוצאה לפי הערך הגדול ביותר שאינו עולה על ערך החיפוש. אם העמודה אינה ממוינת, התוצאה עלולה להיות שגויה בלי שתופיע שגיאה. אופן ההתאמה מפורט בתיעוד גוגל.

התאמה מקורבת מתאימה לטבלת מדרגות. נניח טבלה היפותטית של סיווג ציונים, שבה עמודה A מכילה את הסף התחתון של כל קטגוריה (0, 60, 70, 80, 90) ועמודה B מכילה את שם הקטגוריה. ציון 75 לא מופיע בעמודה A, ולכן הנוסחה תחזיר את הקטגוריה של הסף 70, שהוא הגדול ביותר שאינו עולה על 75.

אם ערך החיפוש קטן מהערך הקטן ביותר בעמודה הראשונה, גם בהתאמה מקורבת מתקבלת שגיאת #N/A. בטבלה הזאת אפשר להימנע מכך אם הסף הנמוך ביותר הוא 0.

ניקוי נתונים ותווים כלליים בחיפוש

רוב התקלות ב־VLOOKUP נובעות מהנתונים ולא מהנוסחה. לפי מיקרוסופט, כדאי לנקות את הנתונים לפני החיפוש:

• להסיר רווחים בתחילת הערך או בסופו. אפשר להיעזר בפונקציה TRIM.
• להסיר תווים שאינם מודפסים, בעזרת הפונקציה CLEAN.
• לאחד מרכאות ישרות ומרכאות מסולסלות.
• לא לשמור מספרים ותאריכים כטקסט, כי זה עלול להחזיר תוצאות שגויות או בלתי צפויות.

בהתאמה מדויקת אפשר להשתמש בתווים כלליים. סימן שאלה (?) מייצג תו בודד, כוכבית (*) מייצגת רצף תווים כלשהו, וטילדה (~) לפני תו גורמת לו להיחשב תו רגיל. כך אפשר, למשל, לחפש קוד שמתחיל באותיות קבועות ושאר התווים בו משתנים. בגוגל שיטס התווים הכלליים פועלים רק בהתאמה מדויקת.

התווים הכלליים מועילים כשמכירים רק חלק מהערך, אבל הם יכולים להחזיר התאמה שלא התכוונתם אליה. בחיפוש לפי מזהה מלא עדיף להזין את המזהה כפי שהוא.

מתי לשקול שימוש ב־XLOOKUP

VLOOKUP מחפשת רק משמאל לימין: ערך החיפוש חייב להיות בעמודה הראשונה של הטווח, ולכן אי אפשר להחזיר נתון מעמודה שמשמאל לה. כשבונים נוסחה חדשה והגרסה שלכם תומכת בכך, כדאי לשקול XLOOKUP.

לפי מיקרוסופט, ההבדלים העיקריים הם:

• XLOOKUP מפרידה בין טווח החיפוש לטווח התוצאה, ולכן אפשר להחזיר נתון משני צדי עמודת החיפוש.
• אין צורך לספור עמודות. מציינים ישירות את טווח התוצאה.
• ברירת המחדל היא התאמה מדויקת.
• יש ארגומנט מובנה לטקסט שיוצג כשאין התאמה, במקום #N/A.
• אפשר להחזיר כמה עמודות בנוסחה אחת.

XLOOKUP זמינה ב־Microsoft 365, באקסל 2024 ובאקסל 2021, וגם באקסל לאייפד, לאייפון ולמכשירי אנדרואיד. היא אינה זמינה באקסל 2016 ובאקסל 2019. אם הקובץ מיועד למשתמשים בגרסאות האלה, זו מגבלה שצריך להביא בחשבון לפני שמחליפים נוסחאות קיימות. במקרה כזה VLOOKUP נשארת האפשרות הפשוטה שעובדת בכל הגרסאות.

איך מאתרים שגיאה או תוצאה לא צפויה

גם תוצאה שנראית סבירה דורשת בדיקה מול שורת המקור. לפי תיעוד גוגל, כשאותו מפתח מופיע כמה פעמים, מוחזרת ההתאמה הראשונה בלבד.

• מתקבלת שגיאת #N/A: בהתאמה מדויקת הערך לא נמצא. בדקו שהמזהה קיים בטווח, שאין רווחים מיותרים ושמספר אינו שמור כטקסט באחד הצדדים.
• מתקבלת שגיאת #REF!: בדקו שמספר עמודת התוצאה אינו גדול ממספר העמודות בטווח.
• מתקבלת שגיאת #VALUE!: בגוגל שיטס היא מציינת שהאינדקס אינו תקין או קטן מ־1.
• מתקבלת שגיאת #NAME?: ייתכן שטקסט בנוסחה נכתב בלי מרכאות.
• מתקבלת שגיאת #SPILL! באקסל: לפי מיקרוסופט, היא נגרמת בדרך כלל מהפניה לעמודה שלמה כערך החיפוש. הפנו לתא בודד, למשל A2.
• מוחזר נתון שגוי: בדקו שהוגדר FALSE לחיפוש מדויק ושבחרתם את עמודת התוצאה הנכונה.
• הנוסחה משתבשת אחרי העתקה: בדקו שטווח החיפוש מקובע ושהוא כולל את הרשומות הדרושות.

בגוגל שיטס אפשר להחליף את #N/A בהודעה משלכם באמצעות הפונקציה IFNA. כך רשימה ארוכה לא מתמלאת בשגיאות כשחלק מהקודים חסרים.

בדיקות לפני שמעתיקים את הנוסחה לרשימה שלמה

1. ודאו שעמודת החיפוש היא הראשונה בטווח.
2. ספרו את עמודת התוצאה מתחילת הטווח שבחרתם ולא מתחילת הגיליון.
3. הגדירו FALSE, אלא אם אתם בונים טבלת מדרגות ממוינת.
4. קבעו את הטווח בסימני דולר.
5. נקו רווחים ותווים לא מודפסים, ובדקו שמספרים ותאריכים אינם שמורים כטקסט.
6. נסו קוד שקיים בטבלה וקוד שאינו קיים בה, וודאו שהתוצאות מתאימות לציפייה.
7. בדקו אם יש קודים כפולים, כדי לדעת איזו רשומה תוחזר. התוצאה היא הרשומה הראשונה.

אחרי שהנוסחה עובדת בשורה אחת, גררו אותה לשאר השורות והשוו כמה תוצאות באקראי מול הטבלה המקורית.