חיפוש מרובה קריטריונים עם INDEX ו-MATCH

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

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

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

תחביר כללי

=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

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

כדי למצוא את המוצר שהוא לבן ובעל גודל בינוני במחיר של $18, אנא העתק או הזן את הנוסחה למטה בתא H8, ולאחר מכן לחץ Ctrl + Shift + Enter כדי לקבל את התוצאה:

=INDEX(B5:B10,MATCH(1,("White"=C5:C10)*("Medium"=D5:D10)*(18=E5:E10),0))

או השתמש בהפנייה לתא כדי להפוך את הנוסחה לדינמית:

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

הסבר לנוסחה

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

• (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): הנוסחה משווה את הצבע בתא h3 נגד כל הצבעים בטווח C5:C10; משווה את הגודל ב H5 נגד כל הגודלים ב D5:D10; משווה את המחיר ב H6 נגד כל המחירים ב E5:E10. התוצאה הראשונית נראית כך:
  {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
  הכפלת TRUE ו-FALSE תהפוך אותם ל-1-ים ו-0-ים:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  לאחר הכפלה, נקבל מערך יחיד כזה:
  {0;0;0;0;1;0}.
• MATCH(1,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0),0) = MATCH(1,MATCH(1,{0;0;0;0;1;0},0),0): match_type 0 דורש מהפונקציה MATCH למצוא התאמה מדויקת. הפונקציה תחזיר את מיקום ה1 במערך {0;0;0;0;1;0}, שהוא 5.
• INDEX(B5:B10B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10B5:B10,5): הפונקציה INDEX מחזירה את הערך החמשי בטווח מזהי המוצר B5:B10, שהוא 30005.

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

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