XLOOKUP ב-Excel: תחביר, דוגמאות ולמה הוא עדיף על VLOOKUP
By Mirko Peters · · Updated
XLOOKUP ב-Excel מוסבר: התחביר, כל ארגומנט, דוגמאות להתאמה מדויקת, לתווים כלליים ולהתאמה אחרונה, חיפוש בכמה עמודות, והשוואה ל-VLOOKUP.
Automatically translated with AI from the original post. Read the English original
XLOOKUP ב-Excel מוצאת ערך בטווח אחד ומחזירה את הפריט המתאים מטווח אחר: =XLOOKUP(lookup_value, lookup_array, return_array). הקלידו מזהה עובד, הצביעו על עמודת המזהים, הצביעו על עמודת השמות, וקבלו את השם. מאמר זה מיועד למשתמשי Excel שעדיין כותבים VLOOKUP מתוך הרגל, או שראו לאחרונה XLOOKUP בקובץ של עמית. תקבלו את התחביר, שישה ארגומנטים, דוגמאות מעובדות למקרים שמופיעים בעבודה, השוואה ל-VLOOKUP, ורשימה של השגיאות שסביר שתיתקלו בהן.
עיקרי הדברים
- XLOOKUP מקבלת ערך חיפוש, טווח חיפוש וטווח החזרה: =XLOOKUP(lookup_value, lookup_array, return_array). היא מוצאת את ההתאמה הראשונה ומחזירה את הפריט מטווח ההחזרה.
- היא יכולה לחפש בצד שמאל של עמודת החיפוש, וברירת המחדל שלה היא התאמה מדויקת. שני דברים ש-VLOOKUP לא יכולה לעשות, או לא עושה כברירת מחדל.
- הארגומנט הרביעי, if_not_found, מחליף את שגיאת #N/A בטקסט שאתם בוחרים. בלעדיו, חיפוש ללא התאמה יחזיר #N/A.
- match_mode 2 מפעיל תווים כלליים, הערכים -1 ו-1 מוצאים את הפריט הקטן או הגדול הבא, ו-search_mode -1 מחפש מהפריט האחרון לראשון.
- דף העזרה של Microsoft מפרט את XLOOKUP עבור Microsoft 365, 2021 ו-2024, וההערות שלו על Excel 2016 ו-2019 סותרות זו את זו, ולכן בדקו קבצים משותפים לפני שאתם מסתמכים עליה.
התחביר והאפשרויות שלהלן מבוססים על דף Microsoft בנושא פונקציית XLOOKUP. במקומות שבהם דפוס מסוים אינו מופיע באותו דף, הטקסט מציין זאת. זוהי פונקציה ב-Excel, והיא הפונקציה שמיקרוסופט עצמה מציעה כבחירה עדיפה כשפותחים את דף העזרה של VLOOKUP.
התחביר ושישת הארגומנטים
הנוסחה המלאה נראית כך:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
שלושת הארגומנטים הראשונים הם חובה. שלושת האחרונים הם אופציונליים.
lookup_valueהוא מה שמחפשים, למשל מזהה בתא D2. אם משמיטים אותו, XLOOKUP מחזירה תאים ריקים שהיא מוצאת במערך החיפוש.lookup_arrayהוא הטווח או המערך לחיפוש, כגון A2:A100.return_arrayהוא הטווח או המערך שממנו מחזירים ערכים, כגון B2:B100.if_not_foundהוא הטקסט שיוצג כאשר אין התאמה תקפה. אם משמיטים אותו, מקבלים #N/A.match_modeקובע איך מתבצעת ההתאמה.0הוא התאמה מדויקת וזו ברירת המחדל.-1פירושו התאמה מדויקת או, אם אין כזו, הפריט הקטן הבא.1פירושו התאמה מדויקת או הפריט הגדול הבא.2הוא התאמה עם תווים כלליים, שבה*,?ו-~בעלי משמעות מיוחדת.search_modeקובע את כיוון החיפוש.1מחפש מהפריט הראשון לאחרון וזו ברירת המחדל.-1מחפש מהאחרון לראשון.2ו--2הם חיפושים בינאריים על נתונים הממוינים בסדר עולה או יורד. Microsoft מזהירה שנתונים לא ממוינים יחזירו תוצאות לא תקינות במצבים הבינאריים.
שמרו על אותו גודל במערך החיפוש ובמערך ההחזרה. אם מערך החיפוש מכסה את השורות 2 עד 100, גם מערך ההחזרה צריך לכסות את השורות 2 עד 100. דף התמיכה של Microsoft אינו מנסח את זה ככלל מפורש, אבל טווחים שאינם תואמים הם הסיבה הנפוצה לכך שחיפוש מחזיר שורה שגויה או שגיאה.
חיפושים בסיסיים וארגומנט הערך שלא נמצא
נניח שמזהי העובדים נמצאים בתאים A2:A100 והשמות בתאים B2:B100. אתם מקלידים את המזהה שאתם רוצים בתא D2.
- לחצו על התא שבו צריכה להופיע התוצאה, למשל E2.
- הזינו
=XLOOKUP(D2,A2:A100,B2:B100). - לחצו על Enter.
תקבלו את השם ששייך למזהה. XLOOKUP מחזיר את הפריט של ההתאמה הראשונה שהוא מוצא. אם המזהה לא קיים, תקבלו #N/A.
כדי להציג הודעה ידידותית יותר, הוסיפו את הארגומנט הרביעי:
=XLOOKUP(D2,A2:A100,B2:B100,"No record found")
כעת מזהה שאינו ברשימה יציג את הטקסט "No record found". השתמשו בזה בגיליונות שאנשים אחרים קוראים. אזהרה אחת: הודעה כזו יכולה להסתיר בעיה אמיתית. אם בכל השורות מופיע "No record found", הבעיה נמצאת בטווחים ולא בנתונים, לכן בדקו קודם מזהה שאתם יודעים שקיים.
חיפוש שמאלה
VLOOKUP אינו יכול להחזיר ערך מעמודה שנמצאת משמאל לעמודת החיפוש. XLOOKUP יכול, משום שטווחי החיפוש וההחזרה נפרדים. אם השמות נמצאים בעמודה C והמזהה שאתם רוצים לקבל נמצא בעמודה A:
=XLOOKUP(F2,C2:C100,A2:A100)
דף התמיכה של Microsoft אומר זאת במפורש: עמודת החיפוש יכולה להיות משני צדי עמודת ההחזרה.
מצבי התאמה: תווים כלליים וערכים קרובים
התאמה עם תווים כלליים
הגדירו את match_mode לערך 2 כדי להשתמש בתווים כלליים. הכוכבית מייצגת כל מספר תווים, וסימן השאלה מייצג תו אחד בדיוק. טילדה מאפשרת לחפש כוכבית או סימן שאלה כטקסט מילולי.
=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)
הנוסחה מחזירה את הרשומה הראשונה שמתחילה ב-"Jan". אם תשמיטו את הערך 2, Excel יחפש את הטקסט המילולי "Jan*" ויחזיר את הודעת ה-not-found שלכם. זו הטעות הנפוצה ביותר עם תווים כלליים. שמרו על תבניות חיפוש ספציפיות ככל האפשר, כי XLOOKUP מחזיר רק את ההתאמה הראשונה, ותבנית רחבה עלולה להתאים לרשומה שלא התכוונתם אליה.
הפריט הקטן או הגדול הבא
מצבים -1 ו-1 מיועדים לסקאלות: שיעורי מס, מדרגות עמלה, נקודות מחיר. שימו את הסף בתאים A2:A6 והשיעורים בתאים B2:B6, וחפשו הכנסה בתא E2:
=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)
עם -1 Excel מחזיר את ההתאמה המדויקת, או את הסף הקטן הבא אם אין התאמה מדויקת. המקום הריק לפני -1 מדלג על ארגומנט הערך שלא נמצא. הדוגמה של Microsoft להתאמה משוערת משתמשת ב-1 כדי להחזיר שיעור מס עבור ערך ההכנסה הגדול הבא, לכן בחרו את הכיוון שמתאים לטבלה שלכם. בניגוד ל-VLOOKUP משוער בסגנון הישן, אין צורך למיין את הספים עבור מצבים אלה. מצבי חיפוש בינארי הם אלה שדורשים נתונים ממוינים.
חיפוש מהסוף: ההתאמה האחרונה
כאשר ערך מופיע כמה פעמים, XLOOKUP מחזיר את הראשון שהוא פוגש. כדי לקבל את האחרון, חפשו מהסוף על ידי הגדרת search_mode לערך -1:
=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)
אם הגיליון מציג הזמנות לפי סדר תאריכים, הנוסחה הזו מחזירה את הרשומה האחרונה של לקוח. הערך 0 שומר על התאמה מדויקת. זו התבנית לשאלות כמו "המחיר האחרון", "הקשר האחרון" ושאלות דומות שהיו מסורבלות עם VLOOKUP.
החזרת כמה עמודות בבת אחת
דף התמיכה של Microsoft מציין שבניגוד ל-VLOOKUP, XLOOKUP יכול להחזיר כמה עמודות כמערך אחד. הרחיבו את טווח ההחזרה:
=XLOOKUP(D2,A2:A100,B2:D100)
הנוסחה מציבה בתא את ההתאמה מעמודה B, ומפזרת את הערכים מעמודות C ו-D לתאים שמימין. השאירו להם מקום. אם התאים מימין כבר מכילים נתונים, Excel יציג שגיאת spill במקום לדרוס אותם. הדוגמה של Microsoft מחזירה שם עובד ומחלקה מתוך נוסחה אחת.
חיפושים דו-כיווניים
Microsoft מראה כיצד לקנן שתי פונקציות XLOOKUP כדי למצוא ערך בצומת של שורה ועמודה. התבנית, מדף התמיכה שלהם, היא:
=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))
הפונקציה הפנימית בוחרת את העמודה שתואמת לכותרת שב-C3. הפונקציה החיצונית מחפשת אחר כך את D2 בתוויות השורות ומחזירה את הערך מאותה עמודה. Microsoft משווה זאת לשילוב של INDEX ו-MATCH. אם יש לכם דוח עם מוצרים לאורך הצד וחודשים לרוחב הראש, זו התחליף לנוסחה מסובכת יותר.
חיפוש לפי כמה קריטריונים
XLOOKUP מקבל ערך חיפוש אחד, ודף הפונקציה של Microsoft אינו כולל דוגמה לחיפוש לפי כמה קריטריונים. תבנית נפוצה היא לתת ל-Excel לבדוק שני תנאים בו זמנית. נניח שהקטגוריה נמצאת ב-A, הסטטוס ב-B והמחיר ב-C. כדי למצוא את המחיר לקטגוריה שב-F2 ולסטטוס שב-G2:
=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")
כל השוואה מחזירה TRUE או FALSE. הכפלה ביניהן נותנת 1 רק בשורות שבהן שני התנאים מתקיימים, וXLOOKUP מחפש את ה-1 הראשון. זו תבנית שעובדת בגרסאות הנוכחיות של Microsoft 365, אך היא אינה מופיעה בדף הפונקציה של Microsoft, ולכן כדאי לבדוק אותה על דגימה קטנה. עמודת עזר שמחברת את שני השדות היא האלטרנטיבה הישנה יותר. הפרידו בין הערכים באמצעות מפריד, למשל =A2&"|"&B2, וחפשו את אותו טקסט מחובר.
XLOOKUP וVLOOKUP זה לצד זה
דף VLOOKUP של Microsoft מציג את התחביר כ-VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), וקובע שערך החיפוש חייב להימצא בעמודה הראשונה של הטווח. אם משמיטים את range_lookup, VLOOKUP מבצע התאמה משוערת, שמניחה שהעמודה הראשונה ממוינת. הגדירו אותו ל-FALSE להתאמה מדויקת. אותו דף ממליץ לנסות את XLOOKUP ומציין שהוא פועל בכל כיוון ומחזיר התאמות מדויקות כברירת מחדל.
אותה משימה בשתי הפונקציות, כאשר המזהים נמצאים בעמודה A והשמות בעמודה B:
=VLOOKUP(D2,A2:B100,2,FALSE)=XLOOKUP(D2,A2:A100,B2:B100)
ההבדלים שחשובים בשימוש היומיומי:
- VLOOKUP מתייחס לתוצאה לפי מספר עמודה, כך שהוספת עמודה לטבלה משנה את משמעות המספר. XLOOKUP מתייחס ישירות לטווח ההחזרה.
- VLOOKUP אינו יכול לחפש שמאלה. XLOOKUP יכול.
- VLOOKUP מבצע כברירת מחדל התאמה משוערת. XLOOKUP מבצע כברירת מחדל התאמה מדויקת.
- ל-XLOOKUP יש ארגומנט מובנה לטיפול במקרה של אי-מציאה וכיוון חיפוש מובנה.
אין צורך לכתוב מחדש חוברות עבודה ישנות. אם VLOOKUP עובד, השאירו אותו. השתמשו ב-XLOOKUP בנוסחאות חדשות ולחיפושים שדורשים חיפוש שמאלה, התאמה אחרונה או כמה עמודות החזרה.
באילו גרסאות של Excel נתמך XLOOKUP
כאן צריך להיזהר. דף Microsoft מפרט את XLOOKUP עבור Excel for Microsoft 365 ב-Windows וב-Mac, Excel 2024, Excel 2021, ואפליקציות ל-iPad, ל-iPhone ולאנדרואיד. הערה בדף קובעת שהפונקציה אינה זמינה ב-Excel 2016 או 2019, אך עשויה להופיע בחוברות עבודה שנוצרו בגרסאות חדשות יותר, ואילו שורת ה-applies-to שבדף כוללת גם את 2016 ו-2019. יש כאן סתירה, ולכן אין להתייחס ל-Excel 2016 ו-2019 כאל גרסאות בטוחות. Excel for the web גם אינו מופיע בדף הזה.
אם תשלחו חוברת עבודה למישהו שעובד בגרסה ישנה יותר, הנוסחה עלולה להציג שגיאה במקום ערך. שאלו אילו גרסאות משתמשים הנמענים, ובקבצים שנשלחים לאנשים רבים בדקו על הגרסה הישנה ביותר שנמצאת בשימוש. אם אינכם יכולים לבדוק, השתמשו בפונקציית חיפוש קלאסית לאותו קובץ.
שגיאות וטעויות נפוצות
- שכחה של
match_modeשווה ל-2 כשמשתמשים בתווי כללי. אז החיפוש מחפש את התווים באופן מילולי. - שימוש ב-
search_modeבינארי (2 או -2) בנתונים לא ממוינים. Microsoft קובעת שהתוצאות אינן תקפות. - טווחי חיפוש והחזרה בגדלים שונים. הקפידו שיהיו באותו אורך.
- רווחים נסתרים ומספרים השמורים כטקסט. "100" ו-100 שונים זה מזה, ולכן החיפוש נכשל ומופיעה השגיאה #N/A. נקו את הנתונים באמצעות TRIM, או המירו את המספרים.
- הסתרת בעיות בהודעת אי-מציאה ידידותית. בדקו ערך ידוע אחד לפני שאתם נשענים על הנוסחה.
- הפניית מערך ההחזרה לתאים שבהם התוצאה צריכה להישפך, והשארת התאים האלה תפוסים.
- הפניות לעמודה שלמה בגיליונות גדולים מאוד. השתמשו בטווח מוגבל או בטבלת Excel, כדי ש-Excel יעבור על פחות תאים.
נושאים קשורים לעיון
- XLOOKUP לעומת INDEX ו-MATCH. שניהם מבצעים חיפוש שמאלה, והצמד INDEX ו-MATCH עובד גם בגרסאות ישנות יותר של Excel. כדאי לקרוא על כך אם אתם משתפים קבצים עם אנשים שעובדים ב-Excel 2016 או 2019.
- מספר תוצאות. XLOOKUP מחזירה רק את ההתאמה הראשונה. כדי לקבל את כל ההתאמות, עיינו בפונקציה FILTER במקום זאת.
- XLOOKUP עם טבלאות של Excel. הפניות מובְנות גורמות לטווחים לגדול יחד עם הנתונים, והן קריאות היטב בתוך נוסחאות.
- נתונים מ-Power Query. לאחר שטענתם נתונים לתוך טבלה, XLOOKUP יכולה לחפש בה כמו בכל טבלה אחרת. רעננו קודם את השאילתה, ובדקו את התוצאות לאחר מכן.
- Excel for the web. דף XLOOKUP של Microsoft אינו מציין את הגרסה הזו, לכן בדקו בסביבה שלכם אם הפונקציה פועלת עבור העמיתים לפני ששיתפתם קובץ.
הצעד הבא
קחו VLOOKUP אחד מהחוברת הנוכחית שלכם, וכתבו אותו מחדש כ-XLOOKUP. הוסיפו הודעה למקרה של אי-מציאה, והשוו בין שתי התוצאות על כמה מזהים שאתם מכירים. אם שתיהן מסכימות, יש לכם תבנית שאפשר לעשות בה שימוש חוזר בכל XLOOKUP חדש ב-Excel.