אין כמעט משתמש באקסל שלא שמע על פונקציית VLOOKUP . אך האם יצא לכם להכיר או להשתמש באחותה הפחות מפורסמת (שלא בצדק!), הלא היא פונקציית Index Match?

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

(שימו לב – לפונקציה זו יש תרגיל שהוכן במיוחד עבור קוראי אקסל מאסטר – להורדת תרגיל Index Match לחצו כאן)

למה בעצם להשתמש ב- Index Match?

השאלה הראשונה והחשובה שצריך לשאול היא – מה כל כך מיוחד בפונקציה הזו (שהיא בעצם שילוב של שתי פונקציות – פונקציית Index ופונקציית Match – כפי שיוסבר בהמשך)?

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

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

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

למרבה הצער – אין אפשרות לפתור את המשימה הזו עם VLOOKUP – זאת משום שהעמודה שבה אנו מחפשים את השם היא D, ואילו העמודה שממנה אנו מבקשים להחזיר את התוצאה היא C. הואיל ו- C נמצאת לפני D – פונקציית VLOOKUP תחזיר לנו שגיאה כשננסה להחזיר את ת.ז של יחיאל. הדרך שבה רובנו משתמשים על מנת לעקוף בעיה זו, היא להזיז את עמודת ת.ז כך שתופיע אחרי עמודת השם, או לחילופין – ליצור עמודת עזר של ת.ז. שתופיע אחרי סוף הטבלה (למשל, בעמודה F). זוהי דרך התמודדות לגיטימית, אך היא עלולה ליצור סיבוך ובלבול בגיליון העבודה, ולכן הדרך ה”נקייה” לעשות זאת תהיה עם שילוב הפונקציות Index + Match – שאינן תלויות במיקום יחסי.

יתרון שלישי – ניסיתם להוסיף עמודה / למחוק עמודה מאמצע טבלת החיפוש שלכם? ברכותינו – ה- VLOOKUP שלכם יפסיק לעבוד! הבה נראה דוגמא –

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

עד כאן, הכל עובד בסדר. כעת – נתבקשתם להוסיף בין עמודה D לעמודה E עמודה חדשה – “שם משפחה”. שימו לב מה קורה עכשיו:

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

כעת, שימו לב מה קורה אם במקום להוסיף עמודה, היינו מוחקים את עמודת השם:

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

איך נמנעים מכל זה? ניחשתם נכון – על ידי פונקציית Index Match! 

אז איך פונקציית Index Match עובדת בכלל?

כפי שכבר כתבנו – מדובר בשילוב של שתי פונקציות – פונקציית Index ופונקציית Match. לחוד הן משעממות, אך ביחד – הן צוות מנצח! הדרך הטובה ביותר להסביר את דרך פעולתן היא קודם כל להבין מה כל אחת עושה בנפרד:

פונקציית Index

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

להלן מבנה הפונקציה:

INDEX(Array,Row number,[Column number])

Array – הטווח המבוקש. יכול להיות שורה בודדת / עמודה בודדת / מספר שורות ועמודות.

Row Number – מספר השורה היחסי בתוך המערך. בשורה זו נמצא ערך התא שאנו רוצים להחזיר.

Column Number – מספר העמודה היחסי בתוך המערך. בעמודה זו נמצא ערך התא שאנו רוצים להחזיר. שימו לב שפרמטר זה מוקף בסוגריים מרובעים – אשר משמעותם ב”אקסלית” היא שמדובר ברכיב לא הכרחי שניתן להסתדר בלעדיו.

למעשה, במידה והטווח עמו אנו עובדים הוא שורה/עמודה בודדת – אין צורך להתייחס לפרמטר Column Number, משום שאקסל יתייחס למספר שמופיע בפרמטר Row Number בהתאם לסוג הטווח. אם מדובר בטווח של שורה בודדת, הספרה תייצג מיקום יחסי של עמודה, ואילו אם מדובר בטווח של עמודה בודדת, הספרה תייצג מיקום יחסי של שורה. רק אם מדובר של יותר משורה ועמודה אחת – יהיה צורך להזין את שני הפרמטרים. ראו דוגמא להלן:

פונקציית Match

פונקציית Match מקבלת ערך מסוים וטווח מסוים, ומחזירה מהו המיקום היחסי של הערך בתוך הטווח. שימו לב – הטווח יכול להיות שורה / עמודה בודדת בלבד.

להלן מבנה הנוסחה:

Match(Lookup value,Lookup Array,[Match Type])

Lookup Value – הערך אותו אנו מחפשים

Lookup Array – השורה / העמודה בה אנו מעוניינים למצוא את מיקומו של הערך

