=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) מחזירה את המחיר מהשורה שבה המוצר הוא E2 וגם הגודל הוא F2. כל השוואה בודקת כל שורה, ההכפלה שלהן נותנת 1 רק כששתיהן מתקיימות, ו-XLOOKUP מחפשת את ה-1 הזה. היא דורשת Excel 2021 או Microsoft 365; גרסת ה-INDEX MATCH בהמשך עובדת בכל גרסה.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
Tea ו-Large נפגשים בשורה 5, ולכן G2 מחזיר $3.00. בחרו Juice ו-Small: שוב $3.00, משורה אחרת. הוסיפו ארגומנט רביעי למקרה ששום שורה לא מתאימה לשניהם: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7,"No such item").
איך התנאים המוכפלים עובדים
A2:A7=E2 משווה כל מוצר ל-E2 ומחזירה שישה ערכי TRUE או FALSE. הכפלה של שתי רשימות כאלה הופכת TRUE ל-1 ו-FALSE ל-0, ושורה היא 1 רק אם היא 1 בשתיהן. עמודה D מציגה את הרשימה הזו, שנשפכת מנוסחה אחת.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
רק D5 הוא 1. שנו את F2 או את G2 וה-1 זז. כל קריטריון נוסף הוא עוד *(range=value), והתנאים לא חייבים להיות שוויון: *(C2:C7<3) מוסיף "מחיר מתחת ל-3". כל הטווחים חייבים לכסות את אותן שורות (A2:A7, B2:B7, C2:C7): אם לטווח ההחזרה יש גודל שונה מהתנאים, XLOOKUP מחזירה #VALUE!.
INDEX MATCH עם כמה קריטריונים
ל-Excel 2019 ומטה, MATCH יכולה לחפש את ה-1 באותו מערך ו-INDEX מחזירה את המחיר מהמיקום הזה.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
Coffee ו-Large הם מיקום 2 של המערך, ו-INDEX מחזירה $3.50. ב-Excel 2019 ומטה זו נוסחת מערך: לחצו Ctrl+Shift+Enter (במק Cmd+Shift+Enter) במקום Enter, ואקסל מציג אותה בתוך סוגריים מסולסלים. לחיצה על Enter רגיל שם מחזירה בדרך כלל #N/A או #VALUE!. ב-Excel 365 מספיק Enter. הצורה עם קריטריון אחד נמצאת בעמוד INDEX ו-MATCH.
חיבור הקריטריונים למפתח אחד
הדרך האחרת היא להפוך שני קריטריונים לאחד על ידי חיבור שלהם. VLOOKUP צריכה את הערכים המחוברים בעמודת עזר בתחילת הטבלה (העמוד של VLOOKUP מראה את הגרסה הזו). XLOOKUP יכולה לחבר את הטווחים בתוך הנוסחה, כך שלא צריך עמודת עזר.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
A2:A7&"|"&B2:B7 בונה שישה מפתחות כמו Juice|Large, ו-XLOOKUP מוצאת ביניהם את Juice|Large: $4.00. שימו מפריד בין החלקים. בלעדיו, "AB" ו-"C" מתחברים לאותו "ABC" כמו "A" ו-"BC", והחיפוש יכול להחזיר את השורה הלא נכונה.
אם הערך שאתם רוצים הוא מספר וכל צירוף מופיע פעם אחת, SUMIFS נותנת את אותה תשובה בלי מערך בכלל: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). היא מחזירה 0 במקום שגיאה כששום דבר לא מתאים, וזה יכול להסתיר שגיאת הקלדה.
החזרת כל ההתאמות עם FILTER
XLOOKUP ו-INDEX MATCH מחזירות את השורה המתאימה הראשונה. כשכמה שורות מתאימות ואתם רוצים את כולן, השתמשו ב-FILTER עם אותם תנאים.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
שלוש שורות הן North ו-Phone, ולכן F2 שופך את הרבעונים והמכירות שלהן לתוך F2:G4. שנו את A3 ל-South והרשימה מתקצרת לשתיים. אם שום שורה לא מתאימה, FILTER מחזירה #CALC!; ארגומנט שלישי כמו "None" מציג טקסט במקום. עוד אפשרויות נמצאות בעמוד של FILTER.
תרגול: שלושה קריטריונים
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
תורכם: החזירו ב-G4 את המכירות לאזור שב-G1, למוצר שב-G2 ולרבעון שב-G3.
שאלות נפוצות
איך משתמשים ב-XLOOKUP עם כמה קריטריונים?
הכפילו השוואה אחת לכל קריטריון וחפשו את 1: =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7). כל השוואה נותנת TRUE או FALSE לכל שורה, המכפלה היא 1 רק כשכולן TRUE, ו-XLOOKUP מחזירה את השורה הראשונה כזו.
איך עושים INDEX MATCH עם שני קריטריונים?
השתמשו באותם תנאים מוכפלים בתוך MATCH: =INDEX(C2:C7,MATCH(1,(A2:A7=E2)*(B2:B7=F2),0)). ב-Excel 2019 ומטה, אשרו אותה עם Ctrl+Shift+Enter (במק Cmd+Shift+Enter).
האם VLOOKUP יכולה להשתמש בשני קריטריונים?
לא ישירות. הוסיפו עמודת עזר בתחילת הטבלה שמחברת את שני הערכים, כמו =A2&"|"&B2, ואז חפשו את הערך המחובר: =VLOOKUP(E2&"|"&F2,helper_table,col,FALSE).
האם SUMIFS יכולה להחליף חיפוש עם שני קריטריונים?
כן, כשהערך הוא מספר וכל צירוף מופיע פעם אחת: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2). היא מחזירה 0 במקום #N/A כששום שורה לא מתאימה, ומחברת את הערכים אם צירוף מופיע פעמיים.
איך מחפשים עם קריטריונים של OR?
חברו את התנאים במקום להכפיל אותם: (A2:A7="Tea")+(A2:A7="Juice") הוא 1 או יותר כשאחד מהם מתקיים. חפשו ערך גדול מ-0, למשל עם =XLOOKUP(TRUE,((A2:A7="Tea")+(A2:A7="Juice"))>0,C2:C7).
