פונקציית VLOOKUP של Excel

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

• ערך - הערך שיש לחפש בעמודה הראשונה בטבלה.
• שולחן - הטבלה שממנה ניתן לאחזר ערך.
• col_index - העמודה בטבלה ממנה ניתן לאחזר ערך.
• טווח_מבט - [אופציונלי] TRUE = התאמה משוערת (ברירת מחדל). FALSE = התאמה מדויקת.

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

נתונים אנכיים | מספרי עמודה | רק נראה נכון | מצבים תואמים | התאמה מדוייקת | התאמה משוערת | משחק ראשון | התאמת Wildcard | חיפוש דו כיווני | קריטריונים מרובים | #לא שגיאות | סרטונים

V מיועד לאנכי

מטרת VLOOKUP היא לקבל מידע משולחן המאורגן כך:

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

 
= VLOOKUP (1004,B5:F9,4,FALSE) // returns 'Sue Martin'

עבור נתונים אופקיים, אתה יכול להשתמש ב HLOOKUP , INDEX ו- MATCH , או XLOOKUP .

מצטיינים ספירת תאים שאינם ריקים

VLOOKUP מבוסס על מספרי עמודות

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

ניתן לאחזר את שם המשפחה והדוא'ל באמצעות עמודות 3 ו -4:

 
= VLOOKUP (H3,B4:E13,2,FALSE) // first name = VLOOKUP (H3,B4:E13,3,FALSE) // last name = VLOOKUP (H3,B4:E13,4,FALSE) // email address

VLOOKUP נראה רק נכון

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

אם אתה צריך לחפש ערכים משמאל, ראה INDEX ו- MATCH , או XLOOKUP .

התאמה מדויקת ומשוערת

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

חשוב להבין זאת טווח_מבט כברירת מחדל ל- TRUE , כלומר VLOOKUP ישתמש בהתאמה משוערת כברירת מחדל, מה שעלול להיות מסוכן . מַעֲרֶכֶת טווח_מבט ל- FALSE כדי לאלץ התאמה מדויקת:

 
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match

הערה: תוכל גם לספק אפס (0) במקום FALSE להתאמה מדויקת.

התאמה מדוייקת

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

הנוסחה ב- H6 למצוא שָׁנָה , בהתבסס על התאמה מדויקת של שם הסרט, הוא:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

התאמה משוערת

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

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

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

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

משחק ראשון

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

 
= VLOOKUP (E5,B5:C11,2,FALSE) // returns 17

התאמת תווים כלליים

הפונקציה VLOOKUP תומכת תווים כלליים , המאפשר לבצע התאמה חלקית על ערך חיפוש. לדוגמה, תוכל להשתמש ב- VLOOKUP כדי לאחזר ערכים מהטבלה לאחר הקלדה בלבד חֵלֶק בעל ערך חיפוש. כדי להשתמש בתווים כלליים עם VLOOKUP, עליך לציין מצב התאמה מדויקת על ידי מתן FALSE או 0 עבור הארגומנט האחרון, טווח_מבט . הנוסחה ב- H7 מאחזרת את השם הפרטי, 'מייקל', לאחר הקלדת 'איה' לתא H4:

 
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)

קרא עוד הסבר מפורט כאן .

חיפוש דו כיווני

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

 
= VLOOKUP (H4,B5:E13, MATCH (H5,B4:E4,0),0)

לפרטים נוספים, ראה דוגמה זו .

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

קריטריונים מרובים

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

 
=C5&D5 // helper column

VLOOKUP מוגדר לעשות את אותו הדבר כדי ליצור ערך חיפוש. הנוסחה ב- H6 היא:

 
= VLOOKUP (H4&H5,B5:E13,4,0)

לפרטים, ראה דוגמה זו .

הערה: INDEX ו- MATCH ו XLOOKUP הן דרכים חזקות יותר לטפל בחיפושים המבוססים על מספר קריטריונים.

שגיאות VLOOKUP ו- #N/A

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

אחת הדרכים 'ללכוד' את שגיאת ה- NA היא להשתמש ב- פונקציית IFNA ככה:

הנוסחה ב- H6 היא:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'Not found')

ניתן להתאים את המסר לפי הצורך. כדי לא להחזיר כלום (כלומר להציג תוצאה ריקה) כאשר VLOOKUP מחזיר #לא ניתן להשתמש במחרוזת ריקה כזו:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'') // no message

השגיאה #N/A שימושית מכיוון שהיא אומרת לך שמשהו לא בסדר. בפועל, ישנן סיבות רבות לכך שאתה עשוי לראות שגיאה זו, כולל:

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

קרא עוד: VLOOKUP ללא #N/A שגיאות

עוד על VLOOKUP

• דוגמאות נוספות ל- VLOOKUP
• סרטוני VLOOKUP
• 23 עצות לשימוש ב- VLOOKUP

הערות אחרות

• טווח_מבט שולט אם ערך צריך להתאים בדיוק או לא. ברירת המחדל היא TRUE = אפשר התאמה לא מדויקת.
• מַעֲרֶכֶת טווח_מבט ל FALSE ל לִדרוֹשׁ התאמה מדויקת ואמיתית ל לאפשר התאמה לא מדויקת .
• אם טווח_מבט הוא TRUE (הגדרת ברירת המחדל), התאמה לא מדויקת תגרום לפונקציית VLOOKUP להתאים לערך הקרוב ביותר בטבלה שהוא עדיין פחות מ ערך .
• מתי טווח_מבט מושמטת, הפונקציה VLOOKUP תאפשר התאמה לא מדויקת, אך היא תשתמש בהתאמה מדויקת אם קיימת.
• אם טווח_מבט הוא TRUE (הגדרת ברירת המחדל) ודא שערכי החיפוש בשורה הראשונה בטבלה ממוינים בסדר עולה. אחרת, VLOOKUP עשוי להחזיר ערך שגוי או בלתי צפוי.
• אם טווח_מבט הוא FALSE (דורש התאמה מדויקת), ערכים בעמודה הראשונה של שולחן לא צריך למיין.