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

צריכים לחזק את יכולות ה- VLOOKUP שלכם? היכנסו לפינת תרגילי האקסל שלנו והתחילו לתרגל! 

לצפייה במדריך וידאו של פונקציית VLOOKUP – לחצו כאן

מטרת פונקציית VLOOKUP

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

לדוגמא – חפש מספר טלפון בטבלת לקוחות, ועבור הטלפון שמצאת החזר את שם בעל קו הטלפון:

דוגמא נוספת – ברשותך שתי טבלאות. בטבלה אחת מופיעים מספרי תעודות הזהות של העובדים ומשכורותיהם. בטבלה השנייה מופיעים שמות העובדים ומספרי תעודות הזהות שלהם. נתבקשת על ידי המנהלת במשרד להצליב בין השמות לבין המשכורות. האם ניתן לעשות זאת? כמובן! החוליה המקשרת בין שתי הטבלאות היא מספרי תעודות הזהות, ולכן אנו נריץ בתוך הטבלה השנייה (של שמות העובדים ותעודות הזהות שלהם) VLOOKUP שיחפש לפי תעודת זהות בטבלה הראשונה, ויחזיר את המשכורת המתאימה לכל מספר תעודת זהות.

 

כיצד כותבים את פונקציית ה- VLOOKUP?

להלן צורת הכתיבה הבסיסית של הפונקצייה:

=VLOOKUP(VALUE,TABLE,COLUMN NUMBER,MATCH TYPE)

הסבר:

VALUE – זהו הערך שאתם רוצים למצוא. זה יכול להיות מספר מסוים, מלל (ואז יש צורך להכניס את המלל בתוך גרשיים: “מלל”), וזה גם יכול להיות תא.

TABLE – זוהי הטבלה שבה נרצה לחפש את הערך, ובה נמצא את הערך המתאים שנרצה להחזיר.

מספר נקודות חשובות בנוגע להגדרת הטבלה:

• הגדרת הטבלה – ניתן להגדיר את הטבלה על ידי סימון טווח עם העכבר או על ידי הקלדה. דוגמא לטווח של טבלה: A1:C7 – כלומר, טבלה בת שלוש עמודות ושבע שורות. בנוסף, ניתן להגדיר את הטבלה כרצף של כמה עמודות. למשל מעמודה A עד עמודה C. בנוסף – ניתן להגדיר את הטבלה כ- NAME (הסבר על NAMES יופיע במדריך נפרד).
• קיבוע – בעת הגדרת הטבלה, חשוב מאוד לבצע קיבוע לטבלה. באמצעות הקיבוע אנו מוודאים שגם אם נגרור את פונקציית ה- VLOOKUP למעלה למטה (כדי שנוכל לבצע את החיפוש עבור ערכים נוספים), הטבלה שבה אנו מחפשים לא תזוז ממקומה. כיצד מקבעים? מיד בתום סימון הטבלה, לחצו על F4 כך שיופיעו לכם סימני $ לפני כל אות ולפני כל מספר בטבלה שהגדרתם. זה צריך להיראות כך:      שימו לב – אם הגדרתם את הטבלה כעמודות שלמות (A:C לדוגמא) – אין חובה לבצע קיבוע.
• מאיזו עמודה מתחילים לסמן את הטבלה? כלל האצבע פשוט מאוד – העמודה הראשונה שתסומן היא העמודה שבה צריך להיות הערך שאתם מחפשים. אם ביקשתם למצוא מספר טלפון מסוים, העמודה הראשונה שתסמנו תהיה עמודת מספרי הטלפון. אם לא תעבדו לפי כלל האצבע הזה – הפונקציה לא תעבוד.
• ועד איזו עמודה צריך לסמן? עליכם לסמן לפחות עד העמודה שמכילה את הערך שתרצו להחזיר במידה ותימצא התאמה לערך שחיפשתם. למה לפחות? כי ניתן לסמן גם עוד כמה עמודות “אקסטרא” – זה לא מפריע לאקסל, משום שאנו נסביר לו בהמשך הפונקציה באיזו עמודה בדיוק עליו לחפש.
• האם חייב לסמן גם את שורת הכותרת של הטבלה (במידה וישנה כזו)? התשובה שלילית. אפשר לסמן, ואפשר גם לא – אין לכך חשיבות.
• מה עושים אם העמודה שאני מחפש נמצאת אחרי העמודה שממנה אני צריך להחזיר את הערך המבוקש?  

