11 טכניקות VLOOKUP שיהפכו אתכם לתותחי VLOOKUP

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

שימו לב ! למעבר לחלק ב’ של המאמר - החל מטכניקה 7 - לחצו כאן

למקומות, היכון, צא !

אם אתם מחפשים לשדרג את ניתוח הנתונים והדו"חות שלכם באופן מקצועי ?
אני ממליץ בחום לשקול את השירות שלנו של תותח Excel.
המומחיות שלנו באקסל תורמת להגשמת היעדים ולפתרון של בעיות מורכבות בצורה יעילה ומהירה.
עם מומחה Excel, אתם יכולים להיות בטוחים שתהליך העבודה שלכם יתנהל בצורה מקצועית.
ליצירת קשר לחצו כאן.

המאמר מיועד למי שכבר מכיר vlookup ועובד איתה ביום-יום.

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

לטובת אלו שמכירים vlookup  אבל קצת שכחו, אני אציג 2 דוגמאות בסיסיות, רק בשביל ליישר קו, ומייד אחרי כן, אציג את כל הטכניקות המתקדמות (ולכל מי שסופר, כן, זה “רק” 5 טכניקות מתקדמות 😊, כי  ה-2 הראשונות בסיסיות למדי.)

לכן, אם אתם כבר מכירים vlookup  ועובדים איתו ביום יום, פשוט תדלגו על 2 הטכניקות הראשונות, הם לא בשבילכם.

ועוד הערה אחת של תודה : את ההשראה המקורית קיבלתי מהמאמר הזה של Summit Bansal.

אם אתם לא מכירים בכלל vlookup - אני ממליץ לעצור, ולצפות בסרטון שלי על vlookup מתוך קורס אקסל למתקדמים.

טכניקת VLOOKUP מספר 1 - רענון קצר על הנוסחה

בדוגמא הראשונה אני משתמש בטבלה שמכילה את הטמפרטורות הצפויות בכמה ערים לאורך כמה ימים.

טכניקות VLOOKUP - הטבלה להדגמה

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

=VLOOKUP("קרית שמונה",A4:F12,3,FALSE)

כמו שאפשר לראות הנוסחה מכילה מספר פרמטרים:

הפרמטר הראשון של נוסחת vlookup הוא שם העיר שאותה אנחנו מחפשים (ואם נהיה מעט פחות ספציפיים, אז הפרמטר הראשון צריך להיות “מה מחפשים”, כלומר הערך שלפיו אנחנו מחפשים).

הפרמטר השני של נוסחת vlookup - הוא הטווח שמכיל את הטבלה.

הפרמטר השלישי של נוסחת vlookup  - הוא מספר העמודה בטבלה שמכילה את הטמפרטורה הרצויה.

והפרמטר האחרון של נוסחת vlookup  - אומר האם לחפש “מדויק” או “בקירוב”. במקרה שלנו בגלל שאנחנו מחפשים טמפרטורה ספציפית אז נרשום false  כדי לחפש מדויק. בהמשך אראה דוגמאות שבהם רלוונטי להשתמש בחיפוש בקירוב, ובהם כמובן נרשום TRUE.

=VLOOKUP(חיפוש מדויק או בערך ,מספר עמודה להחזרת ערך , תחום הטבלה , מה מחפשים )

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

טכניקת VLOOKUP מספר 2 - ערך מתוך תא

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

בדוגמה הקודמת, הערך הקבוע היה “קריית שמונה”. אז כעת במקום ערך זה, נרשום כתובת של תא.

בתוך התא, נוכל לרשום שם של עיר (כלומר, ערך לחיפוש) לפי הצורך באותו רגע.

טכניקת VLOOKUP מספר 2

רוצה ליצור דוחות ותרשימים מתקדמים בעזרת Excel?
שירות של מומחה Excel יכול להציע לך פתרונות מותאמים לצרכים שלך.
צור קשר עכשיו

טכניקת VLOOKUP מספר 3 - לעשות VLOOKUP דו מימדי

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

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

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

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

נוסחת MATCH תמצא את מספר העמודה לפי הערך שנשים שאותו צריך לחפש בין השמות של העמודות.

למשל, אם שמות העמודות הם : ראשון, שני, שלישי. ואנחנו מחפשים את המילה “שלישי”, אז נוסחת MATCH תחזיר לנו את המספר 3.

הנה דוגמא:

טכניקת VLOOKUP מספר 4 - VLOOKUP דו מימדית

