20+ דוגמאות ל-VLOOKUP למתחילים ולמתקדמים ב-Excel

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

מבוא לפונקציית VLOOKUP – תחביר וארגומנטים

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

תחביר פונקציית VLOOKUP:

ארגומנטים:

הערות לפונקציה:

דוגמאות בסיסיות ל-VLOOKUP

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

2.1 VLOOKUP בהתאמה מדויקת ובהתאמה משוערת

 2.1.1 ביצוע VLOOKUP בהתאמה מדויקת

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

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

העתיקו והדביקו את הנוסחה הבאה לתא ריק (כאן, בחרתי G2), ולחצו על מקש "Enter" לקבלת התוצאה:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

איך פועלת נוסחת VLOOKUP?

ראשית, היא מחפשת את ה-ID - C1005 בעמודה השמאלית ביותר של הטבלה. היא עוברת מלמעלה למטה ומוצאת את הערך בתא A6.

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

כך תקבלו את התוצאה כפי שמוצג בצילום המסך הבא:

 2.1.2 ביצוע VLOOKUP בהתאמה משוערת

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

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

שלב1: החל את נוסחת VLOOKUP ומלא אותה לתאים נוספים

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

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

תוצאה:

כעת תקבלו את ההתאמות המשוערות בהתבסס על הערכים הנתונים, ראו צילום מסך:

2.2 ביצוע VLOOKUP תלוי רישיות ב-Excel

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

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

שלב1: החל אחת מהנוסחאות ומלא לתאים נוספים

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

נוסחה1: לאחר הדבקת הנוסחה, לחץ על "Ctrl" + "Shift" + "Enter".

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

נוסחה2: לאחר הדבקת הנוסחה, לחץ על "Enter".

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

תוצאה:

כעת תקבלו את התוצאות הנכונות שאתם צריכים. ראו צילום מסך:

2.3 VLOOKUP לערכים מימין לשמאל ב-Excel

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

לחצו כאן למדריך שלב אחר שלב למשימה זו…

2.4 VLOOKUP לערך השני, ה-n או האחרון שנמצא ב-Excel

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

 2.4.1 VLOOKUP והחזרת הערך השני או ה-n שנמצא

נניח שיש לכם רשימת שמות בעמודה A, וקורסי ההדרכה שהם רכשו בעמודה B. כעת, אתם רוצים למצוא את קורס ההדרכה השני או ה-n שנרכש על ידי הלקוח הנתון. ראו צילום מסך:

כאן, פונקציית VLOOKUP לא תפתור זאת ישירות. אך ניתן להשתמש בפונקציית INDEX כחלופה.

שלב1: החל ומלא את הנוסחה לתאים נוספים

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

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

תוצאה:

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

 2.4.2 VLOOKUP והחזרת הערך האחרון שנמצא

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

2.5 VLOOKUP לערכים תואמים בין שני ערכים או תאריכים נתונים

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

 2.5.1 VLOOKUP לערכים תואמים בין שני ערכים או תאריכים באמצעות נוסחה

שלב1: סדרו את הנתונים והחילו את הנוסחה הבאה

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

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

תוצאה:

כעת, כל הרשומות התואמות בהתבסס על הערך הנתון יופיעו, ראו צילום מסך:

 2.5.2 VLOOKUP לערכים תואמים בין שני ערכים או תאריכים באמצעות תכונה נוחה

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

1. לחצו על "Kutools" > "חיפוש מתקדם" > "מציאת נתונים בין שני ערכים" כדי להפעיל תכונה זו.
2. לאחר מכן הגדירו את הפעולות בתיבת הדו-שיח בהתאם לנתונים שלכם.

2.6 שימוש בתווי כלל (Wildcards) להתאמות חלקיות בפונקציית VLOOKUP

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

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

שלב1: החל את הנוסחה ומלא לתאים נוספים

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

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

תוצאה:

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

2.7 VLOOKUP לערכים מגליון עבודה אחר

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

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

שלב1: החל את הנוסחה ומלא לתאים נוספים

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

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

תוצאה:

תקבלו את התוצאות התואמות לפי הצורך, ראו צילום מסך:

2.8 VLOOKUP לערכים מחוברת עבודה אחרת

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

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

שלב1: החל את הנוסחה

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

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

תוצאה:

