XLOOKUP
הנוסחה XLOOKUP מחפשת טווח עבור ערך שצוין ומחזירה את הערך מאותה שורה בעמודה אחרת.
XLOOKUP(ערך-חיפוש, טווח-חיפוש, טווח-החזרה, אם-לא-נמצא, סוג-התאמה, סוג-חיפוש)
טווח-חיפוש: התאים לחיפוש.
טווח-החזרה: התאים להחזרה.
אם-לא-נמצא: ארגומנט אופציונלי לציון ההודעה שתוצג אם לא תימצא התאמה.
סוג-התאמה: ארגומנט אופציונלי המציין את סוג ההתאמה שיש לחפש.
מדויקת או הקטנה ביותר הבאה (-1): אם אין התאמה, הפונקציה מחזירה שגיאה.
התאמה מדויקת (0 או מושמט): אם אין התאמה מדויקת, הפונקציה מחזירה שגיאה.
מדויקת או הגדולה ביותר הבאה (1): אם אין התאמה, הפונקציה מחזירה שגיאה.
תו כללי (2): לתווים *, ?, ו‑~ יש משמעות מסוימת. REGEX יכול לשמש ב‑XLOOKUP רק אם משתמשים בתו משתנה.
סוג-חיפוש: ארגומנט אופציונלי המציין את הסדר שבו יש לחפש את הטווח.
בינארי בסדר יורד (-2): חיפוש בינארי הדורש שהטווח יסודר בסדר יורד, אחרת תוחזר שגיאה.
מהאחרון לראשון (-1): מחפש את הטווח מהפריט האחרון לראשון.
מהראשון לאחרון (1 או מושמט): מחפש את הטווח מהפריט הראשון לאחרון.
בינארי בסדר עולה (2): חיפוש בינארי הדורש שהטווח יסודר בסדר עולה, אחרת תוחזר שגיאה.
הערות
אם טווח-חיפוש או טווח-החזרה מקיף הפניה (למשל ״B״), המערכת מתעלמת מכותרות עליונות ותחתונות.
כדי להחזיר תוצאה ממערך, יש להשתמש בפונקציהINDEX עם XLOOKUP.
דוגמה |
|---|
הטבלה שלהלן, הנקראת ״מוצרים״, כוללת מוצרים בשילוב עם המאפיינים שלהם כמו גודל ומחיר: |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | מוצר | אורך (ס״מ) | רוחב (ס״מ) | משקל (ק״ג) | מחיר |
2 | מוצר 1 | 16 | 17 | 10 | $82.00 |
3 | מוצר 2 | 16 | 20 | 18 | $77.00 |
4 | מוצר 3 | 11 | 11 | 15 | $88.00 |
5 | מוצר 4 | 15 | 16 | 20 | $63.00 |
חיפוש באמצעות XLOOKUP |
|---|
באמצעות XLOOKUP, ניתן להוסיף לגיליון העבודה נוסחה שתחזיר כל ערך משויך על‑ידי ציון שם המוצר תחילה ואז ציון העמודה שבה ממוקם הערך שברצונך להחזיר. למשל, אם רצית להחזיר את הרוחב של מוצר 1 בטבלה מעל, יכולת להשתמש בנוסחה הבאה, שמחזירה ״17 ס״מ״:  בנוסחה נמצאים בשימוש הארגומנטים הבאים:
|
הגדרת המחרוזת if-not-found |
|---|
אם רוצים לחפש אורך ספציפי של מוצר ולהחזיר את הרוחב התואם לו, כמו גם את המחרוזת שיש להחזיר אם לא נמצאת אף התאמה, אפשר להשתמש בנוסחה הבאה, שתחזיר ״אין התאמה״:  בנוסחה זו, הארגומנט if-not-found משמש לביצוע חיפוש ספציפי יותר:
|
חיפוש הערך הקרוב ביותר הבא |
|---|
הנוסחה XLOOKUP יכולה גם לאפשר חיפוש רחב שמתבסס על ערך ספציפי והערכים הקרובים אליו. אם משנים את match-type מהנוסחה שמעל, אפשר להחזיר את הרוחב שתואם אורך של 13 ס״מ, או את הערך הקטן ביותר הבא. הנוסחה הבאה מחזירה רוחב של ״11 ס״מ״:  בנוסחה הזו, הארגומנטים זהים לאלה שהיו מעל, למעט העובדה שערך אחר נמצא בשימוש עבור match-type כדי לשנות את אופן החיפוש בטבלה:
|
שינוי סדר החיפוש |
|---|
במקרים מסוימים, שינוי סדר החיפוש בטבלה באמצעות XLOOKUP יכול להיות שימושי. לדוגמה, בטבלה מעל, יש שני מוצרים שאורכם 16 ס״מ, כך שישנן שתי התאמות פוטנציאליות אם מחפשים אחר ״16 ס״מ״ בעמודה ״אורך״ באמצעות search-value ו‑search-range. ניתן להגדיר את סדר החיפוש באמצעות נוסחה כמו זו, שמחזירה ״20 ס״מ״:  בנוסחה זו, הארגומנט search-type משמש להגדרת הסדר שבו הפונקציה XLOOKUP מחפשת התאמה בטבלה:
|
השימוש בפונקציה XLOOKUP עם פונקציות אחרות |
|---|
ניתן להשתמש ב‑XLOOKUP גם עם פונקציות אחרות, כגון SUM. למשל, ניתן להשתמש בנוסחה כמו זו שלהלן כדי להחזיר את הנתון $247, ה‑SUM של מחירי המוצרים 1, 2 ו‑3:  בדוגמה הזו, פונקציית ה‑XLOOKUP הראשונה מחפשת את המחיר של מוצר 1 ופונקציית ה‑XLOOKUP השנייה מחפשת את המחיר של מוצר 3. תו הנקודתיים (:) בין פונקציות ה‑XLOOKUP מציין שעל SUM להחזיר לא רק את המחיר הכולל של מוצר 1 ומוצר 3 אלא גם כל ערך ביניהם. |
בנוסחה הבאה נעשה שימוש ב‑XLOOKUP עם REGEX כדי להחזיר את מוצר 2, המוצר הראשון בעל רוחב שמתחיל ב‑״2״:  בדוגמה הזו נעשה שימוש ב״תו כללי (2)״ עבור match-type כדי לנצל את התווים הכלליים בפונקציה REGEX. |
דוגמאות נוספות |
|---|
בהינתן הטבלה הבאה: |
A | B | C | |
|---|---|---|---|
1 | שם | גיל | משכורת |
2 | נעמה | 35 | 71000 |
3 | מתי | 27 | 81000 |
4 | יעל | 42 | 86000 |
5 | סיגל | 51 | 66000 |
6 | יואב | 28 | 52000 |
7 | תום | 49 | 62000 |
8 | אהרון | 63 | 89000 |
9 | מירי | 22 | 34000 |
10 | אפרת | 29 | 52000 |
11 | בן | 35 | 52500 |
הפונקציה =XLOOKUP(49,B2:B11,C2:C11) מחזירה ״62000״, שהיא המשכורת של העובד הראשון שגילו 49. הפונקציה =XLOOKUP(60000,C2:C11,B2:B11,"No match") מחזירה ״No match״, מכיוון שאין עובד שהמשכורת שלו היא 60,000. הפונקציה =XLOOKUP(REGEX("^C.*"), A2:A11, B2:B11, FALSE, 2) מחזירה ״42״, הגיל של ״יעל״, העובדת הראשונה בטווח ששמה מתחיל באות ״י״. הפונקציה =INDEX(XLOOKUP(A2,A2:A11,B2:C11),2) מחזירה 71000, הערך השני מבערך שהוחזר על‑ידי XLOOKUP. |