נושא זה מתאר את הסיבות הנפוצות ביותר ל- VLOOKUP כתוצאה שגויה מהפונקציה ומספק הצעות לשימוש ב- INDEX וב- MATCH במקום זאת.
עצה: בנוסף, עיין בכרטיס לעיון מהיר: עצות לפתרון בעיות ב- VLOOKUP שמציג את הסיבות הנפוצות לבעיות #NA בקובץ PDF נוח. באפשרותך לשתף את ה- PDF עם אנשים אחרים או להדפיס לעיון משלך.
בעיה: ערך בדיקת המידע אינו נמצא בעמודה הראשונה בארגומנט table_array
אילוץ אחד של VLOOKUP הוא שהיא יכולה לחפש רק ערכים בעמודה הימנית ביותר במערך הטבלה. אם ערך בדיקת המידע שלך אינו נמצא בעמודה הראשונה של המערך, תראה את שגיאת #N/A.
בטבלה הבאה, אנו רוצים לאחזר את מספר היחידות שנמכרו עבור Kale.
תוצאת השגיאה #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(מערך או הפניה, 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))
הנוסחה מחפשת את הערך הראשון ב- 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; ולכן מופיעה השגיאה.
פתרון:
-
תקן את ערך בדיקת המידע לפי הצורך.
-
אם אינך יכול לשנות את ערך בדיקת המידע ואתה זקוק לגמישות רבה יותר עם ערכים תואמים, שקול להשתמש ב- 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.
