=VLOOKUP(F2,A2:D6,3,FALSE) מחפשת את הערך שב-F2 בעמודה הראשונה של A2:D6 ומחזירה את הערך מהעמודה השלישית באותה שורה. FALSE בסוף פירושו "התאמה מדויקת בלבד". בחרו מוצר אחר ב-F2 והמחיר משתנה.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
לחצו על G2 כדי לראות את הטבלה A2:D6 מסומנת במסגרת. שנו את ה-3 בנוסחה ל-2 ו-G2 יחזיר את הקטגוריה במקום המחיר, כי Category היא העמודה השנייה בטבלה. ההתאמה מתעלמת מגודל האותיות: pear מוצאת את Pear.
התחביר של VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| ארגומנט | מה זה | בדוגמה |
|---|---|---|
lookup_value | הערך שמחפשים. | F2 (Pear) |
table_array | הטבלה שבה מחפשים. VLOOKUP מחפשת רק בעמודה הראשונה שלה. | A2:D6 |
col_index_num | איזו עמודה של הטבלה להחזיר, כשסופרים מהעמודה הראשונה של הטבלה (1). | 3 (Price) |
range_lookup | FALSE או 0 להתאמה מדויקת. TRUE, 1 או כלום להתאמה משוערת. | FALSE |
מספר העמודה נספר מתחילת הטבלה, לא מעמודה A של הגיליון. בטבלה שמתחילה בעמודה C, col_index_num 2 פירושו עמודה D. מספר גדול מרוחב הטבלה מחזיר #REF!, ו-0 מחזיר #VALUE!.
באקסל שמוגדר לשפה שמשתמשת בפסיק עשרוני, הארגומנטים מופרדים בנקודה פסיק: =VLOOKUP(F2;A2:D6;3;FALSE).
בחירת עמודת ההחזרה עם MATCH
3 קבוע נשבר בשקט כשמישהו מוסיף עמודה בתוך הטבלה: הנוסחה ממשיכה להחזיר את העמודה השלישית, שעכשיו מכילה משהו אחר. תנו ל-MATCH למצוא את מספר העמודה מהכותרת במקום. כאן G1 היא רשימה נפתחת: בחרו Stock או Category ו-G2 מתעדכן בהתאם.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
MATCH(G1,A1:D1,0) מחזירה את המיקום של "Price" בשורת הכותרות, 3, ו-VLOOKUP משתמשת בו כמספר העמודה: 0.8 עבור Carrot. זה חיפוש דו־כיווני: שורה שנבחרת לפי מוצר, עמודה שנבחרת לפי כותרת. אותו רעיון כשהוא כתוב עם INDEX במקום VLOOKUP נמצא בעמוד INDEX ו-MATCH.
התאמה משוערת: VLOOKUP עם TRUE
עם TRUE כארגומנט האחרון, VLOOKUP לא מחפשת ערך שווה. היא מוצאת את הערך הגדול ביותר שקטן מערך החיפוש או שווה לו. זה מה שרוצים לטווחים: מדרגות מס, ציונים, תעריפי משלוח, רמות עמלה. העמודה הראשונה חייבת להיות ממוינת מהקטן לגדול.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
ה-4,200 של Ben לא נמצא בעמודה A. הערך הגדול ביותר שאינו מעליו הוא 1,000, ולכן הוא מקבל 3%. ה-5,000 של Cara מתאים בדיוק לשורת ה-5,000 ומקבל 5%. ה-12,500 של Dev מעל הרמה האחרונה ומקבל את השיעור האחרון, 8%. ערך מתחת לרמה הראשונה (כאן, נתון מכירות שלילי) מחזיר #N/A, ולכן הטבלה מתחילה ב-0.
סימני ה-$ ב-$A$2:$B$5 משאירים את הטבלה במקומה כש-F2 מועתק למטה עד F5. בלעדיהם, F3 היה מחפש ב-A3:B6 ומדלג על הרמה הראשונה.
השמטת הארגומנט הרביעי זהה ל-TRUE. ברשימת מוצרים לא ממוינת זה באג שקט: אקסל מחפש כאילו הרשימה ממוינת ויכול להחזיר מחיר מהשורה הלא נכונה, או #N/A לערך שנמצא שם. כשמחפשים שמות, קודים או מזהים, סיימו תמיד ב-FALSE.
למה VLOOKUP מחזירה #N/A
#N/A פירושו "לא נמצא". הגיליון למטה מראה שלוש סיבות נפוצות, ועמודה G חוזרת על כל חיפוש כשהוא עטוף ב-IFNA וב-TRIM.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A הערך שחיפשתם לא נמצא בטווח החיפוש.- הערך לא נמצא בטבלה. Kiwi לא נמצא ב-A2:A6. זה "לא נמצא" אמיתי, ו-
IFNA(...,"Not found")הופכת אותו לטקסט קריא. שנו את E2 ל-Apple ושתי העמודות יציגו את המחיר. - רווחים מיותרים. ב-E3 יש
"Milk "עם רווח בסוף, ולכן הוא לא שווה ל-Milk.TRIM(E3)מסירה אותו ו-G3 מוצא את המחיר. אם הרווחים נמצאים דווקא בטבלה, נקו את עמודה A עם TRIM פעם אחת ולא בכל חיפוש. - הערך נמצא בעמודה אחרת. Fruit קיים, אבל בעמודה B. VLOOKUP מחפשת רק בעמודה הראשונה של הטבלה, ולכן E4 נכשל בשתי העמודות. התחילו את הטבלה בעמודה שבה אתם מחפשים, או השתמשו ב-XLOOKUP, שמקבלת את עמודת החיפוש ואת עמודת ההחזרה בנפרד.
השתמשו ב-IFNA ולא ב-IFERROR סביב חיפוש. IFNA תופסת רק את #N/A, כך ש-#REF! ממספר עמודה שגוי עדיין מוצג במקום להיות מוסתר כ-"Not found".
עוד שתי סיבות:
- מספרים שמאוחסנים כטקסט. אם בעמודה A יש קודי מוצר שהוקלדו כטקסט (לרוב אחרי ייבוא, עם משולש ירוק קטן בפינה) וב-F2 יש את המספר 101,
=VLOOKUP(F2,A2:B6,2,FALSE)מחזירה #N/A אף ש-101 מופיע ברשימה. המירו צד אחד:=VLOOKUP(F2&"",A2:B6,2,FALSE)מחפשת את הטקסט "101", ו-=VLOOKUP(VALUE(F2),A2:B6,2,FALSE)מחפשת מספר כש-F2 הוא הטקסט. - התאמה משוערת על נתונים לא ממוינים, כמו שתואר בסעיף הקודם.
VLOOKUP מחזירה 0 במקום תא ריק
כשהתא ש-VLOOKUP מגיעה אליו ריק, אקסל מציג 0, לא תא ריק. 0 בעמודת Stock נקרא אז כ"אזל מהמלאי" כשהמלאי פשוט לא הוזן. הוסיפו &"" לנוסחה, או בדקו את האורך של התוצאה:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
הראשונה קצרה יותר אבל הופכת כל מספר שהיא מחזירה לטקסט, ולכן SUM בהמשך מדלגת עליו. השנייה משאירה מספרים כמספרים.
VLOOKUP מגיליון אחר
כתבו את שם הגיליון ואת ! לפני הטבלה. כשבונים את הנוסחה באקסל, לחצו על הלשונית של הגיליון האחר ובחרו את הטווח: אקסל כותב Prices!A2:B6 בשבילכם. כאן הלשונית Orders מחפשת מחירים בלשונית Prices.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
פתחו את הלשונית Prices ושנו את המחיר של Apple: סכום ההזמנה מתעדכן. שני פרטים:
- שם גיליון עם רווחים צריך גרשיים בודדים:
=VLOOKUP(B2,'Price list'!$A$2:$B$6,2,FALSE). - טבלה בחוברת עבודה אחרת מוסיפה את שם הקובץ בסוגריים מרובעים,
[Prices.xlsx]Prices!$A$2:$B$6. כשהקובץ הזה סגור, אקסל מציג בנוסחה את הנתיב המלא שלו והחיפוש ממשיך לעבוד מהקובץ השמור.
VLOOKUP עם תווים כלליים (התאמה חלקית)
עם FALSE, ערך החיפוש יכול להכיל תווים כלליים: * מייצג כל מספר של תווים ו-? מייצג תו אחד בדיוק. "*"&E2&"*" מוצאת את המוצר הראשון שהשם שלו מכיל את הטקסט שב-E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
"coffee" מתאים גם ל-Iced coffee וגם ל-Coffee beans; VLOOKUP מחזירה את הראשון מלמעלה, $2.90. שנו את E2 ל-bean כדי לקבל $8.50, או ל-juice. כדי לחפש כוכבית או סימן שאלה אמיתיים, שימו לפניהם טילדה: "~*".
VLOOKUP שמאלה
VLOOKUP לא יכולה להחזיר עמודה שנמצאת משמאל לעמודה שבה היא מחפשת: col_index_num סופר רק ימינה, ומספרים שליליים הם שגיאה. כדי למצוא את המוצר עבור מחיר נתון, חפשו בעמודה C והחזירו את עמודה A עם XLOOKUP או עם INDEX ו-MATCH:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
שתיהן מחזירות Bread על הנתונים של הגיליון הראשון. ב-XLOOKUP יש את ההסבר המלא.
תרגול: עלות משלוח לפי משקל
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
תורכם: כל עלות חלה מהמשקל שלה ועד המשקל הבא ברשימה. ב-E2, השתמשו ב-VLOOKUP כדי להחזיר את עלות המשלוח של משקל החבילה שב-D2.
VLOOKUP עם שני קריטריונים
VLOOKUP מקבלת ערך חיפוש אחד. כדי להתאים לפי שתי עמודות, בנו עמודת עזר שמחברת אותן, שימו אותה ראשונה בטבלה, וחפשו את אותו טקסט מחובר. עמודה A למטה היא =B2&"-"&C2 שמועתקת למטה, ולכן היא מכילה Coffee-Small, Coffee-Large וכן הלאה.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
תורכם: עמודה A מחברת מוצר וגודל עם מקף. ב-G2, החזירו את המחיר עבור המוצר שב-E2 והגודל שב-F2.
המפריד חשוב: "Tea"&"Large" נותן TeaLarge, שלא מתאים לשום דבר בעמודה A. ב-Excel 2021 וב-Microsoft 365 אפשר לוותר על עמודת העזר עם =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6); חיפוש עם כמה קריטריונים מראה את זה ואת הגרסה עם INDEX/MATCH.
שאלות נפוצות
איך עושים VLOOKUP באקסל?
הקלידו =VLOOKUP( ותנו ארבעה ארגומנטים: הערך שמחפשים, הטבלה (העמודה הראשונה שלה חייבת להכיל את הערך הזה), מספר העמודה שתוחזר, ו-FALSE להתאמה מדויקת. =VLOOKUP("Pear",A2:D6,3,FALSE) מוצאת את Pear בעמודה A ומחזירה את הערך מעמודה C של אותה שורה.
מה המשמעות של TRUE או FALSE בסוף VLOOKUP?
FALSE (או 0) מבקשת התאמה מדויקת ומחזירה #N/A כשהערך חסר. TRUE (או 1, או השמטת הארגומנט) מבקשת התאמה משוערת: הערך הגדול ביותר שקטן מערך החיפוש או שווה לו, וזה עובד רק כשהעמודה הראשונה ממוינת בסדר עולה.
למה ה-VLOOKUP שלי מחזירה #N/A?
הערך לא נמצא בעמודה הראשונה של הטבלה. הסיבות הרגילות הן שגיאת הקלדה, רווח מיותר ("Milk " אינו "Milk"), מספר שמאוחסן כטקסט רק בצד אחד, או ערך שנמצא בעמודה אחרת. עטפו את הנוסחה ב-IFNA כדי להציג טקסט משלכם: =IFNA(VLOOKUP(F2,A2:D6,3,FALSE),"Not found").
האם VLOOKUP יכולה לחפש שמאלה?
לא. VLOOKUP מחזירה רק עמודות שמימין לעמודה הראשונה של הטבלה. השתמשו ב-=XLOOKUP(F2,C2:C6,A2:A6) ב-Excel 2021 או ב-Microsoft 365, או ב-=INDEX(A2:A6,MATCH(F2,C2:C6,0)) בכל גרסה.
איך עושים VLOOKUP מגיליון אחר?
כתבו את שם הגיליון וסימן קריאה לפני הטווח: =VLOOKUP(B2,Prices!$A$2:$B$6,2,FALSE). אם בשם הגיליון יש רווח, עטפו אותו בגרשיים בודדים: 'Price list'!$A$2:$B$6.
