VLOOKUP (הפונקציה VLOOKUP)

חל על
Excel של Microsoft 365 Excel של Microsoft 365 עבור Mac Excel 2024 ‏Excel 2024 עבור Mac Excel 2021 Excel 2021 עבור Mac Excel 2019 Excel 2016

עצה

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

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

בצורתה הפשוטה ביותר, הפונקציה VLOOKUP מציינת:

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

עצה

  • סוד השימוש ב- VLOOKUP הוא ארגון הנתונים כך שהערך שאתה מחפש (Fruit) נמצא מימין לערך המוחזר (Amount) שברצונך למצוא.
  • אם אתה מנוי של Microsoft Copilot, Copilot יכול להקל עליך עוד יותר להכניס פונקציות VLookup או XLookup ולהשתמש בהן. ראה 'קבלת תובנות נתונים באמצעות Copilot ב- Excel'.

פרטים טכניים

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

תחביר

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])‎

לדוגמה:

  • =VLOOKUP(A2,A10:C20,2,TRUE)
  • ‎=VLOOKUP("Fontana",B2:E7,2,FALSE)‎
  • =VLOOKUP(A2,'Client Details'! A:F,3,FALSE)
שם ארגומנט תיאור
lookup_value (חובה) הערך שברצונך לחפש לפיו. הערך שברצונך לחפש חייב להימצא בעמודה הראשונה של טווח התאים שציינת בארגומנט table_array .
לדוגמה, אם מערך הטבלה כולל את התאים B2:D7, lookup_value שלך חייב להימצא בעמודה B.
Lookup_value יכול להיות ערך או הפניה לתא.
Table_array (חובה) טווח התאים שבו VLOOKUP יחפש את lookup_value ואת הערך המוחזר. באפשרותך להשתמש בטווח בעל שם או בטבלה, ובאפשרותך להשתמש בשמות בארגומנט במקום בהפניות לתאים.
העמודה הראשונה בטווח התאים חייבת להכיל את lookup_value. בנוסף, על טווח התאים להכיל את הערך המוחזר שברצונך למצוא.
col_index_num (חובה) מספר העמודה (החל מ- 1 עבור העמודה הימנית ביותר ב table_array) המכילה את הערך המוחזר.
range_lookup (אופציונלי) ערך לוגי המציין אם ברצונך שהפונקציה VLOOKUP תאתר התאמה משוערת או התאמה מדויקת:
  • התאמה משוערת - 1/TRUE להנחה שהעמודה הראשונה בטבלה מסודרת בסדר מספרי או אלפביתי ולחיפוש הערך הקרוב ביותר. זוהי שיטת ברירת המחדל אם אינך מציין שיטה. לדוגמה, =VLOOKUP(90,A1:B100,2,TRUE).
  • התאמה מדויקת - 0/FALSE מחפשת את הערך המדויק בעמודה הראשונה. לדוגמה, =VLOOKUP("Smith",A1:B100,2,FALSE).

כיצד להתחיל בעבודה

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

  1. הערך שברצונך לחפש, הנקרא גם ערך בדיקת המידע.
  2. הטווח שבו ממוקם ערך בדיקת המידע. זכור כי ערך בדיקת המידע חייב להימצא תמיד בעמודה הראשונה בטווח על מנת שהפונקציה VLOOKUP תפעל נכון. לדוגמה, אם ערך בדיקת המידע שלך נמצאת בתא C2, הטווח שלך אמור להתחיל ב- C.
  3. מספר העמודה בטווח המכיל את הערך המוחזר. לדוגמה, אם תציין את B2:D11 כטווח, עליך להתייחס ל- B כעמודה הראשונה, ל- C כעמודה השניה וכן הלאה.
  4. באופן אופציונלי, באפשרותך לציין ערך TRUE אם אתה מעוניין בהתאמה משוערת או ערך FALSE אם אתה מעוניין בהתאמה מדויקת של הערך המוחזר. אם לא תציין דבר, ערך ברירת המחדל יהיה תמיד TRUE או התאמה משוערת.

