כיצד להשתמש בפונקציית XLOOKUP החדשה והמתקדמת ב-Excel (10 דוגמאות)

XLOOKUP החדשה של Excel היא פונקציית החיפוש החזקה והפשוטה ביותר ש-Excel מציעה. לאחר מאמצים מתמשכים, מיקרוסופט סוף סוף שחררה אותה כדי להחליף את VLOOKUP, HLOOKUP, INDEX+MATCH ופונקציות חיפוש נוספות.

במדריך זה נציג את היתרונות של XLOOKUP, כיצד ניתן להשיג אותה וכיצד ליישם אותה לפתרון בעיות חיפוש שונות.

כיצד משיגים את XLOOKUP?

תחביר

דוגמאות

• דוגמה1: התאמה מדויקת
  • בצע התאמה מדויקת באמצעות XLOOKUP
  • רק כמה לחיצות לקבלת התאמה מדויקת

• דוגמה2: התאמה בקירוב
  • בצע התאמה בקירוב באמצעות XLOOKUP

• דוגמה3: התאמה עם תווי כלליים (Wildcard)
  • בצע התאמה עם תווי כלליים באמצעות XLOOKUP

• דוגמה4: חיפוש מימין לשמאל
  • בצע חיפוש מימין לשמאל באמצעות XLOOKUP
  • חפש ערך מימין לשמאל בכמה לחיצות פשוטות

• דוגמה5: חיפוש אנכי או אופקי
  • בצע חיפוש אנכי או אופקי באמצעות XLOOKUP

• דוגמה6: חיפוש דו-כיווני
  • בצע חיפוש דו-כיווני באמצעות XLOOKUP

• דוגמה7: התאמה אישית של הודעת ערך שלא נמצא
  • התאם אישית הודעת ערך שלא נמצא באמצעות XLOOKUP
  • התאם אישית את שגיאת #N/A עם אפשרות נוחה

• דוגמה8: ערכים מרובים
  • החזר ערכים מרובים באמצעות XLOOKUP

• דוגמה9: קריטריונים מרובים
  • בצע חיפוש מרובה קריטריונים באמצעות XLOOKUP
  • חיפוש מרובה תנאים בשיטה מהירה

• דוגמה10: מציאת ערך בהתאמה האחרונה
  • קבל את התוצאה האחרונה התואמת באמצעות XLOOKUP
  • חפש בקלות את הערך האחרון התואם עם כלי מדהים

הורד קובץ דוגמה של XLOOKUP

כיצד משיגים את XLOOKUP?

פונקציית XLOOKUP זמינה רק ב-Excel עבור Microsoft365, Excel2021 וגרסאות מאוחרות יותר, וכן ב-Excel עבור האינטרנט. אם אתה משתמש ב-Excel2019 או גרסה מוקדמת יותר, שקול לשדרג כדי לקבל גישה ל-XLOOKUP.

תחביר

הפונקציה מחפשת טווח או מערך ומחזירה את הערך של התוצאה הראשונה התואמת. התחביר הוא כך:

ארגומנטים:

1. Lookup_value (נדרש): הערך שאתה מחפש. הוא יכול להימצא בכל עמודה בטווח הטבלה (table_array).
2. Lookup_array (נדרש): המערך או הטווח שבו אתה מחפש את ערך החיפוש.
3. Return_array (נדרש): המערך או הטווח שממנו ברצונך לקבל את הערך.
4. If_not_found (אופציונלי): הערך שיוחזר כאשר לא נמצאה התאמה תקינה. ניתן להתאים אישית את הטקסט ב-[if_not_found] כדי להציג שאין התאמה.
   אם לא תוגדר הודעה מותאמת אישית, ערך ברירת המחדל שיחזור הוא #N/A.
5. Match_mode (אופציונלי): כאן ניתן להגדיר כיצד תתבצע ההתאמה בין lookup_value לערכים ב-lookup_array.
   • 0 (ברירת מחדל) = התאמה מדויקת. אם לא נמצאה התאמה, יוחזר #N/A.
   • -1 = התאמה מדויקת. אם לא נמצאה התאמה, יוחזר הערך הקטן הבא.
   • 1 = התאמה מדויקת. אם לא נמצאה התאמה, יוחזר הערך הגדול הבא.
   • 2 = התאמה חלקית. השתמש בתווי כלליים כמו *, ? ו-~ כדי לבצע התאמה עם תווים כלליים.
