=XLOOKUP(F2,A2:A6,C2:C6) מחפשת את הערך שב-F2 ב-A2:A6 ומחזירה את הערך מאותה שורה של C2:C6. כברירת מחדל היא מחפשת התאמה מדויקת, עמודת החיפוש יכולה להיות בכל מקום, והיא דורשת Excel 2021 או Microsoft 365 (ב-Excel 2019 ומטה השתמשו ב-INDEX וב-MATCH). הקלידו מוצר אחר ב-F2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Bread | $2.40 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
לחצו על G2: טווח החיפוש וטווח ההחזרה מסומנים כל אחד בנפרד. שנו את C2:C6 ל-B2:B6 ו-G2 יחזיר את הקטגוריה. אין מספר עמודה לספור, ולכן הוספת עמודה בין A ל-C לא שוברת את הנוסחה: אקסל מזיז את שני הטווחים.
התחביר של XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| ארגומנט | מה הוא עושה | ברירת מחדל |
|---|---|---|
lookup_value | הערך שמחפשים. | חובה |
lookup_array | העמודה (או השורה) שבה מחפשים. | חובה |
return_array | העמודה, השורה או הבלוק שמהם מחזירים. באותו גובה כמו lookup_array. | חובה |
if_not_found | מה להציג כששום דבר לא מתאים. | #N/A |
match_mode | 0 מדויקת, -1 מדויקת או הקטן הבא, 1 מדויקת או הגדול הבא, 2 תווים כלליים. | 0 |
search_mode | 1 מהראשון לאחרון, -1 מהאחרון לראשון, 2 ו--2 חיפוש בינארי על נתונים ממוינים. | 1 |
רק שלושת הראשונים הם חובה. כדי לדלג על ארגומנט אופציונלי ולהגדיר ארגומנט מאוחר יותר, השאירו אותו ריק בין פסיקים: =XLOOKUP(F2,A2:A6,C2:C6,,0,-1) מגדירה את search_mode ומשאירה את if_not_found בברירת המחדל שלו.
החזרת כמה עמודות בבת אחת
תנו ל-XLOOKUP טווח החזרה ברוחב של כמה עמודות והשורה כולה חוזרת. התוצאה נשפכת לתאים שליד הנוסחה.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Category | Price | Stock |
| 2 | Apple | Fruit | $1.20 | 40 |
| 3 | Pear | Fruit | $1.50 | 25 |
| 4 | Carrot | Vegetable | $0.80 | 60 |
| 5 | Bread | Bakery | $2.40 | 15 |
| 6 | Milk | Dairy | $1.10 | 30 |
| 7 | Look for | Carrot | ||
| 8 | Result | Vegetable | $0.80 | 60 |
נוסחה אחת ב-B8 ממלאת את B8:D8 ב-Vegetable, $0.80 ו-60. הקלידו משהו ב-C8 ו-B8 יציג #SPILL!, כי לתוצאה אין מקום; מחקו אותו והתוצאה חוזרת. כדי להחזיר את העמודות בסדר אחר, עטפו את טווח ההחזרה ב-CHOOSECOLS: =XLOOKUP(B7,A2:A6,CHOOSECOLS(B2:D6,3,1)) נותנת את Stock, ואחריו את Category.
XLOOKUP שמאלה, והודעה כששום דבר לא מתאים
עמודת החיפוש לא חייבת להיות ראשונה. כאן XLOOKUP מחפשת את המחירים בעמודה C ומחזירה את שם המוצר מעמודה A, ו-VLOOKUP לא יכולה לעשות את זה. הארגומנט הרביעי אומר מה להציג כשלאף מוצר אין את המחיר הזה.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Price | Product | |
| 2 | Apple | Fruit | $1.20 | 40 | $2.40 | Bread | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
$2.40 מחזיר Bread. שנו את F2 ל-3 ו-G2 יציג "No product" במקום #N/A. "" כארגומנט הרביעי מציג תא שנראה ריק. if_not_found מכסה רק את "לא נמצא": טווח החזרה בגובה הלא נכון עדיין נותן #VALUE!, וזה מה שרוצים לראות.
מציאת ההתאמה האחרונה
XLOOKUP מחזירה את ההתאמה הראשונה מלמעלה. הגדירו את search_mode, הארגומנט השישי, ל--1 והיא מחפשת מלמטה, ולכן מחזירה את ההתאמה האחרונה: ההזמנה האחרונה, המחיר העדכני ביותר, הסטטוס האחרון.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | First | Last | |
| 2 | 2026-03-02 | Ben | 120 | Ben | 120 | 60 | |
| 3 | 2026-03-05 | Ana | 80 | ||||
| 4 | 2026-03-09 | Ben | 45 | ||||
| 5 | 2026-03-12 | Cara | 200 | ||||
| 6 | 2026-03-20 | Ben | 60 | ||||
| 7 | 2026-03-24 | Ana | 95 |
ההזמנה הראשונה של Ben היא 120 והאחרונה היא 60. שנו את E2 ל-Ana: 80 ו-95. זה מסתמך על כך שהשורות מסודרות לפי תאריך. אם הן לא, חפשו במקום זאת את התאריך האחרון של הלקוח: =XLOOKUP(1,(B2:B7=E2)*(A2:A7=MAXIFS(A2:A7,B2:B7,E2)),C2:C7).
התאמה משוערת: הקטן הבא או הגדול הבא
match_mode -1 מחזיר התאמה מדויקת או, אם אין כזו, את הערך הקטן הבא. זה הכלל לטווחים: רמת עמלה, מדרגת מס, ציון. בניגוד ל-VLOOKUP עם TRUE, הטבלה לא חייבת להיות ממוינת. הרמות למטה מופיעות בכוונה בלי סדר מסוים.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 5000 | 5% | Ana | 750 | 0% | |
| 3 | 0 | 0% | Ben | 4,200 | 3% | |
| 4 | 10000 | 8% | Cara | 5,000 | 5% | |
| 5 | 1000 | 3% | Dev | 12,500 | 8% |
ה-4,200 של Ben נופל בין 1,000 ל-5,000, ולכן הוא מקבל את ה-3% של רמת ה-1,000. ה-5,000 של Cara הוא התאמה מדויקת, 5%. match_mode 1 עובד בכיוון ההפוך, מדויקת או הגדול הבא, וזה עונה על "הקופסה הקטנה ביותר שמתאימה" או "חלון המשלוח הבא": =XLOOKUP(18,{5;12;25;50},{"S";"M";"L";"XL"},,1) מחזירה L.
XLOOKUP עם תווים כלליים
match_mode 2 הופך את * (כל תווים) ואת ? (תו אחד) לתווים כלליים. בלעדיו XLOOKUP מחפשת את התווים עצמם, וזה ההפך מ-VLOOKUP (שההתאמה המדויקת שלה מקבלת תווים כלליים) והסיבה הרגילה לכך ש-XLOOKUP עם תווים כלליים מחזירה #N/A או את טקסט ה-if_not_found שלה:
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None") None: no product is named *coffee*
=XLOOKUP("*coffee*",A2:A6,C2:C6,"None",2) 2.9, the price of Iced coffee
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
"coffee" מוצא קודם את Iced coffee, $2.90. הוסיפו -1 כארגומנט שישי והיא מוצאת את Coffee beans, $8.50. כמו בכל חיפוש באקסל, ההתאמה מתעלמת מגודל האותיות. כדי למצוא כוכבית או סימן שאלה אמיתיים ב-match_mode 2, שימו לפניהם טילדה: "~*".
XLOOKUP דו־כיווני
XLOOKUP שמחזירה שורה שלמה יכולה לשמש כטווח ההחזרה של XLOOKUP שנייה. הפנימית בוחרת את השורה לפי אזור, והחיצונית בוחרת מתוך השורה הזו את העמודה של החודש.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 4,200 | 3,900 | 4,800 |
| 3 | South | 3,100 | 3,600 | 3,300 |
| 4 | East | 5,200 | 4,700 | 5,600 |
| 5 | West | 2,800 | 3,000 | 3,400 |
| 6 | Region | South | ||
| 7 | Month | Feb | ||
| 8 | Sales | 3,600 |
XLOOKUP(B6,A2:A5,B2:D5) מחזירה את השורה של South, 3100, 3600 ו-3300. ה-XLOOKUP החיצונית מוצאת את Feb ב-B1:D1 ולוקחת מהשורה הזו את הערך המתאים: 3,600. בחרו אזור וחודש אחרים ב-B6 וב-B7. הגרסה של אותו חיפוש עם INDEX ו-MATCH נמצאת בעמוד INDEX ו-MATCH.
XLOOKUP באקסל ישן וב-Google Sheets
XLOOKUP קיימת ב-Excel 2021, Excel 2024, Microsoft 365, Excel לאינטרנט ובאפליקציות לנייד. אם פותחים קובץ שמשתמש בה ב-Excel 2019 ומטה, הנוסחאות מציגות #NAME? ברגע שהן מחושבות מחדש. כשקובץ צריך לעבוד בכל מקום, כתבו את החיפוש עם INDEX ו-MATCH, שכל גרסה מבינה:
=XLOOKUP(F2, A2:A6, C2:C6, "Not found")
=IFNA(INDEX(C2:C6, MATCH(F2, A2:A6, 0)), "Not found")
ב-Google Sheets יש XLOOKUP מאז 2022, עם אותם ארגומנטים. להשוואה של ההבדלים זה לצד זה, ראו VLOOKUP מול XLOOKUP. כדי להתאים לפי שתי עמודות בבת אחת (מוצר וגודל, שם ותאריך), התבנית =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6) מוסברת בעמוד חיפוש עם כמה קריטריונים.
תרגול: מחיר, או "Not found"
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | ||
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
תורכם: ב-G2, החזירו את המחיר של המוצר שב-F2, או את הטקסט Not found כשהוא לא ברשימה.
תרגול: הנחה לפי גודל הזמנה
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order from | Discount | Order | Discount | |
| 2 | $0 | 0% | $320 | ||
| 3 | $100 | 5% | |||
| 4 | $250 | 10% | |||
| 5 | $500 | 15% |
תורכם: כל הנחה חלה מסכום ההזמנה שלה ומעלה. ב-E2, השתמשו ב-XLOOKUP כדי להחזיר את ההנחה עבור סכום ההזמנה שב-D2.
שאלות נפוצות
איך משתמשים ב-XLOOKUP באקסל?
תנו לה שלושה ארגומנטים: מה למצוא, העמודה שבה מחפשים, והעמודה שממנה מחזירים. =XLOOKUP("Pear",A2:A6,C2:C6) מוצאת את Pear ב-A2:A6 ומחזירה את הערך מאותה שורה של C2:C6. היא מחפשת התאמה מדויקת אלא אם אומרים לה אחרת.
באילו גרסאות של אקסל יש XLOOKUP?
Excel 2021, Excel 2024, Microsoft 365 ו-Excel לאינטרנט. ב-Excel 2019 ומטה הנוסחה מציגה #NAME?; השתמשו שם ב-=INDEX(C2:C6,MATCH(F2,A2:A6,0)). גם ב-Google Sheets יש XLOOKUP.
איך גורמים ל-XLOOKUP להחזיר תא ריק או טקסט במקום #N/A?
השתמשו בארגומנט הרביעי, if_not_found: =XLOOKUP(F2,A2:A6,C2:C6,"Not found"), או "" לתא שנראה ריק. הוא מחליף רק את המקרה של "לא נמצא"; שגיאות אחרות עדיין מוצגות.
איך מוצאים את ההתאמה האחרונה עם XLOOKUP?
הגדירו את הארגומנט השישי, search_mode, ל--1 כדי שהחיפוש ירוץ מלמטה למעלה: =XLOOKUP("Ben",B2:B7,C2:C7,,0,-1) מחזירה את הסכום האחרון של Ben במקום את הראשון.
האם XLOOKUP יכולה להחזיר יותר מעמודה אחת?
כן. תנו לה טווח החזרה ברוחב של כמה עמודות, כמו =XLOOKUP(F2,A2:A6,B2:D6), והתוצאה נשפכת לתאים שלצידה. התאים שאליהם היא נשפכת חייבים להיות ריקים, אחרת אקסל מציג #SPILL!.
