=HLOOKUP("Mar",A1:E3,2,FALSE) מחפשת את Mar בשורה הראשונה של A1:E3 ומחזירה את הערך מהשורה השנייה באותה עמודה. זו VLOOKUP שהופכה על הצד, לטבלאות שבהן התוויות עוברות לרוחב החלק העליון.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Mar | |||
| 6 | Sales | 4,800 |
B6 מחפש את Mar בשורה 1, מוצא אותו בעמודה D, ומחזיר את שורה 2 של העמודה הזו: 4,800. בחרו Apr ב-B5 כדי לקבל 5,100, או שנו את ה-2 בנוסחה ל-3 בשביל העלויות.
התחביר של HLOOKUP
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value: מה למצוא בשורה הראשונה של הטבלה.table_array: הטבלה. HLOOKUP מחפשת רק בשורה העליונה שלה.row_index_num: איזו שורה להחזיר, כשהשורה העליונה נספרת כ-1. מספר גדול מגובה הטבלה נותן #REF!; 0 נותן #VALUE!.range_lookup:FALSEלהתאמה מדויקת.TRUEאו כלום להתאמה משוערת על שורה ממוינת.
ההתאמה מתעלמת מגודל האותיות (mar מוצא את Mar), ועם FALSE ערך החיפוש יכול להשתמש בתווים הכלליים * ו-?. ערך שלא נמצא בשורה הראשונה מחזיר #N/A.
התאמה משוערת לרוחב שורה
עם TRUE, HLOOKUP מוצאת את הכותרת הגדולה ביותר שקטנה מערך החיפוש או שווה לו. השורה הראשונה חייבת להיות ממוינת משמאל לימין בסדר עולה.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | 0 | 2 | 5 | 10 |
| 2 | Cost | $4.50 | $6.00 | $9.50 | $14.00 |
| 3 | |||||
| 4 | Parcel (kg) | 7 | |||
| 5 | Cost | $9.50 |
7 ק"ג אינו כותרת. הכותרת הגדולה ביותר שאינה מעליו היא 5, ולכן B5 מחזיר $9.50. שנו את B4 ל-1.5 כדי לקבל $4.50 או ל-12 כדי לקבל $14.00. הטבלה בנוסחה היא B1:E2, לא A1:E2: היא מתחילה במשקל הראשון, כדי שתווית הטקסט ב-A1 לא תהיה חלק מהשורה הממוינת.
XLOOKUP לרוחב שורה
ב-Excel 2021 וב-Microsoft 365, XLOOKUP מחליפה את HLOOKUP. היא מקבלת את השורה שבה מחפשים ואת השורה שממנה מחזירים כשני טווחים, כך שאין מספר שורה לספור, וטווח החזרה בגובה של כמה שורות מחזיר את העמודה כולה.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | Profit | 1,600 | 1,400 | 1,900 | 2,100 |
| 5 | |||||
| 6 | Month | Feb | |||
| 7 | Figures | 3,900 | |||
| 8 | 2,500 | ||||
| 9 | 1,400 |
נוסחה אחת ב-B7 שופכת את שלושת הנתונים של Feb למטה לאורך B7:B9: 3,900, 2,500 ו-1,400. אם ב-B8 או ב-B9 היה משהו, B7 היה מציג #SPILL!. העמוד של XLOOKUP מסביר את האפשרויות האחרות שלה, כמו הודעת "לא נמצא" וההתאמה האחרונה.
להפוך את הטבלה במקום: TRANSPOSE
לפעמים התיקון הטוב יותר הוא עותק אנכי של הטבלה. =TRANSPOSE(A1:D3) מחזירה את אותם תאים כששורות ועמודות מוחלפות, והיא נשארת מקושרת למקור. VLOOKUP, FILTER ותרשימים עובדים עליה אז כרגיל.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Sales | 4,200 | 3,900 | 4,800 |
| 3 | Costs | 2,600 | 2,500 | 2,900 |
| 4 | ||||
| 5 | Month | Sales | Costs | |
| 6 | Jan | 4,200 | 2,600 | |
| 7 | Feb | 3,900 | 2,500 | |
| 8 | Mar | 4,800 | 2,900 |
A5 שופך בלוק של 4 על 3: החודשים לאורך הצד, Sales ו-Costs לרוחב החלק העליון. שנו את המכירות של Feb ב-C2 ל-4100 והעותק מתעדכן. להעתקה חד־פעמית בלי נוסחה, בחרו את הטבלה, העתיקו אותה, ואז השתמשו ב'בית > הדבק > הדבקה מיוחדת' וסמנו 'החלף שורות ועמודות' (Transpose).
תרגול: העלויות של חודש
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Apr | |||
| 6 | Costs |
תורכם: ב-B6, השתמשו ב-HLOOKUP כדי להחזיר את העלויות של החודש שב-B5.
שאלות נפוצות
מה ההבדל בין VLOOKUP ל-HLOOKUP?
VLOOKUP מחפשת למטה בעמודה הראשונה של טבלה ומחזירה ערך מעמודה שמימין. HLOOKUP מחפשת לרוחב השורה הראשונה ומחזירה ערך משורה שמתחת. הארגומנטים זהים, עם מספר שורה במקום מספר עמודה.
מה מספר השורה ב-HLOOKUP?
מספר השורה שתוחזר, כשסופרים מהשורה הראשונה של הטבלה, שהיא שורה 1. ב-=HLOOKUP("Mar",A1:E3,3,FALSE), 3 פירושו השורה השלישית של A1:E3. מספר גדול מגובה הטבלה מחזיר #REF!.
האם XLOOKUP יכולה להחליף את HLOOKUP?
כן. XLOOKUP עובדת בשני הכיוונים: =XLOOKUP("Mar",B1:E1,B2:E2) מחפשת בשורה ומחזירה משורה אחרת. היא דורשת Excel 2021 או Microsoft 365.
למה HLOOKUP מחזירה #N/A?
ערך החיפוש לא נמצא בשורה הראשונה של הטבלה: שגיאת הקלדה, רווח מיותר, מספר שמאוחסן כטקסט, או ערך שנמצא בשורה אחרת. עם TRUE כארגומנט האחרון, גם ערך שקטן מהכותרת הראשונה מחזיר #N/A.