6. Search_mode (אופציונלי): כאן ניתן להגדיר את סדר החיפוש שיבוצע.
   • 1 (ברירת מחדל) = חפש את lookup_value מהפריט הראשון לאחרון ב-lookup_array.
   • -1 = חפש את lookup_value מהפריט האחרון לראשון. שימושי כאשר יש צורך לקבל את התוצאה האחרונה התואמת ב-lookup_array.
   • 2 = בצע חיפוש בינארי הדורש ש-lookup_array יהיה ממויין בסדר עולה. אם לא ממויין, התוצאה לא תהיה תקינה.
   • -2 = בצע חיפוש בינארי הדורש ש-lookup_array יהיה ממויין בסדר יורד. אם לא ממויין, התוצאה לא תהיה תקינה.

למידע מפורט על הארגומנטים, בצע את הצעדים הבאים:

1. הקלד את התחביר הבא בתא ריק, שים לב שיש להקליד רק צד אחד של הסוגריים.

2. לחץ על Ctrl+A, תופיע תיבת הנחיה המציגה את ארגומנטים הפונקציה. הצד השני של הסוגריים יושלם אוטומטית.

3. גלול את לוח הנתונים למטה, ותוכל לראות את כל ששת הארגומנטים של פונקציית XLOOKUP.

>>>

דוגמאות

אני בטוח שכעת שלטת בעיקרון הבסיסי של XLOOKUP. בוא נצלול ישר לדוגמאות המעשיות של XLOOKUP.

דוגמה1: התאמה מדויקת

בצע התאמה מדויקת באמצעות XLOOKUP

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

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

הקלד את הנוסחה הבאה בתא הריק F2 ולחץ על Enter כדי לקבל את התוצאה.

=XLOOKUP(E2,A2:A10,C2:C10)

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

רק כמה לחיצות לקבלת התאמה מדויקת

אולי אתה משתמש בגרסה ישנה של Excel ואין בכוונתך לשדרג ל-Excel2021 או Microsoft365 כרגע. במקרה כזה, אני ממליץ על אפשרות נוחה – "מצא נתונים בטווח". באמצעות אפשרות זו תוכל לקבל את התוצאה ללא נוסחאות מסובכות או גישה ל-XLOOKUP.

1. לחץ על התא שבו תרצה להציג את התוצאה התואמת.

2. עבור ללשונית "Kutools", לחץ על "עזר לנוסחאות", ולאחר מכן בחר "עזר לנוסחאות" מהרשימה הנפתחת.

3. בתיבת הדו-שיח של עזר לנוסחאות, הגדר כך:

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

4. לחץ על כפתור OK לקבלת התוצאה.

דוגמה2: התאמה בקירוב

בצע התאמה בקירוב באמצעות XLOOKUP

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

במקרה זה, עליך לדעת את שיעורי המס להכנסות העובדים שלך. בצד שמאל של הגיליון מופיעים מדרגות מס פדרליות לשנת2021. כיצד תוכל לקבל את שיעור המס לעובד בעמודה E? אל דאגה, בצע את הצעדים הבאים:

1. הקלד את הנוסחה הבאה בתא הריק E2 ולחץ על Enter לקבלת התוצאה.
לאחר מכן שנה את עיצוב התוצאה המוחזרת לפי הצורך.

=XLOOKUP(D2,B2:B8,A2:A8,,1)

>>>

√ הערה: הפרמטר הרביעי [If_not_found] הוא אופציונלי, ולכן דילגתי עליו.

2. כעת אתה יודע את שיעור המס של תא D2. כדי לקבל את שאר התוצאות, עליך להמיר את הפניות התאים של lookup_array ו-return_array למוחלטות.

• לחץ פעמיים על תא E2 כדי להציג את הנוסחה =XLOOKUP(D2,B2:B8,A2:A8,,1);
• בחר את טווח החיפוש B2:B8 בנוסחה, לחץ על F4 כדי לקבל $B$2:$B$8;
• בחר את טווח התוצאה A2:A8 בנוסחה, לחץ על F4 כדי לקבל $A$2:$A$8;
• לחץ על Enter לקבלת התוצאה של תא E2.

