XLOOKUP מוצאת ערך בטווח אחד ומחזירה את הערך המתאים מטווח אחר. זו התחליף המודרני ל-VLOOKUP ול-HLOOKUP: היא מחפשת בכל כיוון, מתאימה בדיוק כברירת מחדל, ויש לה תשובה מובנית ל"לא נמצא".
הצורה הבסיסית
דמיין רשימת עובדים. A2:A5 מכיל את המזהים, B2:B5 את השמות ו-C2:C5 את המחלקות. שלושת הארגומנטים הראשונים הם מה לחפש, איפה לחפש ומה להחזיר.
=XLOOKUP(F1,A2:A5,B2:B5)
מחפש את המזהה שהוקלד ב-F1 ומחזיר את השם מאותו מיקום.
מכיוון שטווח החיפוש וטווח ההחזרה נפרדים, מפתח החיפוש לא חייב להיות בעמודה השמאלית ביותר, ושום דבר לא נשבר כשמישהו מוסיף עמודה באמצע.
טיפול בערך חסר
הארגומנט הרביעי הוא מה להחזיר כשלא נמצאת התאמה, כך שאין צורך ב-IFERROR נוסף.
=XLOOKUP(F1,A2:A5,B2:B5,"No such ID")
רק מקרה ה"לא נמצא" מוחלף. שגיאות אחרות, כמו טווחים בגדלים שונים, עדיין יופיעו, וזה בדיוק מה שרוצים.
מצבי התאמה וחיפוש
הארגומנט החמישי קובע את מצב ההתאמה. 0 הוא מדויק, ברירת המחדל. -1 פירושו מדויק או הערך הקטן הבא, ו-1 פירושו מדויק או הערך הגדול הבא, שימושי לטווחים כמו שיעורי מס או הנחות. 2 מפעיל תווים כלליים.
=XLOOKUP(F1,A2:A5,B2:B5,"Below range",-1)
עם ספים ב-A2:A5, הנוסחה מחזירה את הערך של הסף הגבוה ביותר שאינו עולה על F1. הספים אינם חייבים להיות ממוינים.
הארגומנט השישי קובע את כיוון החיפוש. 1 מתחיל מלמעלה, ברירת המחדל. -1 מתחיל מלמטה, כך שמקבלים את ההתאמה האחרונה במקום הראשונה, למשל ההזמנה האחרונה של לקוח.
=XLOOKUP(F1,A2:A5,B2:B5,"Not found",0,-1)
התאמה מדויקת, עם חיפוש מלמטה למעלה.
חיפושים דו-כיווניים ותווים כלליים
מכיוון ש-XLOOKUP מחזירה טווח, אפשר לקנן אותה. נניח ש-C1:E1 מכיל את הכותרות Q1, Q2 ו-Q3, ו-C2:E5 מכיל את המכירות של ארבעת האנשים. החיפוש הפנימי בוחר את העמודה, והחיצוני בוחר את השורה:
=XLOOKUP(F1,A2:A5,XLOOKUP(F2,C1:E1,C2:E5))
F1 מכיל את המזהה, F2 מכיל את הרבעון.
עם מצב התאמה 2, ערך החיפוש יכול להכיל תווים כלליים, ולכן =XLOOKUP("Ma*",B2:B5,A2:A5,"",2) מחזירה את המזהה של השם הראשון שמתחיל ב-Ma.
החזרת יותר מערך אחד
אם טווח ההחזרה מכיל כמה עמודות, XLOOKUP מחזירה את השורה כולה, והתוצאה דולפת לתאים שליד הנוסחה.
=XLOOKUP(F1,A2:A5,B2:C5)
מחזירה את השם ואת המחלקה של האדם בבת אחת.
XLOOKUP דורשת Excel 2021, Excel 2024 או Microsoft 365. היא לא קיימת ב-Excel 2019 ומוקדם יותר, ולכן חוברת עבודה משותפת עדיין עשויה לדרוש את VLOOKUP.
טווח החיפוש וטווח ההחזרה חייבים להיות בעלי אותו מספר שורות, או אותו מספר עמודות כשמחפשים לאורך שורה.
התאמת טקסט אינה רגישה לאותיות גדולות וקטנות. רווחים מיותרים עדיין נחשבים, ולכן כדאי לנקות נתונים שיובאו בעזרת TRIM.