רוצה להפוך את עבודתך ב-Excel לפחות מסובכת ויותר יעילה?
מומחה Excel יכול להפוך תהליכים לאוטומטים ולבנות עבורך אקסלים יעילים.
צור קשר עכשיו

טכניקת VLOOKUP מספר 4 - לאפשר לבחור את הנתונים מתוך רשימה במקום להקליד אותם

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

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

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

ראשית נלחץ על התא שבו יושב הערך הראשון, בדוגמה שלנו זה תא D17

בתפריט נתונים לחץ על אימות נתונים ואז במסך שיפתח נבחר באפשרות מתוך רשימה,

וגם נגדיר את הרשימה עבור התא שלנו

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

נחזור על אותם שלבים בשביל התא השני.

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

טכניקת VLOOKUP מספר 5 - VLOOKUP ששואבת את הערך שלה כל פעם מטבלה אחרת ( כלומר VLOOKUP מ-2 טבלאות או יותר)

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

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

איך עושים זאת ?

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

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

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

דוגמא קטנה על פונקצית choose-

נניח והרשימה שלנו היא האותיות : “A”, “B” , “C”

ואנחנו מבקשים את הערך במיקום 2, אז נקבל את האות “B”.

כך זה נראה :

=CHOOSE(2,"A","B","C")

והנה התוצאה בגיליון אקסל, ניתן לראות שאקסל שלף את הערך ה-2 כיוון שזה מה שביקשנו ממנו בפונקציה.

טכניקת VLOOKUP - הסבר על נוסחת CHOOSE

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

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

כל טבלה תתאר שבוע אחר של מזג אויר : טבלה אחת תתאר את השבוע הנוכחי, והטבלה השנייה תתאר את השבוע הבא.

להלן הנוסחה הבסיסית, שימו לב שבנוסחת choose  מופיעים 2 טווחי תאים, ואני בחרתי בטווח מספר 2 ולכן הטמפרטורה נלקחת מהטבלה השנייה.

מייד לאחר מכן, נשכלל מעט את הנוסחה.

טכניקת VLOOKUP יחד עם CHOOSE

אם אתם מחפשים לשדרג את ניתוח הנתונים והדו"חות שלכם באופן מקצועי ?
אני ממליץ בחום לשקול את השירות שלנו של תותח Excel.
המומחיות שלנו באקסל תורמת להגשמת היעדים ולפתרון של בעיות מורכבות בצורה יעילה ומהירה.
עם מומחה Excel, אתם יכולים להיות בטוחים שתהליך העבודה שלכם יתנהל בצורה מקצועית.
ליצירת קשר לחצו כאן.

טכניקת VLOOKUP מספר 6 - לאפשר למשתמש לבחור את מקור המידע

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

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

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

איך נעשה זאת ?

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

אפשר לעשות את זה במספר צורות. אני אדגים באמצעות IF  בשביל הפשטות.

אך ברור שאפשר גם להשתמש ב Index  למי שמכיר.

אז כדי לאפשר לבחור את השבוע, נוסיף תא שבו תהיה נוסחת IF  - כאשר נוסחת IF תחזיר מספר 1 אם נבחר השבוע הנוכחי, ומספר 2 אם נבחר השבוע הבא .

ואז, פשוט נפנה לתא הזה בתוך נוסחת choose   במקום  לרשום את הסיפרה 2.

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

טכניקת VLOOKUP מספר 7 - מציאת המספר האחרון ברשימה עם VLOOKUP

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

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

הטריק הוא ממש פשוט, ומורכב מ-2 חלקים :

— לחפש את המספר הגבוה ביותר, למשל 999999.99

— בפרמטרים של VLOOKUP לרשום את הפרמטר TRUE, כלומר “הערך הכי קרוב” למה שמחפשים, וכך הוא ימצא את תמיד את הערך האחרון.

דוגמא בפועל:

טכניקת VLOOKUP - מציאת המספר האחרון ברשימה

רוצה ליצור דוחות ותרשימים מתקדמים בעזרת Excel?
שירות של מומחה Excel יכול להציע לך פתרונות מותאמים לצרכים שלך.
צור קשר עכשיו

טכניקת VLOOKUP מספר 8 - חיפוש באמצעות תווי התאמה רחבה

או איך למצוא שם לפי חלק מהטקסט באמצעות VLOOKUP

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

באקסל, יש אפשרות להשתמש בתווים (=סימנים) להתאמה רחבה, שזה התרגום הכי קרוב שמצאתי למונח WILCARDS.

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

כוכבית – משמעותה כל צירוף אותיות, בכל אורך

