INDEX ו-MATCH עם מערכים מרובים

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

כיצד לחפש ערך על פני מערכים מרובים?

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

תחביר כללי

=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)

כדי לדעת את מנהיג קבוצה D השייכת למחלקה A, אנא העתק או הזן את הנוסחה שלהלן בתא G5, והקש Enter כדי לקבל את התוצאה:

=INDEX(CHOOSE(1,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

√ הערה: הסימני דולר ($) לעיל מציינים הפניות מוחלטות, מה שאומר שתיחומי השמות והקבוצות בנוסחה לא ישתנו כאשר תעביר או תעתיק את הנוסחה לתאים אחרים. לאחר הכנסת הנוסחה, גרור את ידית המילוי כלפי מטה כדי להחיל את הנוסחה על התאים מתחת, ולאחר מכן שנה את array_num בהתאם.

הסבר לנוסחה

=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

• CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): הפונקציה CHOOSE מחזירה את המערך ה1 מבין שלושת המערכים המפורטים בנוסחה. לכן היא תחזיר $B$5:$C$8, כלומר טווח הנתונים של מחלקת A.
• MATCH(F5,$B$5:$B$8,0): סוג ההתאמה 0 מכריח את הפונקציה MATCH להחזיר את מיקומו של התאום הראשון של קבוצה D, הערך בתא F5, במערך $B$5:$B$8, שהוא 4.
• INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2) = INDEX($B$5:$C$8,4,2): הפונקציה INDEX מחלצת את הערך בנקודת החיתוך של השורה הרביעית והעמודה השנייה של הטווח $B$5:$C$8, שהוא Emily.

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

=INDEX(CHOOSE(D5,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

√ הערה: המספרים 1, 2, 3 בעמודת העזר מציינים את array1, array2, array3 בתוך הפונקציה CHOOSE.

פונקציות קשורות

נוסחאות קשורות