שלום לכולם,

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

נסתכל על הדוגמא הזו:

 

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

אם אנו רוצים שיוצג לנו נתון מסויים, לדוגמא, אם נבחר באוסנת, ונכתוב שאנו רוצים למצוא את התאמה מס' 2, אנחנו נראה שיחזור לנו ערך המכירות השני של אוסנת בטבלה, שזה 14:

כדי לפשט קצת את הנוסחה הזו , אנו מצרפים הסבר: היא מורכבת ע"ב נוסחת Index, ומשתמשת ב-Small  ו-Row כדי להחזיר את המיקום המדוייק של הערך שאותו אנחנו מחפשים.
יש לציין שזוהי נוסחת מערך(!!!), לכן חשוב לזכור לאחר שכותבים אותה יש ללחוץ ctrl+shift+enter ולא רק אנטר, ולוודא שאנחנו רואים את הנוסחה בתא בסוף, עטופה בסוגריים מסולסלים כמו בצילום המסך למעלה.
להלן התבנית של הנוסחה:

=Index(*range to bring value from*,Small(If(*range to search value*=*value to search*,row(*range to search value*)-*row number of header*,99999999),*Occurrence to return*),1)

 להלן הערכים בתבנית שיש לשנות, ביחד עם נתוני הדוגמא שמופיעים בדוגמא שלמעלה:
*range to bring value from* – טווח התאים/העמודה בה נמצא הנתון שאותו אנחנו מחזירים. (במקרה שלנו עמודת המכירות)

*range to search value* – טווח התאים/עמודה בה אנחנו מבצעים את החיפוש של הערך. (במקרה שלנו עמודת העובדים)

*value to search* – הערך אותו אנחנו מחפשים כדי למצוא התאמה בנתון הקודם. (במקרה שלנו שם העובד)

*row number of header* – מס' השורה בה נמצאת הכותרת של טווחי תאים (במקרה שלנו 1)

*occurrence to return* – מס' ההתאמה אותה אנחנו רוצים להחזיר. (במקרה שלנו 2)

איך זה עובד?
נוסחת המערך מחזירה מערך של מספרי השורות בהן נמצאה התאמה, ובעזרת הנוסחא Small מחזירה את מס' השורה בה נמצאת מס' ההתאמה הספציפי אותו אנו מחפשים.
לבסוף נוסחת ה- Index ביחד עם מס' השורה מחזירה לנו את הערך אותו חיפשנו.

אתם מוזמנים לשאול שאלות בדף "הכה את המומחה ? שאלות ותשובות":
עמוד שאלות ותשובות