סימן שאלה – משמעותו, כל אות סימן, באורך של תו אחד בלבד.

טילדה, משמשת בתור “תו בריחה” ונדבר על זה בהזדמנות אחרת.

אז למשל הביטוי משה*, אומר בעצם : המילה משה, וכל מה שבא אחריה. למשל “משה יצחקוב” יחשב בתור טקסט שמתאים לתבנית משה.*

או הביטוי משה, אומר למעשה, המילה משה, וכל מה שבא לפניה או לאחריה. כך למשל, הביטוי “חברת משה רבינוביץ לבניה” נחשב מתאים לתבנית משה.

אז אין משתמשים בזה ב-Vlookup ?

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

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

=VLOOKUP("*"&$J$4&"*",$C$3:$C$7,1,FALSE)

שימוש ב-VLOOKUP יחד עם תווי התאמה רחבה

רוצה להפוך את עבודתך ב-Excel לפחות מסובכת ויותר יעילה?
מומחה Excel יכול להפוך תהליכים לאוטומטים ולבנות עבורך אקסלים יעילים.
צור קשר עכשיו

טכניקת VLOOKUP מספר 9 - למה VLOOKUP לא עובד לי ?

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

VLOOKUP לא עובדת

כמו שאפשר לראות בתמונה, למרות שהשם “שיבולת” מופיע ברשימת השמות, בכל זאת, נוסחת VLOOKUP לא מוצאת אותו, ומחזירה #N/A (כלומר “לא זמין”(.

למה ?

ובכן, אם נסתכל לעומק, נראה שיש רווח אחרי המילה שיבולת

VLOOKUP לא עובדת כי יש רווח

וזו הסיבה ש-VLOOKUP לא מצא אותה.

הפתרון, הוא די פשוט – להוסיף נוסחה שמנקה את כל הרווחים מהצדדים.

הנוסחה TRIM, עושה בדיוק את זה.

הערה חשובה לגבי הטריק הזה : שימו לב! זוהי נוסחת מערך , וכאשר מסיימים לכתוב את הנוסחה, יש ללחוץ Ctrl+Shift+Enter !

אם תלחצו פשוט ENTER – זה פשוט לא יעבוד.

**=VLOOKUP("****שיבולת",TRIM($C$4:$C$8),1,FALSE)**

חשוב להבין !

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

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

טכניקת VLOOKUP מספר 10 - איך לעשות VLOOKUP לפי כמה עמודות  (ולא לפי עמודה בודדת )

נוסחת VLOOKUP בצורה הבסיסית שלה, מתבססת על עמודה אחת, למשל “תמצא לי שם של מישהו לפי תעודת זהות (=עמודה אחת)”

או למשל “תמצא לי גובה של מישהו, לפי השם שלו (=עמודה אחת)”.

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

למשל : “תמצא לי פריט במחסן שנמצא בעמודה מספר 2, ובשורה מספר 3  (= 2 עמודות באקסל)”

איך עושים זאת ?

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

ואז בנוסחת VLOOKUP, נוכל להתבסס על המפתח בעמודת העזר.

לצורך הדוגמא – הטבלה שלנו נראית כך:

דוגמא לטבלה עבור VLOOKUP מכמה עמודות יחד

כעת ניצור עמודת עזר, שמכילה גם את העמודה וגם את השורה :

חיבור של כמה עמודות יחד כדי ליצור מפתח עבור VLOOKUP

חשוב לשים לב, שאת עמודת העזר שמתי בהתחלה הטבלה , מסיבה פשוטה מאוד : VLOOKUP תמיד מתבסס על התחלת הטבלה בתור “עמודת המפתח”.

ועכשיו, נרשום נוסחת VLOOKUP שמתבססת על עמודה ושורה יחד :

נוסחת VLOOKUP לפי כמה עמודות יחד

ולמי שרוצה לראות את הטכניקה הזו בשימוש דו-מימדי, יש סרטון ישן שהקלטתי על זה פעם במאמר הזה https://excel.kova.co.il/173/

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

טכניקת VLOOKUP מספר 11 - הודעת שגיאה מסוגננת עבור VLOOKUP

טיפול במצבים שבהם VLOOKUP לא מצליחה למצוא את המידע

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

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

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

להלן דוגמא פשוטה :

=IFERROR(VLOOKUP("ג5",טבלה911[#All],2,FALSE),"אופס...לא נמצא")

הודעת שגיאה מסוגננת עבור VLOOKUP

סיכום VLOOKUP

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

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