
לחיפוש ערכים עם INDEX ו- MATCH, תוך שימוש בקריטריונים מרובים, תוכל להשתמש בנוסחת מערך. בדוגמה המוצגת, הנוסחה ב- H8 היא:
{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}
הערה: זהו נוסחת מערך , ויש להזין אותו עם שליטה + shift + enter, למעט ב- Excel 365 .
הֶסבֵּרזו נוסחה מתקדמת יותר. ליסודות, ראה כיצד להשתמש ב- INDEX וב- MATCH .
מצטיין שגיאה # לא
בדרך כלל, נוסחת INDEX MATCH מוגדרת עם ערכת MATCH שתסתכל בטווח של עמודות אחת ותספק התאמה המבוססת על קריטריונים נתונים. ללא שרשור ערכים ב- טור עוזר או בנוסחה עצמה, אין דרך לספק יותר מקריטריונים אחד.
נוסחה זו פועלת סביב מגבלה זו באמצעות היגיון בוליאני ליצור מַעֲרָך של אחד ואפסים לייצוג שורות התואמות את כל 3 הקריטריונים, ולאחר מכן השתמש ב- MATCH כדי להתאים את הראשון שנמצא. המערך הזמני של האפסים והאפסים נוצר בעזרת קטע זה:
{= INDEX (E5:E11, MATCH (1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0))}
כאן אנו משווים את הפריט ב- H5 מול כל הפריטים, הגודל ב- H6 מול כל הגדלים והצבע ב- H7 מול כל הצבעים. התוצאה הראשונית היא שלושה מערכים של תוצאות TRUE/FALSE כך:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
עֵצָה: השתמש ב- F9 כדי לראות תוצאות אלו . פשוט בחר ביטוי בשורת הנוסחאות והקש F9.
פעולת המתמטיקה (כפל) הופכת את ערכי TRUE FALSE לשניות 1 ו 0:
כיצד להפוך את הפסקה להצטיין
{TRUETRUETRUEFALSEFALSEFALSETRUE}*{FALSEFALSETRUEFALSEFALSETRUEFALSE}*{TRUEFALSETRUEFALSEFALSEFALSETRUE}
לאחר הכפל, יש לנו מערך יחיד כזה:
{1110001}*{0010010}*{1010001}
אשר מוזנת לפונקציית MATCH כמערך החיפוש, עם ערך חיפוש של 1:
{0010000}
בשלב זה, הנוסחה היא נוסחה סטנדרטית של INDEX MATCH. הפונקציה MATCH מחזירה 3 ל- INDEX:
MATCH (1,{0010000})
ו- INDEX מחזירה תוצאה סופית של $ 17.00.
מערך ויזואליזציה
המערכים שהוסברו לעיל יכולים להיות קשים לדמיון. התמונה למטה מציגה את הרעיון הבסיסי. העמודות B, C ו- D מתאימות לנתונים בדוגמה. עמודה F נוצרת על ידי הכפלת שלוש העמודות יחד. זהו המערך שנמסר ל- MATCH.
גרסה שאינה מערך
אפשר להוסיף נוסחה נוספת ל- INDEX, תוך הימנעות מהצורך להיכנס כנוסחת מערך עם הפקודה + shift + enter:
= INDEX (E5:E11,3)
הפונקציה INDEX יכולה להתמודד עם מערכים באופן מקורי, כך שה- INDEX השני מתווסף רק כדי 'לתפוס' את המערך שנוצר עם פעולת ההיגיון הבוליאני ולהחזיר את אותו מערך שוב ל- MATCH. לשם כך, INDEX מוגדר עם אפס שורות ועמודה אחת. הטריק בשורת האפס גורם ל- INDEX להחזיר עמודה 1 מהמערך (שהיא ממילא כבר עמודה אחת).
מדוע שתרצה את גרסת ה- non-array? לפעמים אנשים שוכחים להזין נוסחת מערך עם הפקודה + shift + enter והנוסחה מחזירה תוצאה לא נכונה. אז, נוסחה שאינה מערך היא יותר 'חסינת כדורים'. עם זאת, הפשרה היא נוסחה מורכבת יותר.
הערה: ב Excel 365 , אין צורך להזין נוסחאות מערך בצורה מיוחדת.
כיצד להוסיף שורה כוללת ב- Excelקבצים מצורפים