Match Type – סוג ההתאמה. ישנן 3 אפשרויות: 0, 1 ו 1- (מינוס 1). 0 -> התאמה מלאה. 1 / 1- -> התאמה חלקית. ברירת המחדל ב- 99.9% מהמקרים תהיה להשתמש בהתאמה מלאה (0). שימו לב – על אף ש- Match Type הינו פרמטר אופציונלי – משמע אפשר להשתמש בפונקציה מבלי להזין בו דבר – אנו בכל זאת ממליצים להזין בו 0 תמיד. הסיבה לכך נעוצה בעובדה שברירת המחדל של אקסל היא התאמה חלקית דווקא, משמע ישנו סיכוי (קטן, אך ממשי) שאם תוותרו על ה- 0, תקבלו תוצאות שגויות שנובעות מהתאמה חלקית ולא מלאה. אם הכתוב נשמע לכם סינית, נסכם זאת בקצרה – אל תשמיטו את ה- 0 בסוף הפונקציה! 

להלן דוגמא לשימוש בפונקציית MATCH:

Index + Match – טובים השניים מן האחד!

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

מבנה פונקציית Index Match הוא כדלקמן:

INDEX(Array,MATCH(Lookup Value,Lookup Array,Lookup Type))

וכעת נסביר בעברית מה כל הטוב הזה אומר!

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

Lookup Value – זהו הערך אותו אנו מחפשים באמצעות פונקציית ה- MATCH

Lookup Array – זהו הטווח שבו נמצא הערך שאנו מחפשים באמצעות פונקציית ה- MATCH.

Lookup Type – זוהי סוג ההתאמה המבוקשת. כפי שציינו מקודם, ברירת המחדל שלנו תהיה 0 – התאמה מלאה.

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

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

עובדת בונוס – האם שמתם לב שהערך שביקשנו להחזיר נמצא בעמודה שממוקמת לפני העמודה שבה הערך אותו חיפשנו? ב- VLOOKUP זה לא היה עובד!

דגשים

חשוב לזכור את הדברים הבאים כשמשתמשים בשילוב של הפונקציות הנ”ל:

1. לא לשכוח לקבע את הטווחים במידה ותעשו שימוש בגרירה/העתקה של הפונקציות הנ”ל.
2. הקפידו שתהיה הקבלה בין הטווח שבו הערך שרוצים להחזיר, לטווח שבו הערך שרוצים למצוא. אם הגדרתם טווח החזרה B2:B6, הקפידו שטווח החיפוש יקביל – C2:C6 או D2:D6, אך לא C3:C9 למשל.
3. הקפידו לשים 0 בפונקציית MATCH היכן שאתם נשאלים לגבי סוג ההתאמה.

INDEX MATCH MATCH – חיפוש דו ממדי

(אם הגעתם עד לכאן, סימן שאתם באמת רוצים להיות תותחי אקסל… 🙂 )

מלבד האפשרות לתפקד כתחליף לפונקציית VLOOKUP, ניתן להשתמש בשילוב הפונקציות INDEX ו- MATCH כדי לבצע חיפוש דו ממדי – כלומר למצוא ערך בטבלה כאשר איננו יודעים באיזו שורה וגם באיזו עמודה.

האם אתם זוכרים שבתחילת המדריך הסברנו שפונקציית INDEX יכולה לשמש להחזרת תוצאה בטווח המורכב מיותר משורה/עמודה אחת? לשם ניצול היכולת הזו, אנו נשתמש ב- Index Match Match. מלבד ה- Match על מספר השורה, עתה נבקש גם Match על מספר העמודה. שני ה- Matchים הנ”ל יחזירו לנו נ.צ. שבו בעצם נמצא התא שאנו מעוניינים להחזיר. שימו לב לדוגמא הבאה:

 

כפי שניתן לראות – ה- MATCH הראשון מחזיר את התוצאה 3 (מיקום חודש מרץ), ואילו ה- MATCH השני מחזיר את התוצאה 3 (מיקום שנת 2017). ביחד נוצרת הפניה לתא הממוקם במיקום היחסי 3,3 בטבלה הכחולה, ולכן התשובה שתתקבל היא “גילי”. החידוש לעומת INDEX MATCH רגיל טמון בכך שעתה ניתן לשנות את תאים B14 ו- C14 (למשל להזין במקום את חודש אפריל 2015) ולקבל תשובות בהתאם לחודש והשנה שביקשנו, מבלי לעשות כל שינוי בטווחים שהגדרנו לטבלה! 

תרגול פונקציית INDEX MATCH

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