כעת חבר את כל מה שצוין לעיל באופן הבא:

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

דוגמאות

להלן כמה דוגמאות של VLOOKUP:

דוגמה 1

=VLOOKUP (B3,B2:E7,2,FALSE) הפונקציה VLOOKUP מחפשת את Fontana בעמודה הראשונה (עמודה B) table_array- B2:E7, ומחזירה את Olivier מהעמודה השניה (עמודה C) של table_array. הפונקציה FALSE מחזירה התאמה מדויקת.

דוגמה 2

=VLOOKUP (102,A2:C7,2,FALSE) הפונקציה VLOOKUP מחפשת התאמה מדויקת (FALSE) של שם המשפחה 102 (lookup_value) בעמודה השנייה (עמודה B) בטווח A2:C7, ומחזירה את Fontana.

דוגמה 3

=IF(VLOOKUP(103,A1:E7,2,FALSE)=Souse,Located,Not found) הפונקציה IF בודקת אם הפונקציה VLOOKUP מחזירה את סוזה כשם המשפחה של העובד התואם ל- 103 (lookup_value) ב- A1:E7 (table_array). מכיוון ששם המשפחה המתאים ל- 103 הוא Leal, תנאי IF הוא False, ומוצג התנאי 'לא נמצא'.

דוגמה 4

=INT(YEARFRAC(DATE(2014,6,30),VLOOKUP(105,A2:E7,5,FLASE),1)) הפונקציה VLOOKUP מחפשת את תאריך הלידה של העובד התואם ל- 109 (lookup_value) בטווח A2:E7 (table_array) ומחזירה את התאריך 03/04/1955. לאחר מכן, YEARFRAC מחסירה את תאריך הלידה הזה מ- 2014/6/30 ומחזירה ערך, אשר מומר על-ידי INY למספר השלם 59.

דוגמה 5

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,Employee not found,VLOOKUP(105,A2:E7,2,FALSE)) הפונקציה IF בודקת אם הפונקציה VLOOKUP מחזירה ערך עבור שם משפחה מעמודה B עבור 105 (lookup_value). אם הפונקציה VLOOKUP מוצאת שם משפחה, הפונקציה IF תציג את שם המשפחה. אחרת, הפונקציה IF תחזיר את השם 'עובד לא נמצא'. ISNA מוודאת שאם הפונקציה VLOOKUP מחזירה #N/A, השגיאה מוחלפת על-ידי 'עובד לא נמצא' במקום #N/A. בדוגמה זו, ערך ההחזרה הוא Burke, שהוא שם המשפחה התואם ל- 105.

בעיות נפוצות

בעיה מה השתבש
הערך המוחזר שגוי אם range_lookup הוא TRUE או אינו מצוין, העמודה הראשונה חייבת להיות ממוינת בסדר אלפביתי או מספרי. אם העמודה הראשונה אינה ממוינת, הערך המוחזר עלול להיות בלתי צפוי. מיין את העמודה הראשונה או השתמש ב- FALSE לקבלת התאמה מדויקת.
‎ #N/A בתא
  • אם range_lookup הוא TRUE, אם הערך ב lookup_value קטן מהערך הקטן ביותר בעמודה הראשונה של table_array, תקבל את ערך השגיאה #N/A.
  • אם range_lookup הוא FALSE, ערך השגיאה #N/A מציין שהמספר המדויק לא נמצא.