>>>

3. גרור את ידית המילוי למטה לקבלת כל התוצאות.

√ הערה:

• לחיצה על מקש F4 במקלדת מאפשרת לשנות את הפניית התא למוחלטת על ידי הוספת סימני דולר לפני השורה והעמודה.
• לאחר שהחלת הפניה מוחלטת על טווחי החיפוש והתוצאה, שינינו את הנוסחה בתא E2 לגרסה זו:

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

• כאשר אתה גורר את ידית המילוי למטה מתא E2, הנוסחאות בכל תא בעמודה E משתנות רק בהיבט של lookup_value.
  לדוגמה, הנוסחה ב-E13 כעת היא:

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

דוגמה3: התאמה עם תווי כלליים (Wildcard)

בצע התאמה עם תווי כלליים באמצעות XLOOKUP

לפני שנעמיק בפונקציית ההתאמה עם תווי כלליים של XLOOKUP, נבין קודם מהם תווי כלליים.

ב-Microsoft Excel, תווי כלליים הם תווים מיוחדים שיכולים להחליף כל תו אחר. הם שימושיים במיוחד כאשר רוצים לבצע חיפושים בהתאמה חלקית.

ישנם שלושה סוגי תווי כלליים: כוכבית (*), סימן שאלה (?), וטילדה (~).

• כוכבית (*) מייצגת כל מספר של תווים בטקסט;
• סימן שאלה (?) מייצג תו יחיד כלשהו בטקסט;
• טילדה (~) משמשת להפיכת תווי כלליים (*, ? ~) לתווים רגילים. יש להציב טילדה (~) לפני תו כללי כדי להשיג זאת;

ברוב המקרים, כאשר אנו מבצעים התאמה עם תווי כלליים ב-XLOOKUP, אנו משתמשים בתו הכוכבית (*). כעת נראה כיצד זה עובד בפועל.

נניח שיש לך רשימה של שווי שוק של50 החברות האמריקאיות הגדולות, ואתה רוצה לדעת את שווי השוק של כמה חברות אך שמות החברות מקוצרים – זהו תרחיש מושלם להתאמה עם תווי כלליים. עקוב אחריי שלב אחר שלב לביצוע הפעולה.

√ הערה: כדי לבצע התאמה עם תווי כלליים, חשוב להגדיר את הפרמטר החמישי [match_mode] ל-2.

1. הקלד את הנוסחה הבאה בתא הריק H3 ולחץ על Enter לקבלת התוצאה.

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

>>>

2. כעת אתה יודע את התוצאה של תא H3. כדי לקבל את שאר התוצאות, יש לקבע את lookup_array ו-return_array על ידי הצבת הסמן במערך ולחיצה על F4. לאחר מכן הנוסחה ב-H3 הופכת ל:

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. גרור את ידית המילוי למטה לקבלת כל התוצאות.

√ הערה:

• ערך החיפוש בנוסחה בתא H3 הוא "*"&G3&"*". אנו מחברים את תו הכוכבית (*) עם הערך G3 באמצעות סימן &.
• הפרמטר הרביעי [If_not_found] הוא אופציונלי, ולכן דילגתי עליו.

דוגמה4: חיפוש לשמאל

בצע חיפוש מימין לשמאל באמצעות XLOOKUP

אחת המגבלות של VLOOKUP היא שהוא מוגבל לחיפוש רק ימינה מעמודת החיפוש. אם תנסה לחפש ערכים שמאלה לעמודת החיפוש, תקבל את שגיאת #N/A. אל דאגה – XLOOKUP היא הפונקציה המושלמת לפתרון בעיה זו.

XLOOKUP נועדה לאפשר חיפוש ערכים שמאלה או ימינה לעמודת החיפוש, ללא מגבלות, ומותאמת לצרכי המשתמשים ב-Excel. בדוגמה הבאה נראה כיצד זה עובד.

נניח שיש לך רשימת מדינות עם קודי טלפון, ואתה רוצה לחפש את שם המדינה לפי קוד טלפון ידוע.

עלינו לחפש בעמודה C ולהחזיר את הערך מעמודה A. בצע את הצעדים הבאים:

1. הקלד את הנוסחה הבאה בתא הריק G2.

