התאמה מקורבת דו-כיוונית עם קריטריונים מרובים

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

כיצד לבצע התאמה מקורבת דו-כיוונית עם קריטריונים מרובים ב-Excel?

כדי לגלות את גודל הבגד עבור אישה שגובהה 165.5 ס"מ ומשקלה 55 ק"ג לפי הטבלה שלמעלה, ניתן להוסיף שתי פונקציות MATCH לנוסחה: אחת עם פונקציית IF מקבלת את מספר השורה (מין וגובה), השנייה מקבלת את מספר העמודה (משקל). לאחר מכן INDEX ימצא את הגודל המתאים לפי הקואורדינטות. (שימו לב שמספר השורה קודם למספר העמודה בנוסחת INDEX.)

תחביר כללי

=INDEX(return_range,MATCH(lookup_value1,IF(lookup_array2=lookup_value2,lookup_array1),match_type),MATCH(lookup_value3,lookup_array3,match_type))

√ הערה: זוהי נוסחה מערך הדורשת להקליד עם Ctrl + Shift + Enter.

כדי לגלות את גודל הבגד עבור האישה שגובהה 165.5 ס"מ ומשקלה 55 ק"ג, אנא העתק או הזן את הנוסחה שלהלן בתא M9, והקש Ctrl + Shift + Enter כדי לקבל את התוצאה:

=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))

הסבר לנוסחה

=INDEX(D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1))

• IF(B5:B16=M5,C5:C16): הפונקציה IF בודקת כל ערך בB5:B16 אם הם תואמים את הערך בתא M5, Female. אם כן, הפונקציה IF תשאיר את הגבהים המתאימים המפורטים בC5:C16; אם לא, IF תחזיר FALSEs. הנוסחה תחזיר מערך כזה: {157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.
• MATCH(M6,MATCH(M6,IF(B5:B16=M5,C5:C16),1),1) = MATCH(M6,MATCH(M6,{157;160;162.5;165;167.5;170;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},1),1): הסוג ההתאמה 1 מכריח את הפונקציה MATCH להחזיר את המיקום של הערך הגדול ביותר שהוא קטן או שווה ל-165.5 (הערך בתא M6) במערך. לכן, MATCH תחזיר 4, המיקום של הערך 165.
• MATCH(M7,D4:J4,1): הפונקציה MATCH מחזירה 3, מכיוון שהערך הגדול ביותר שהוא קטן או שווה לערך ב-M7, 55, נמצא במקום ה-3 של הטווח D4:J4.
• INDEX(D5:J16D5:J16,MATCH(M6,IF(B5:B16=M5,C5:C16),1),MATCH(M7,D4:J4,1)) = INDEX(D5:J16D5:J16,4,3): הפונקציה INDEX מחלצת את הערך בנקודת החיתוך של השורה הרביעית והעמודה השלישית בטווח הגודל D5:J16, שהוא הערך בתא F8, M.

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

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