כפי שניתן לראות, הפונקציה מחזירה #N/A – משמע, היא לא מוצאת את הערך. למרבה הצער, פונקציית ה- VLOOKUP עובדת בכיוון אחד בלבד – אם היישור הוא מימין לשמאל (כמו בדוגמא הנ”ל) – הפונקצייה נעה באותו כיוון – מימין לשמאל – ולהפך. לאור העובדה שעמודת השמות נמצאת אחרי עמודת תעודות הזהות, לא ניתן יהיה להחזיר תוצאה.

איך בכל זאת פותרים את הבעיה? 

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

או על ידי סימון העמודה שברצונכם להזיז, ואז לחיצה בגבול הסימון (כפי שרואים בדוגמא). החזיקו את העכבר, והזיזו את הבחירה למקום הרצוי. אם תחזיקו את מקש ה- SHIFT לחוץ תוך כדי, תוכלו ממש לבצע החלפה מהירה בין הטווחים!

דרך שנייה – לא להשתמש בפונקציית VLOOKUP. יש לפונקציה תחליפים שעובדים לשני הכיוונים, כגון שילוב הפונקציות INDEX + MATCH.

COLUMN NUMBER – זהו מספר העמודה היחסי שבו נמצא הערך אותו אנו מבקשים להחזיר. מה משמעות היחסי? אם לדוגמא הטבלה שלנו מתחילה בעמודה A, והערך שלנו נמצא בעמודה D – המספר היחסי שלנו הוא 4 – שזה בעצם עמודות A,B,C,D – סה”כ 4. ואם אנחנו מתחילים בעמודה B? אז המספר יהיה 3 – שמייצג את עמודות B,C,D. כיצד נוכל לספור מהר את מספר העמודות בטבלה? 

כאשר אנו מסמנים את טווח טבלת החיפוש, צצה לנו תיבה קטנה, כפי שרואים בטבלה הבאה (מוקפת באדום):

זה בעצם מציג את מימדי הטבלה. R = שורות, C = עמודות. לפיכך, בטבלה שלנו יש 3 עמודות בסך הכל. כעת, בחלק שבו אנו מזינים את מספר העמודה, אם נרצה להחזיר את “שכר”, נזין 3. אם נרצה להחזיר מספר ת.ז. – נזין 2. הטריק הזה שימושי מאוד כשמתעסקים עם טבלאות בנות עשרות עמודות.

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

גם כאן, ניתן לראות שיש בטווח המסומן 6 שורות (R), ו- 3 עמודות (C)

 

ואחרון חביב…

MATCH TYPE  – זהו הפרמטר הכי פחות מעניין בפונקציה שלנו. כאן אנחנו נשאלים אם אנחנו מעוניינים בהתאמה מוחלטת (במקרה כזה נזין את הספרה 0 או FALSE), או בהתאמה חלקית (במקרה כזה נזין את הספרה 1, או TRUE). ב- 99.9999% מהמקרים אתם תרצו לעשות שימוש בהתאמה מוחלטת – קרי 0 או FALSE. למעשה, ניתן לכתוב את הפונקצייה אפילו ללא שימוש בפרמטר MATCH TYPE, באופן הבא:

=VLOOKUP(VALUE,TABLE,COLUMN NUMBER)

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

חדש: תרגלו את הנוסחה באונליין – הקלידו בתא B12 את התוצאה הנדרשת! 

*למשתמשים בנייד – הקליקו פעמיים על התא כדי להיכנס למצב עריכה! 

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

 

תקלות נפוצות ב- VLOOKUP ופתרונן

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