=XLOOKUP(F2,C2:C11,A2:A11)

2. לחץ על Enter לקבלת התוצאה.

√ הערה: פונקציית החיפוש לשמאל של XLOOKUP יכולה להחליף את Index ו-Match לחיפוש ערכים שמאלה.

חפש ערך מימין לשמאל בכמה לחיצות פשוטות

למי שלא רוצה לזכור נוסחאות, אני ממליץ על אפשרות שימושית – "חיפוש מימין לשמאל". באמצעות אפשרות זו תוכל לבצע חיפוש מימין לשמאל תוך שניות ספורות.

1. עבור ללשונית "Kutools" ב-Excel, מצא "חיפוש מתקדם", ולחץ על "חיפוש מימין לשמאל" מהרשימה הנפתחת.

2. בתיבת הדו-שיח "חיפוש מימין לשמאל", יש להגדיר כך:

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

3. לחץ על כפתור OK לקבלת התוצאה.

דוגמה5: חיפוש אנכי או אופקי

בצע חיפוש אנכי או אופקי באמצעות XLOOKUP

כמשתמשי Excel, ייתכן שאתם מכירים את הפונקציות VLOOKUP ו-HLOOKUP. VLOOKUP משמש לחיפוש אנכי בעמודה, ו-HLOOKUP משמש לחיפוש אופקי בשורה.

כעת XLOOKUP החדשה משלבת את שתיהן, כך שניתן להשתמש בתחביר אחד בלבד לביצוע חיפוש אנכי או אופקי. גאוני, נכון?

בדוגמה הבאה נמחיש כיצד ניתן להשתמש בתחביר אחד של XLOOKUP כדי לבצע חיפושים אנכיים או אופקיים.

לביצוע חיפוש אנכי, הקלד את הנוסחה הבאה בתא הריק E2 ולחץ על Enter לקבלת התוצאה.

=XLOOKUP(E1,A2:A13,B2:B13)

לביצוע חיפוש אופקי, הקלד את הנוסחה הבאה בתא הריק P2 ולחץ על Enter לקבלת התוצאה.

=XLOOKUP(P1,B1:M1,B2:M2)

כפי שניתן לראות, התחביר זהה. ההבדל היחיד בין שתי הנוסחאות הוא שבחיפוש אנכי מזינים עמודות, ובחיפוש אופקי מזינים שורות.

דוגמה6: חיפוש דו-כיווני

בצע חיפוש דו-כיווני באמצעות XLOOKUP

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

XLOOKUP יכולה לבצע חיפוש כפול, כלומר למצוא את נקודת החיתוך של שני ערכים. על ידי קינון XLOOKUP אחת בתוך אחרת, ה-XLOOKUP הפנימית מחזירה שורה או עמודה שלמה, ואז שורה או עמודה זו מוזנת ל-XLOOKUP החיצונית כ-return_array.

נניח שיש לך רשימת ציונים של תלמידים במקצועות שונים, ואתה רוצה לדעת את הציון של קים בכימיה.

נראה כיצד נשתמש ב-XLOOKUP המופלאה כדי לבצע זאת.

• נריץ את ה-XLOOKUP "הפנימית" כדי להחזיר ערכים של עמודה שלמה. XLOOKUP(H2,B1:E1,B2:E10) יכולה להחזיר טווח ציוני כימיה.
• נמקם את ה-XLOOKUP "הפנימית" בתוך ה-XLOOKUP "החיצונית" על ידי שימוש ב-XLOOKUP הפנימית כ-return_array בנוסחה המלאה.
• והנה הנוסחה הסופית:

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

• הקלד את הנוסחה לעיל בתא הריק H3 ולחץ על Enter לקבלת התוצאה.

או שתוכל לעשות זאת הפוך – השתמש ב-XLOOKUP "הפנימית" כדי להחזיר ערכים של שורה שלמה, כלומר כל ציוני המקצועות של קים. לאחר מכן השתמש ב-XLOOKUP "החיצונית" כדי לחפש את ציון הכימיה בין כל ציוני קים.

• הקלד את הנוסחה הבאה בתא הריק H4 ולחץ על Enter לקבלת התוצאה.

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

פונקציית החיפוש הדו-כיווני של XLOOKUP היא גם המחשה מושלמת ליכולת החיפוש האנכי והאופקי שלה. נסה בעצמך!

