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 הארגומנט .
לדוגמה, אם 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 כעמודה הראשונה, כעמודה השניה וכן הלאה.
  4. באופן אופציונלי, באפשרותך לציין ערך TRUE אם אתה מעוניין בהתאמה משוערת או ערך FALSE אם אתה מעוניין בהתאמה מדויקת של הערך המוחזר. אם לא תציין דבר, ערך ברירת המחדל יהיה תמיד TRUE או התאמה משוערת.

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

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

דוגמאות

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

דוגמה 1

=VLOOKUP (B3,B2:E7,2,FALSE) הפונקציה VLOOKUP מחפש את פונטנה בעמודה הראשונה (עמודה 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, ומחזירה אתFontan.

דוגמה 3

=IF(VLOOKUP(103,A1:E7,2,FALSE)=Souse,Located,Not found) IF בודק אם הפונקציה VLOOKUP מחזירה את Sousa כשם המשפחה של העובדים המתאם ל- 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), ומחזירה את 04/03/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 מחזירה את הערך Employee not found. ISNA מבטיח שאם הפונקציה VLOOKUP מחזירה #N/A, השגיאה מוחלפת על-ידי העובד לא נמצא, במקום #N/A. בדוגמה זו, הערך המוחזר הוא ברק, שהוא שם המשפחה התואם ל- 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 גדול ממספר העמודות ב- table-array, תקבל את #REF! ערך שגיאה‎.
לקבלת מידע נוסף אודות פתרון #REF! ב- VLOOKUP, ראה כיצד לתקן שגיאת #REF!.
השגיאה ‎#VALUE!‎ בתא אם table_array קטן מ- 1, תקבל את #VALUE! ערך שגיאה‎.
לקבלת מידע נוסף אודות פתרון #VALUE! ב- VLOOKUP, ראה כיצד לתקן שגיאת #VALUE! בפונקציה VLOOKUP.
‎#NAME?‎ בתא ה"#NAME"? ערך שגיאה פירושו בדרך כלל שחסרים בנוסחה מרכאות. כדי לחפש שם של אדם, הקפד להשתמש במרכאות סביב השם בנוסחה. לדוגמה, הזן את השם כ- "Fontan" ב- =VLOOKUP("Fon",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 Tech Community או לקבל תמיכה בקהילות.