2.9 החזרת תא ריק או טקסט מסוים במקום0 או שגיאת #N/A

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

דוגמאות מתקדמות ל-VLOOKUP

3.1 חיפוש דו-כיווני (VLOOKUP בשורה ובעמודה)

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

ב-Excel, ניתן לשלב את פונקציות VLOOKUP ו-MATCH כדי לבצע חיפוש דו-כיווני.

החילו את הנוסחה הבאה לתא ריק, ולאחר מכן לחצו על מקש "Enter" לקבלת התוצאה.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 VLOOKUP לערך תואם בהתבסס על שני קריטריונים או יותר

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

 3.2.1 VLOOKUP לערך תואם בהתבסס על שני קריטריונים או יותר עם נוסחאות

במקרה זה, פונקציות LOOKUP או MATCH ו-INDEX ב-Excel יכולות לעזור לכם לפתור זאת במהירות ובקלות.

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

שלב1: החילו אחת מהנוסחאות הבאות

נוסחה1: הזינו את הנוסחה הבאה ולחצו על "Enter".

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

נוסחה2: הזינו את הנוסחה הבאה ולחצו על "Ctrl" + "Shift" + "Enter".

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

תוצאה:

 3.2.2 VLOOKUP לערך תואם בהתבסס על שני קריטריונים או יותר עם Kutools for Excel

לעיתים קשה לזכור את הנוסחאות המורכבות שיש להחיל שוב ושוב, דבר שעלול להאט את קצב העבודה. עם זאת, "Kutools for Excel" מציע תכונה בשם "חיפוש מרובה תנאים" המאפשרת להחזיר את התוצאה התואמת בהתבסס על תנאי אחד או יותר בכמה לחיצות בלבד.

1. לחצו על "Kutools" > "חיפוש מתקדם" > "חיפוש מרובה תנאים" כדי להפעיל תכונה זו.
2. לאחר מכן הגדירו את הפעולות בתיבת הדו-שיח בהתאם לנתונים שלכם.

3.3 VLOOKUP להחזרת ערכים מרובים עם קריטריון אחד או יותר

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

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

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

 3.3.2 VLOOKUP כל הערכים התואמים בהתבסס על תנאי אחד או יותר אנכית

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

 3.3.3 VLOOKUP כל הערכים התואמים בהתבסס על תנאי אחד או יותר לתוך תא יחיד

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

3.4 VLOOKUP להחזרת שורה שלמה של תא תואם

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

שלב1: החילו את הנוסחה הבאה

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

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

תוצאה:

כעת תוכלו לראות שכל נתוני השורה הוחזרו. ראו צילום מסך:

3.5 VLOOKUP מקונן ב-Excel

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

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

הנוסחה הכללית ל-VLOOKUP מקונן היא:

שלב1: החילו ומלאו את הנוסחה הבאה

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

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

תוצאה:

כעת תקבלו את התוצאה כפי שמוצג בצילום המסך:

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

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

שלב1: החילו את הנוסחה הבאה

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

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

תוצאה:

ותקבלו את התוצאה לפי הצורך, ראו צילום מסך:

3.7 VLOOKUP וסכימת כל הערכים התואמים בשורות או עמודות

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

 3.7.1 VLOOKUP וסכימת כל הערכים התואמים בשורה או במספר שורות

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

שלב1: החילו את הנוסחה הבאה

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

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

תוצאה:

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

 3.7.2 VLOOKUP וסכימת כל הערכים התואמים בעמודה או במספר עמודות

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

שלב1: החילו את הנוסחה הבאה

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

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

תוצאה:

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

 3.7.3 VLOOKUP וסכימת הערך הראשון או כל הערכים התואמים עם Kutools for Excel

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

1. לחצו על "Kutools" > "חיפוש מתקדם" > "חיפוש וסכום" כדי להפעיל תכונה זו.
2. לאחר מכן הגדירו את הפעולות בתיבת הדו-שיח בהתאם לצורך שלכם.

 3.7.4 VLOOKUP וסכימת כל הערכים התואמים גם בשורות וגם בעמודות

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

כאן ניתן להשתמש בפונקציה SUMPRODUCT כדי לבצע משימה זו.