לקבלת מידע נוסף אודות פתרון שגיאות ‎#N/A בפונקציה VLOOKUP, ראה כיצד לתקן שגיאת ‎#N/A בפונקציה VLOOKUP.
שגיאת ‎#REF!‎ בתא אם col_index_num גדול ממספר העמודות במערך טבלה, תקבל את #REF! ערך שגיאה‎.
לקבלת מידע נוסף על פתרון #REF! שגיאות ב- VLOOKUP, ראה כיצד לתקן שגיאת #REF!.
השגיאה ‎#VALUE!‎ בתא אם table_array קטן מ- 1, תקבל את #VALUE! ערך שגיאה‎.
לקבלת מידע נוסף אודות פתרון #VALUE! שגיאות ב- VLOOKUP, ראה כיצד לתקן שגיאת #VALUE! בפונקציה VLOOKUP.
‎#NAME?‎ בתא #NAME? לרוב, ערך השגיאה פירושו שחסרות בנוסחה מרכאות. כדי לחפש שם של אדם כלשהו, הקפד להשתמש במרכאות מסביב לשם בנוסחה. לדוגמה, הזן את השם כ - "Fontana" ב- =VLOOKUP("Fontana",B2:E7,2,FALSE).
לקבלת מידע נוסף, ראה תיקון שגיאת ‎#NAME!‎‏.
שגיאת ‎#SPILL!‎ בתא שגיאת #SPILL! מסוימת זו פירושה בדרך כלל שהנוסחה שלך מסתמכת על חיתוך משתמע עבור ערך בדיקת המידע ומשתמשת בעמודה שלמה כהפניה. לדוגמה, =VLOOKUP( A:A,A:C,2,FALSE). באפשרותך לפתור את הבעיה על-ידי עיגון הפניית בדיקת המידע באמצעות האופרטור @ באופן הבא: =VLOOKUP(@A:A,A:C,2,FALSE). לחלופין, באפשרותך להשתמש בשיטת VLOOKUP המסורתית ולהפנות לתא בודד במקום לעמודה שלמה: =VLOOKUP(A2,A:C,2,FALSE).

שיטות עבודה מומלצות

לבצע את הפעולה מדוע
השתמש בהפניות מוחלטות עבור range_lookup הפניות מוחלטות מאפשרות לך למלא כלפי מטה את הנוסחה כך שהיא תבדוק תמיד את אותו טווח בדיקת מידע.
למד כיצד להשתמש בהפניות מוחלטות לתאים.
אל תאחסן ערכי מספר או תאריך כטקסט. בעת חיפוש ערכי מספר או תאריך, ודא שהנתונים בעמודה הראשונה של table_array אינם מאוחסנים כערכי טקסט. במקרה כזה, הפונקציה VLOOKUP עלולה להחזיר ערך שגוי או לא צפוי.
מיין את העמודה הראשונה מיין את העמודה הראשונה של table_array לפני השימוש בפונקציה VLOOKUP כאשר range_lookup הוא TRUE.
השתמש בתווים כלליים אם range_lookup הוא FALSE ו - lookup_value הוא טקסט, באפשרותך להשתמש בתווים הכלליים - סימן השאלה (?) והכוכבית (*)- lookup_value. סימן שאלה מתאים לכל תו בודד. כוכבית מתאימה לרצף כלשהו של תווים. אם ברצונך למצוא סימן שאלה או כוכבית בפועל, הקלד ~ לפני התו.
לדוגמה, =VLOOKUP("Fontan?",B2:E7,2,FALSE)‎ מחפש את כל המופעים של Fontana, כאשר האות האחרונה במילה עשויה להיות שונה.
ודא שהנתונים שלך לא מכילים תווים שגויים. בעת חיפוש ערכי טקסט בעמודה הראשונה, ודא שהנתונים בעמודה הראשונה לא מכילים רווחים מובילים, רווחים נגררים, שימוש לא עקבי בגרשיים רגילים (' או ") ובגרשיים מסולסלים (' או ") או תווים שאינם מודפסים. במקרים אלה, הפונקציה VLOOKUP עלולה להחזיר ערך בלתי צפוי.
כדי לקבל תוצאות מדויקות, נסה להשתמש בפונקציה CLEAN או בפונקציה TRIM כדי להסיר רווחים נגררים לאחר ערכי טבלה בתא.

זקוק לעזרה נוספת?

תמיד תוכל לשאול מומחה בקהילה הטכנולוגית של Excel או לקבל תמיכה בקהילות.