דוגמה7: התאמה אישית של הודעת ערך שלא נמצא

התאם אישית הודעת ערך שלא נמצא באמצעות XLOOKUP

בדומה לפונקציות חיפוש אחרות, כאשר לא נמצאה התאמה, תוחזר הודעת שגיאה #N/A. זה עלול לבלבל חלק מהמשתמשים. החדשות הטובות הן שניתן לטפל בשגיאה זו באמצעות הפרמטר הרביעי של פונקציית XLOOKUP.

באמצעות הפרמטר המובנה [if_not_found], ניתן להגדיר הודעה מותאמת אישית במקום תוצאת #N/A. הקלד את הטקסט הרצוי בפרמטר הרביעי (האופציונלי) והקף אותו במרכאות כפולות ("").

לדוגמה, העיר דנוור לא נמצאה, ולכן XLOOKUP מחזירה את הודעת השגיאה #N/A. אך לאחר שהתאמנו את הפרמטר הרביעי עם הטקסט "אין התאמה", הנוסחה תציג את הטקסט "אין התאמה" במקום הודעת השגיאה.

הקלד את הנוסחה הבאה בתא הריק F3 ולחץ על Enter לקבלת התוצאה.

=XLOOKUP(E2,A2:A11,C2:C11,"No Match")

התאם אישית את שגיאת #N/A עם אפשרות נוחה

כדי להחליף במהירות את שגיאת #N/A בהודעה מותאמת אישית, Kutools for Excel הוא כלי מושלם ב-Excel שיעזור לך. עם האפשרות המובנית החלף0 או #N/A בריק או בערך מסוים, תוכל להגדיר הודעת ערך שלא נמצא ללא נוסחאות מסובכות או גישה ל-XLOOKUP.

1. עבור ללשונית "Kutools" ב-Excel, מצא "חיפוש מתקדם", ולחץ על "החלף0 או #N/A בריק או בערך מסוים" מהרשימה הנפתחת.

2. בתיבת הדו-שיח "החלף0 או #N/A בריק או בערך מסוים", יש להגדיר כך:

• בסעיף "טווח הערכים שברצונך לחפש וטווח הפלט", בחר את טווח החיפוש וטווח הפלט;
• לאחר מכן בחר באפשרות "החלף0 או #N/A בערך מסוים", והזן את הטקסט הרצוי;
• בסעיף "אזור הנתונים", בחר את אזור הנתונים, ולאחר מכן ציין את "עמודת המפתח" ו"עמודת ההחזרה".

3. לחץ על כפתור OK לקבלת התוצאה. ההודעה המותאמת תוצג כאשר לא נמצאה התאמה.

דוגמה8: ערכים מרובים

החזר ערכים מרובים באמצעות XLOOKUP

יתרון נוסף של XLOOKUP הוא היכולת להחזיר ערכים מרובים בו-זמנית עבור אותה התאמה. הזן נוסחה אחת לקבלת התוצאה הראשונה, ושאר הערכים יישפכו אוטומטית לתאים הריקים הסמוכים.

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

הקלד את הנוסחה הבאה בתא הריק G2 ולחץ על Enter לקבלת כל התוצאות.

=XLOOKUP(F2,A2:A9,B2:D9)

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

לסיכום, פונקציית הערכים המרובים של XLOOKUP היא שיפור משמעותי לעומת VLOOKUP. אין צורך להגדיר כל עמודה בנפרד לכל נוסחה. 👍

דוגמה9: קריטריונים מרובים

בצע חיפוש מרובה קריטריונים באמצעות XLOOKUP

פיצ'ר חדש ומרשים נוסף של XLOOKUP הוא היכולת לחפש לפי מספר קריטריונים. הטריק הוא לחבר ערכי חיפוש וטווחי חיפוש עם אופרטור "&" בנוסחה. נמחיש זאת בדוגמה הבאה.

אנו צריכים לדעת את המחיר של אגרטל כחול בינוני. במקרה זה, שלושה ערכי חיפוש (קריטריונים) נדרשים למציאת התאמה. הקלד את הנוסחה הבאה בתא הריק I2 ולחץ על Enter לקבלת התוצאה.

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

