VLOOKUP בעברית פשוטה: חיפוש בין שתי טבלאות

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

מה vlookup עושה, במשפט אחד

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

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

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

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

המבנה של vlookup הוא תמיד אותו מבנה:

=VLOOKUP(מה_מחפשים, איפה_מחפשים, מספר_העמודה, סוג_ההתאמה)

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

דוגמה מלאה, שלב אחר שלב

נניח שבגיליון "תלמידים" יש בעמודה A מספרי זהות ובעמודה B שמות. בגיליון "טלפונים" יש בעמודה A מספרי זהות, בעמודה B שם ההורה ובעמודה C מספר הטלפון, בשורות 2 עד 120.

1. בגיליון "תלמידים", כתבו בתא C1 את הכותרת "טלפון הורה".
2. עמדו על C2 והקלידו: =VLOOKUP(A2, טלפונים!$A$2:$C$120, 3, FALSE)
3. לחצו Enter. אם מספר הזהות שב-A2 קיים בגיליון הטלפונים, יופיע מספר הטלפון המתאים.
4. גררו את ידית המילוי (הריבוע הקטן בפינת התא) עד סוף הרשימה, או לחצו עליה לחיצה כפולה.

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

ולמה סימני הדולר? בלעדיהם, כשגוררים את הנוסחה למטה, גם טווח החיפוש היה זז שורה בכל פעם, ובשורות האחרונות הוא היה מפספס חלק מהטבלה. הדולרים "מקבעים" את הטווח, כך שכל שורה מחפשת באותה טבלה מלאה. אפשר להוסיף אותם מהר: אחרי שסימנתם את הטווח בתוך הנוסחה, לחצו F4.

דוגמה שנייה מהבית: רשימת קניות ומחירון

vlookup לא שמורה רק למשרד. נניח שאתם מנהלים רשימת קניות קבועה לבית, ובגיליון נפרד שמרתם מחירון קטן: בעמודה A שם המוצר, בעמודה B המחיר האחרון ששילמתם. בגיליון הרשימה, בכל שבוע אתם כותבים בעמודה A מה צריך לקנות ובעמודה B כמה יחידות.

בעמודה C של הרשימה כתבו: =VLOOKUP(A2, מחירון!$A$2:$B$80, 2, FALSE). הנוסחה תמשוך לכל מוצר את המחיר מהמחירון. בעמודה D כתבו =B2*C2, ובתחתית סכמו את עמודה D. עכשיו יש לכם הערכה של עלות הקנייה עוד לפני שיצאתם מהבית.

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

vlookup בגיליון מימין לשמאל

הרבה אנשים בישראל עובדים בגיליון מימין לשמאל, ושם הבלבול מתחיל. הפונקציה לא מתעניינת בכיוון התצוגה. "העמודה הראשונה" היא תמיד העמודה עם האות המוקדמת יותר באלף-בית האנגלי, ו"מספר העמודה" נספר לפי סדר האותיות: A, B, C.

בגיליון מימין לשמאל, עמודה A נמצאת בצד ימין, ולכן בפועל vlookup "זזה שמאלה" כדי למצוא את הנתון. אם תחשבו על האותיות ולא על הכיוון, לא תתבלבלו.

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

למה vlookup מחזירה N/A# גם כשהערך קיים

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

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

כשהערך באמת לא קיים בטבלה השנייה, N/A# היא התשובה הנכונה. כדי להציג משהו נקי יותר, אפשר לעטוף את הנוסחה: =IFERROR(VLOOKUP(…), "לא נמצא"). רק שימו לב שזה מסתיר גם שגיאות אמיתיות, ולכן כדאי להוסיף את העטיפה רק אחרי שבדקתם שהנוסחה עצמה עובדת.

FALSE או TRUE: מתי צריך התאמה מקורבת

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

יש שימוש לגיטימי ל-TRUE: טבלת מדרגות. למשל, ציון 0 עד 54 הוא "נכשל", 55 עד 74 "עובר", 75 ומעלה "טוב". בונים טבלה קטנה עם הסף התחתון של כל מדרגה, ו-vlookup עם TRUE תמצא לכל ציון את המדרגה שלו.

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

החלופות: XLOOKUP, INDEX ו-MATCH

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

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

הצירוף INDEX ו-MATCH הוא הדרך הוותיקה לעקוף את המגבלות. הוא מעט פחות אינטואיטיבי, ולמתחילים XLOOKUP נוחה יותר.

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

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

1. בחרו שלוש-ארבע שורות באקראי, מתחילת הרשימה, מאמצעה ומסופה.
2. לכל אחת, העתיקו את מספר הזהות ובגיליון השני השתמשו ב-Ctrl+F כדי למצוא אותו ידנית.
3. השוו את הטלפון שמצאתם בעיניים לזה שהנוסחה החזירה.
4. ספרו כמה שורות קיבלו N/A#. אפשר לעשות את זה עם =COUNTIF(C2:C120,"#N/A"), או פשוט עם סינון על העמודה.
5. אם יש הרבה שגיאות, חזרו לרשימת הסיבות שלמעלה. אם יש מעט, בדקו כל אחת מהן בנפרד, לפעמים פשוט חסר אדם אחד ברשימה של המזכירות.

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

טיפים שחוסכים כאב ראש

• לפני שמתחילים, בדקו שבעמודת החיפוש אין ערכים כפולים. vlookup תחזיר תמיד את ההתאמה הראשונה בלבד, ותתעלם מהשאר בשקט.
• אם הטבלה השנייה תגדל עם הזמן, הפכו אותה ל"טבלה" מעוצבת (בלשונית "הוספה" → "טבלה"). אז אפשר להפנות לשם הטבלה, והטווח יתרחב לבד.
• כשהנתונים מגיעים מ-PDF, נקו אותם לפני החיפוש. העתקה מ-PDF מביאה איתה רווחים ותווים נסתרים, וזה מקור קלאסי ל-N/A#. כתבנו על זה במדריך המרת PDF לוורד.
• אחרי שהכול עובד, ואם אין צורך שהעמודה תתעדכן, אפשר להעתיק אותה ולהדביק "ערכים בלבד". ככה הקובץ קל יותר, ומחיקה של הגיליון השני לא תשבור כלום.

שאלות נפוצות

אפשר לחפש לפי שני תנאים, למשל שם פרטי ושם משפחה?

לא ישירות. הפתרון הפשוט הוא להוסיף בשתי הטבלאות עמודת עזר שמחברת את שני הערכים, למשל =A2&" "&B2, ולחפש לפיה.

אפשר למשוך נתונים מקובץ אחר, לא רק מגיליון אחר?

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

מה קורה אם אמחק את הגיליון שממנו vlookup מושכת?

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

הנוסחה איטית מאוד בקובץ גדול. למה?

טווח שמכסה עמודות שלמות, כמו A:C במקום A2:C5000, מכריח את התוכנה לעבור על מיליוני תאים ריקים. צמצמו את הטווח, או השתמשו בטבלה מעוצבת. כדאי לבדוק גם אם המחשב עצמו איטי בכל התוכנות, ולא רק בקובץ הזה.