חיפוש ערך התאמה קרובה ביותר עם מספר קריטריונים

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

כיצד לחפש ערך התאמה קרובה ביותר עם יותר מקריטריון אחד?

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

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

1. בחר תא ריק להצגת התוצאה, ואז העתק את הנוסחה הבאה אליו והקש על מקשי Ctrl + Shift + Enter כדי לקבל את התוצאה.

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

הערות: בנוסחה זו:

• D3:D8 הוא טווח העמודה שמכיל את התוצאה שאתם מחפשים;
• G5 מכיל את הקריטריון השני (מספר ניסיון 15) עליו תחפשו ערך;
• G4 מכיל את הקריטריון הראשון (מחשבים) עליו תחפשו ערך;
• B3:B8 הוא טווח התאים התואמים לקריטריון הראשון;
• C3:C8 הוא טווח התאים התואמים לקריטריון השני;
• המספר 1 הוא חיפוש מקורב, מה שאומר שאם לא נמצא את הערך המדויק, הוא ימצא את הערך הגדול ביותר שהוא קטן מהערך שחיפשתם;
• נוסחה זו חייבת להוזן כנוסחת מערך עם מקשי Ctrl + Shift + Enter.

איך הנוסחה הזו עובדת

ניתן לפרק את הנוסחה הזו למספר רכיבים:

• IF(B3:B8=G4,C3:C8): הפונקציה IF כאן מחזירה את התוצאה {9;13;FALSE;FALSE;FALSE;FALSE}, שמתקבלת מבדיקת הערכים ב-B3:B8 כדי לראות אם הם תואמים את הערך ב-G4. אם יש התאמה, אז מחזירים את הערך המתאים, אחרת מחזירים FALSE. כאן מוצאים שתי התאמות וארבע אי התאמות.
• נוסחת מערך =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): הפונקציה MATCH מוצאת את המיקום של המספר 15 (הערך ב-G5) בטווח C3:C8. מכיוון שלא ניתן למצוא את המספר 15, הוא מתאים לערך הקטן הבא 13. לכן התוצאה כאן היא 2.
• ו-=INDEX(D3:D8,2): הפונקציה INDEX מחזירה את הערך של התא השני בטווח D3:D8. לכן התוצאה הסופית היא Amy.

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

מאמרים קשורים