•  שגיאת #REF – יכולה לקרות מהסיבות הבאות:
  •  מספר העמודה שביקשתם להחזיר גבוה ממספר העמודות בטבלה. נניח שגודל הטבלה שהגדרתם הוא 4 עמודות, וביקשתם להחזיר את עמודה מספר 5 – זה לא יעבוד… 🙂
  • באחד מהפרמטרים של הפונקציה הזנתם הפניה לתא/גיליון שאינו קיים. לדוגמא – ביקשתם לערוך את החיפוש בטבלה שנמצאת בגיליון אחר בקובץ, אלא מה? שכחתם שמחקתם כבר את הגיליון הזה או ששיניתם את שמו…
  • מחקתם עמודה מסוימת בטבלת החיפוש שלכם? המחיקה שיבשה את הגדרת טווח הטבלה. תצטרכו להגדיר שוב את הטבלה כדי שהפונקציה תעבוד
•  שגיאת #NAME – האם רשמתם נכון את שם הפונקציה?
• שגיאת #VALUE – האם הטבלה שבה אתם מחפשים נמצאת בגיליון חיצוני? אם כן – עליכם לוודא שהכתובת שהזנתם תקינה. המלצה חמה – אל תמשכו נתונים מקבצים חיצוניים, אלא העבירו את המידע הנחוץ לגיליונות עזר בקובץ שלכם. זה מתכון לאסון.
• שגיאת #N/A – שגיאת “ערך לא נמצא”. זו השגיאה הנפוצה ביותר שתתקלו בה לא מעט פעמים. ממה היא נובעת?
  • הערך שאתם מחפשים שונה במקצת מהערך שקיים בטבלה. הדוגמא הכי טובה היא במידה ואתם מחפשים שם של בן אדם, ובמקום אחד הוא מאוית באופן אחד, ובמקום האחר – בצורה קצת שונה (או שאולי יש לו גם שם משפחה נוסף שהושמט באחת הטבלאות). מבחינת ה- VLOOKUP אלו שני דברים שונים לחלוטין. אין כאן פתרון קסם מלבד חיפוש ידני והפעלת היגיון בריא.
  • בעיית רווחים – זו אחת הבעיות הקשות ביותר לאיתור, אך הסיכוי שתתקלו בה בעת עבודה עם טבלאות טקסטואליות הוא גבוה במיוחד. מבחינת ה- VLOOKUP, אין כל דימיון בין השם “משה” לבין השם “משה ” שבטעות הוקלד לאחריו רווח, או ” משה” שבו מישהו בטעות לחץ רווח לפני השם. נסו לראות בעין חוסר סימטריה בעמודת השמות, או לבדוק מדגמית כמה “חשודים”. זיהיתם מקרה כזה? סביר להניח שגם שאר הטבלה סובלת מאותה בעיה בדיוק. במקרה הזה מומלץ לעשות שימוש בפונקציה TRIM שמקצצת את כל הרווחים המיותרים בתאים טקסטואליים.
  • האם הקפדתם שהעמודה הראשונה בטבלת החיפוש ב- VLOOKUP היא העמודה שאמורה להכיל את הערך אותו אתם מחפשים? אם לא – תקנו את הנוסחה.
  • האם ביצעתם קיבוע מלא לטבלת החיפוש? אם לא, סביר להניח שבעת שביצעתם גרירה לנוסחה, טבלת החיפוש שלכם זזה באופן יחסי לגרירה ולכן נוצר השיבוש. אל תשכחו לקבע! 
  • גורם מרכזי לתקלה הוא עיצוב של תאים מסוימים בטבלה כטקסט, דבר שמשבש את פעילות הפונקציה. זו אחת התקלות הנפוצות ביותר – וכתבנו עליה מדריך נפרד שמסביר כיצד להתמודד עם הבעיה שניתן לפתוח בלחיצה על הקישור הנ”ל.  שימו לב – זו אחת התקלות הנפוצות והמעצבנות ביותר. 

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