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

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

בעיה: ערך בדיקת המידע אינו נמצא בארגומנט עמודה בארגומנט table_array.

אילוץ אחד של VLOOKUP הוא שהוא יכול לחפש רק ערכים עמודה ביותר במערך הטבלה. אם ערך בדיקת המידע שלך אינו נמצא עמודה של המערך, תראה את #N/A.

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

#NA שגיאה ב- VLOOKUP: ערך בדיקת המידע אינו נמצא עמודה של מערך הטבלה

תוצאות #N/A מאחר שערך בדיקת המידע "Kale" מופיע ב- עמודה (Produce) של הארגומנט table_array A2:C10. במקרה זה, Excel מחפש אותו ב- עמודה A, ולא עמודה B.

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

שקול להשתמש במקום זאת ב- INDEX/MATCH

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

קיימות כמה יתרונות בשימוש ב- INDEX/MATCH במקום ב- VLOOKUP:

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

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

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

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

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

תחביר

כדי לבנות תחביר עבור INDEX/MATCH, עליך להשתמש בארגומנט array/reference מהפונקציה INDEX ולקן את התחביר MATCH בתוכה. פעולה זו ת לקחת את הטופס:

=INDEX(array או reference, MATCH(lookup_value,lookup_array,[match_type])

בוא נשתמש ב- INDEX/MATCH כדי להחליף את VLOOKUP מהדוגמה לעיל. התחביר יראה כך:

=INDEX(C2:C10,MATCH(B13,B2:B10,0))

באנגלית פשוטה פירוש הדבר:

=INDEX(החזר ערך מ- C2:C10, אשר יהיה MATCH(Kale, שהוא במקום כלשהו במערך B2:B10, שבו הערך ההחזרה הוא הערך הראשון התואם ל- Kale))

הפונקציות INDEX ו- MATCH יכולות לשמש כמחליף ל- VLOOKUP

הנוסחה מחפש את הערך הראשון ב- C2:C10 התואם ל- Kale (ב- B7) ומחזירה את הערך ב- C7 (100), שהוא הערך הראשון התואם ל- Kale.

בעיה: לא נמצאה התאמה מדויקת

כאשר הארגומנט range_lookup הוא FALSE – ו- VLOOKUP אינו מצליח למצוא התאמה מדויקת בנתונים שלך - הוא מחזיר את #N/A.

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

כמו כן, שקול להשתמש בפונקציה CLEAN או TRIM כדי לנקות נתונים בתאים.

בעיה: ערך בדיקת המידע קטן מהערך הקטן ביותר במערך

אם הארגומנט range_lookup מוגדר ל- TRUE – וערך בדיקת המידע קטן מהערך הקטן ביותר במערך - תראה את #N/A. TRUE מחפש התאמה משוערת במערך ומחזיר את הערך הקרוב ביותר קטן מערך בדיקת המידע.

בדוגמה הבאה, ערך בדיקת המידע הוא 100, אך אין ערכים בטווח B2:C10 שפחות מ- 100; ומכאן השגיאה.

שגיאת N/A ב- VLOOKUP כאשר ערך בדיקת המידע קטן מהערך הקטן ביותר במערך

פתרון:

  • תקן את ערך בדיקת המידע כנחוץ.

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

בעיה: בדיקת עמודה אינה ממוינות בסדר עולה

אם הארגומנט range_lookup מוגדר ל- TRUE - ואחד מעמודות בדיקת המידע אינו ממוין בסדר עולה (A-Z) - תראה את #N/A.

פתרון:

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

  • השתמש בפונקציה INDEX/MATCH כדי לחפש ערך בטבלה לא ממוי אחרת.

בעיה: הערך הוא מספר נקודה צפה גדול

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

פתרון: קצר את המספרים על-ידי עיגולם עד חמישה מקומות עשרוניים באמצעות הפונקציה ROUND.

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

תוכל תמיד לשאול מומחה ב- Excel Tech Community או לקבל תמיכה בקהילת Answers.

למידע נוסף

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

הרחב את הכישורים שלך
סייר בהדרכה
קבל תכונות חדשות לפני כולם
הצטרף למשתתפי Microsoft Office Insider

האם מידע זה היה שימושי?

עד כמה אתם מרוצים מאיכות השפה?
מה השפיע על החוויה שלכם?

תודה על המשוב!

×