√ הערה: XLOOKUP יכולה לעבד מערכים ישירות. אין צורך לאשר את הנוסחה עם Control + Shift + Enter.

חיפוש מרובה תנאים בשיטה מהירה

האם יש דרך מהירה וקלה יותר לבצע חיפוש מרובה קריטריונים מאשר XLOOKUP ב-Excel? Kutools for Excel מספקת אפשרות מדהימה – "חיפוש מרובה תנאים". עם אפשרות זו תוכל לבצע חיפוש מרובה קריטריונים בכמה לחיצות בלבד!

1. עבור ללשונית "Kutools" ב-Excel, מצא "חיפוש מתקדם", ולחץ על "חיפוש מרובה תנאים" מהרשימה הנפתחת.

2. בתיבת הדו-שיח "חיפוש מרובה תנאים", בצע את הפעולות הבאות:

• בסעיף "טווח הערכים שברצונך לחפש וטווח הפלט", בחר את טווח ערכי החיפוש וטווח הפלט;
• בסעיף "אזור הנתונים", בצע את הפעולות הבאות:
  • בחר את עמודות המפתח המתאימות שמכילות את ערכי החיפוש אחת אחת תוך כדי החזקת מקש Ctrl בתיבת "מפתח";
  • ציין את העמודה שמכילה את הערכים המוחזרים בתיבת "החזרה".

3. לחץ על כפתור OK לקבלת התוצאה.

√ הערה:

• האפשרות החלף ערכים שלא נמצאו (#N/A) בערך מסוים היא אופציונלית בתיבת הדו-שיח, ניתן להגדיר אותה או לא.
• מספר העמודות שנבחרו בתיבת עמודת המפתח חייב להיות שווה למספר העמודות שנבחרו בתיבת ערכי החיפוש, וסדר הקריטריונים בשתי התיבות חייב להיות תואם אחד לאחד.

דוגמה10: מציאת ערך בהתאמה האחרונה

קבל את התוצאה האחרונה התואמת באמצעות XLOOKUP

כדי למצוא את הערך האחרון התואם ב-Excel, יש להגדיר את הפרמטר השישי כך שיבצע חיפוש בסדר הפוך.

ברירת המחדל של מצב החיפוש ב-XLOOKUP היא1, כלומר חיפוש מהראשון לאחרון. היתרון של XLOOKUP הוא שניתן לשנות את כיוון החיפוש. XLOOKUP מציעה את הפרמטר האופציונלי [search mode] לשליטה על סדר החיפוש. פשוט הגדר את מצב החיפוש בפרמטר השישי ל--1, וכיוון החיפוש ישתנה מהאחרון לראשון.

ראה את הדוגמה הבאה. אנו רוצים לדעת את המכירה האחרונה של אמה במסד הנתונים.

הקלד את הנוסחה הבאה בתא הריק G2 ולחץ על Enter לקבלת התוצאה.

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

√ הערה: הפרמטרים הרביעי והחמישי הם אופציונליים ודילגנו עליהם כאן. הגדרנו רק את הפרמטר השישי ל--1.

חפש בקלות את הערך האחרון התואם עם כלי מדהים

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

1. עבור ללשונית "Kutools" ב-Excel, מצא "חיפוש מתקדם", ולחץ על "חיפוש מלמטה למעלה" מהרשימה הנפתחת.

2. בתיבת הדו-שיח "חיפוש מלמטה למעלה", יש להגדיר כך:

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

3. לחץ על כפתור OK לקבלת התוצאה.

√ הערה: האפשרות החלף ערכים שלא נמצאו (#N/A) בערך מסוים היא אופציונלית בתיבת הדו-שיח, ניתן להגדיר אותה או לא.

הורד קובץ דוגמה של XLOOKUP

XLOOKUP Examples.xlsx

מאמרים קשורים:

• כיצד להשתמש ב-INDEX ו-MATCH יחד ב-Excel?
• כיצד להחיל חיפוש עמום (Fuzzy Lookup) למציאת תוצאה בקירוב ב-Excel?
• כיצד להשתמש בנוסחת חיפוש דו-כיוונית ב-Excel?
• כיצד לבצע Vlookup לפי מספר קריטריונים ב-Excel?
• כיצד לבצע Vlookup של ערכים מימין לשמאל ב-Excel?