פונקציית VLOOKUP ב-Excel

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

• הסבר שלב אחר שלב על הפרמטרים
• תחביר ופרמטרים
• דוגמאות ל-VLOOKUP
  התאמה מדויקת לעומת התאמה משוערת | VLOOKUP עם מספר תנאים
• שגיאות נפוצות ב-VLOOKUP ופתרונות
  #N/A שגיאה | #VALUE שגיאה | #REF שגיאה | ערך שגוי

דוגמאות VLOOKUP .xlsx שגיאות VLOOKUP .xlsx

סרטונים קשורים

הסבר שלב אחר שלב על הפרמטרים

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

שלב1: התחלת פונקציית VLOOKUP

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

=VLOOKUP(

שלב2: ציין את ערך החיפוש

ראשית, ציין את ערך החיפוש (הערך שאותו אתה מחפש) בפונקציית VLOOKUP. כאן אני מפנה לתא G6 שמכיל את מספר המזהה1005.

=VLOOKUP(G6

שלב3: ציין את טווח הטבלה

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

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

שלב4: ציין את העמודה שממנה ברצונך להחזיר ערך

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

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

=VLOOKUP(G6,B6:E12,4

שלב5: חפש התאמה משוערת או התאמה מדויקת

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

• כדי למצוא התאמה מדויקת, עליך להשתמש ב-לא (FALSE) כפרמטר האחרון.
• כדי למצוא התאמה משוערת, השתמש ב-כן (TRUE) כפרמטר האחרון, או פשוט השאר אותו ריק.

בדוגמה זו, אני משתמש ב-לא (FALSE) עבור התאמה מדויקת. כעת הנוסחה נראית כך:

=VLOOKUP(G6,B6:E12,4,FALSE

לחץ על מקש Enter לקבלת התוצאה

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

תחביר ופרמטרים

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

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

דוגמאות

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

דוגמה1: התאמה מדויקת לעומת התאמה משוערת ב-VLOOKUP

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

התאמה מדויקת ב-VLOOKUP

בדוגמה זו, אני עומד למצוא את השמות התואמים על פי הציונים המופיעים בטווח E6:E8, לכן אני מזין את הנוסחה הבאה בתא F6 וגורר את ידית המילוי האוטומטי עד F8. בנוסחה זו, הפרמטר האחרון מוגדר כ-לא (FALSE) כדי לבצע חיפוש התאמה מדויקת.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

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

התאמה משוערת ב-VLOOKUP

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

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

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

דוגמה2: שימוש ב-VLOOKUP עם מספר קריטריונים

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

שלב1: הוסף עמודת עזר לאיחוד הערכים מעמודות החיפוש

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

1. הוסף עמודת עזר משמאל לטווח הנתונים שלך ותן לה כותרת. ראה צילום מסך:
   
   
2. בעמודת העזר הזו, בחר את התא הראשון מתחת לכותרת, הזן את הנוסחה הבאה ב- שורת נוסחאות, ולחץ על Enter.
   

   =C6&" "&D6

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

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

שלב2: החל את פונקציית VLOOKUP עם הקריטריונים הנתונים

בחר תא שבו תרצה להציג את התוצאה (כאן אני בוחר I7), הזן את הנוסחה הבאה בשורת הנוסחאות, ולאחר מכן לחץ על Enter.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

תוצאה

שגיאות נפוצות ב-VLOOKUP ופתרונות

חלק זה מציג את השגיאות הנפוצות שתיתקל בהן בעת השימוש ב-VLOOKUP ומספק פתרונות לתיקונן.

#N/A שגיאה מוחזרת

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

סיבה1: ערך החיפוש אינו בעמודה הראשונה של Table_array

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

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

פתרונות

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

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

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• השתמש בפונקציית XLOOKUP (זמין ב-Excel365, Excel2021 וגרסאות מאוחרות יותר)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

סיבה2: ערך החיפוש לא נמצא בעמודת החיפוש (התאמה מדויקת)

אחת הסיבות הנפוצות לכך ש-VLOOKUP מחזירה #N/A היא שהערך שאתה מחפש לא נמצא.

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

פתרונות

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

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

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

סיבה3: ערך החיפוש קטן מהערך הקטן ביותר בעמודת החיפוש (התאמה משוערת)

כפי שמוצג בצילום המסך למטה, אתה מבצע חיפוש התאמה משוערת. הערך שאתה מחפש (מספר מזהה1001 במקרה זה) קטן מהערך הקטן ביותר1002 בעמודת החיפוש, ולכן VLOOKUP מחזיר שגיאת #N/A.

פתרונות

להלן שני פתרונות עבורך.

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

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

סיבה4: מספרים מעוצבים כטקסט

כפי שניתן לראות בצילום המסך למטה, תוצאת השגיאה #N/A בדוגמה זו נובעת מחוסר התאמה בין סוגי הנתונים בין תא החיפוש (G6) לעמודת החיפוש (B6:B12) בטבלה המקורית. כאן הערך ב-G6 הוא מספר, והערכים בטווח B6:B12 הם מספרים המעוצבים כטקסט.

פתרונות

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

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

סיבה5: Table_array אינו קבוע בעת גרירת נוסחת VLOOKUP לתאים אחרים

כפי שמוצג בצילום המסך למטה, ישנם שני ערכי חיפוש ב-E6 וב-E7. לאחר קבלת התוצאה הראשונה ב-F6, גרור את נוסחת VLOOKUP מתא F6 ל-F7, מתקבלת תוצאת שגיאה #N/A. הסיבה לכך היא שהפניות התאים (B6:C12) הן יחסיות כברירת מחדל, ומשתנות ככל שמתקדמים בשורות. טווח הטבלה הוזז ל-B7:C13, שכבר אינו מכיל את ציון החיפוש73.

פתרון

עליך לנעול את טווח הטבלה כדי לשמור עליו קבוע על ידי הוספת סימן $ לפני השורות והעמודות בהפניות התאים. למידע נוסף על הפניה מוחלטת ב-Excel, עיין במדריך זה: Excel absolute reference (how to make and use).

#VALUE שגיאה מוחזרת

התנאים הבאים עלולים לגרום ל-VLOOKUP להחזיר תוצאת שגיאה #VALUE.

סיבה1: ערך החיפוש עולה על255 תווים

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

פתרונות

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

• INDEX ו-MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• פונקציית XLOOKUP (זמין ב-Excel365, Excel2021 וגרסאות מאוחרות יותר):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

סיבה2: הפרמטר col_index קטן מ-1

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

אם תזין מספר עמודה קטן מ-1 (כלומר, אפס או שלילי), VLOOKUP לא יוכל לאתר את העמודה בטווח הטבלה.

פתרון

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

#REF שגיאה מוחזרת

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

סיבה: הפרמטר col_index גדול ממספר העמודות

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

פתרונות

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

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  הערה: בנוסחה לעיל, הפונקציה MATCH("Email",B5:E5,0) משמשת לקבלת מספר העמודה של עמודת "Email" בטווח הנתונים B6:E12. כאן התוצאה היא4, והיא משמשת כ-col_index בפונקציית VLOOKUP.

ערך שגוי מוחזר

אם אתה מגלה ש-VLOOKUP לא מחזירה את התוצאה הנכונה, ייתכן שזה נגרם מהסיבות הבאות

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

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

פתרון

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

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

סיבה2: עמודה הוספה או הוסרה

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

פתרונות

להלן שני פתרונות עבורך.

• ניתן לשנות ידנית את מספר העמודה כך שיתאים למיקום עמודת ההחזרה. כאן יש לשנות את הנוסחה ל:

  =VLOOKUP(H6,B6:F12,5,FALSE)

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

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

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

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

מאמרים קשורים