XLOOKUP יודע להחזיר כתובת, ולא רק ערך!

כולם כבר מכירים את נפלאות הפונקציה XLOOKUP, כפונקציה הידידותית והמשופרת שמחליפה את Vlookup הוותיקה והטובה (כן, מודה, אני עדיין משתמשת בה…)
אבל, לרוב נשתמש בה כדי להחזיר ערך, לדוגמה:

לפנינו טבלת תאריכים וסכומים, אם ארצה להחזיר את הסכום עבור ה 4/1/2023,
אשתמש ב XLOOKUP כך:

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

בואו נתחיל בדוגמה פשוטה:

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

=ROW(XLOOKUP(D2,A2:A13,B2:B13))

ואם ארצה את העמודה, אשתמש בפונקציה

=COLUMN(XLOOKUP(D2,A2:A13,B2:B13))

באותה הדרך אפשר לשלוף את כתובת התא המלאה שבו נמצא הערך.
לצורך כך נשתמש בפונקציה ADDRESS, כשאת מספר השורה אשלוף מתוצאת הפונקציה xlookup, ואילו העמודה קבועה – B, ולכן אקליד את המס' 2:

הערה חשובה – אני אישית הייתי משתמשת בפונקציה MATCH או XMATCH למציאת המיקום של הנתון, אבל כדאי שתכירו גם את הדרך הזאת.
וכדאי שתשימו לב להבדל נוסף – MATCH תחזיר את המיקום היחסי בתוך הטבלה עצמה,
כך שאם תסמנו החל משורה 2, תקבלו את התוצאה 4, משום שהערך נמצא בשורה הרביעית בטווח המסומן.
הפונקציה ROW תחזיר לנו את מספר השורה המוחלט, שהוא 5.
במה תשתמשו? נתון לשיקולכם (:

ובואו נראה עוד דוגמה

אני רוצה לקבל את הערך שנמצא בתא שמתחת לתא שבו נמצא התאריך 4/1/2023, ובמקרה שלנו, אני רוצה לקבל את הערך 143
ראינו שהפונקציה XLOOKUP החזירה לנו תוצאה מספרית,
אבל אם נעטוף אותה, לדוגמה, בפונקציה OFFSET, שמקבלת כתובת של תא ומחזירה ערכים בהיסט:

אז למרות שהחלק הפנימי של הפונקציה, כשהוא עומד לבדו, מחזיר מספר (173 בדוגמה שלנו),
הפונקציה יודעת להתייחס לכתובת, לרדת תא אחד למטה ולהחזיר את הערך שנמצא שם!

ועוד דוגמה אחת:

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

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

במקרה שלנו, קיבלנו את התוצאה 302, שהיא הסכום של 173+129
אבל, וכאן הטריק הגדול, אם אחליף את הפסיק, שבעצם אומר לאקסל לסכום את שני הארגומנטים, בנקודתיים, שאומרות לאקסל שמדובר בטווח,
אז למרות שהערך הגלוי הוא הסכום, אקסל יודע לקחת את הכתובת שנשמרה בזכרון, ולהתייחס אליה בנוסחה!
בואו נראה:

מה בעצם קיבלנו כאן? את הסכום של כל הערכים שבין ה 4/1 ועד ה 10/1, כלומר – את כל הטווח שבין B4:B11!

אז שוב, מה לעזאזל קורה כאן?

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

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