החילו את הנוסחה הבאה לתא, ולאחר מכן לחצו על "Enter" לקבלת התוצאה, ראו צילום מסך:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 VLOOKUP למיזוג שתי טבלאות בהתבסס על עמודות מפתח

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

 3.8.1 VLOOKUP למיזוג שתי טבלאות בהתבסס על עמודת מפתח אחת

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

שלב1: החילו את הנוסחה הבאה

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

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

תוצאה:

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

 3.8.2 VLOOKUP למיזוג שתי טבלאות בהתבסס על מספר עמודות מפתח

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

הנוסחה הכללית היא:

שלב1: החילו את הנוסחה הבאה

החילו את הנוסחה הבאה לתא ריק שבו תרצו להציג את התוצאה, ולאחר מכן לחצו על "Ctrl" + "Shift" + "Enter" יחד לקבלת הערך הראשון, ראו צילום מסך:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

שלב2: מלאו את הנוסחה לתאים נוספים

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

3.9 VLOOKUP לערכים תואמים בין מספר גליונות עבודה

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

ערכי VLOOKUP תואמים שומרים על עיצוב התא

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

4.1 VLOOKUP לערך תואם ושמירת צבע התא ועיצוב הגופן

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

בצעו את השלבים הבאים כדי לחפש ולהחזיר את הערך התואם יחד עם עיצוב התא:

שלב1: העתקו את קוד1 למודול הקוד של הגיליון

1. בגליון העבודה שמכיל את הנתונים שברצונכם לבצע עליהם VLOOKUP, לחצו קליק ימני על לשונית הגיליון ובחרו "הצג קוד" מהתפריט. ראו צילום מסך:
   
2. בחלון "Microsoft Visual Basic for Applications" שנפתח, העתיקו את קוד ה-VBA שלמטה לחלון הקוד.
3. קוד VBA1: VLOOKUP לקבלת עיצוב התא יחד עם ערך החיפוש

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

שלב2: העתקו את קוד2 לחלון המודול

1. עדיין בחלון "Microsoft Visual Basic for Applications", לחצו על "הוספה" > "מודול", והעתיקו את קוד VBA2 לחלון "מודול".
2. קוד VBA2: VLOOKUP לקבלת עיצוב התא יחד עם ערך החיפוש

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

שלב3: בחרו את האפשרות עבור VBAproject

1. לאחר הכנסת הקודים לעיל, לחצו על "כלים" > "הפניות" בחלון "Microsoft Visual Basic for Applications". לאחר מכן סמנו את התיבה "Microsoft Scripting Runtime" בתיבת הדו-שיח "הפניות – VBAProject". ראו צילומי מסך:
2. לאחר מכן, לחצו על "אישור" לסגירת תיבת הדו-שיח, ושמרו וסגרו את חלון הקוד.

שלב4: הקלידו את הנוסחה לקבלת התוצאה

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

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 שמירת פורמט התאריך מערך שהוחזר ב-VLOOKUP

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

שלב1: החילו את הנוסחה הבאה

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

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

תוצאה:

כל התאריכים התואמים הוחזרו כפי שמוצג בצילום המסך:

4.3 החזרת הערת תא מ-VLOOKUP

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

שלב1: העתקו את הקוד למודול

1. החזיקו את המקשים "ALT" + "F11" לפתיחת חלון "Microsoft Visual Basic for Applications".
2. לחצו על "הוספה" > "מודול", ואז העתיקו והדביקו את הקוד הבא לחלון "מודול".
   קוד VBA: VLOOKUP והחזרת ערך תואם עם הערת תא:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. לאחר מכן שמרו וסגרו את חלון הקוד.

שלב2: הקלידו את הנוסחה לקבלת התוצאה

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

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 VLOOKUP למספרים השמורים כמחרוזת טקסט

לדוגמה, יש לי טווח נתונים שבו מספר ה-ID בטבלה המקורית הוא בפורמט מספרי ומספר ה-ID בתאי החיפוש נשמר כמחרוזת טקסט, ייתכן שתיתקלו בשגיאת #N/A בעת שימוש בפונקציית VLOOKUP הרגילה. במקרה כזה, כדי לשלוף את המידע הנכון, ניתן לעטוף את הפונקציות TEXT ו-VALUE בתוך פונקציית VLOOKUP. להלן הנוסחה להשגת זאת:

שלב1: החילו ומלאו את הנוסחה הבאה

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

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

תוצאה:

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