השגת הערך התואם הראשון בתא מול רשימה

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

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

כדי לקבל את מילת המפתח התואמת הראשונה בתא מול רשימת מילות מפתח כפי שמוצג בטבלה למעלה, זה אומר שעלינו לבצע התאמה חלקית במקום התאמה מדויקת. כדי לעשות זאת, ניתן להשתמש בפונקציית SEARCH כדי להעביר את המיקומים של מילות המפתח שנמצאו בתא כמספרים לתוך הפונקציה AGGREGATE. לאחר מכן, AGGREGATE תקבל את המספר הקטן ביותר על ידי הגדרת הפרמטר function_num ל-15 והארגומנט ref2 ל-1. לאחר מכן, השתמשו בפונקציית MATCH כדי לאתר את הערך הקטן ביותר והעבירו את מספר המיקום ל-INDEX כדי לחלץ את הערך במיקום זה.

תחביר כללי

=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ הערה: זוהי נוסחת מערך הדורשת להקליד באמצעות Ctrl + Shift + Enter.

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

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

√ הערה: סימני הדולר ($) מעל מצביעים על הפניות מוחלטות, מה שאומר שטווח מילות המפתח (keyword_rng) בנוסחה לא ישתנה כאשר תעבירו או תעתיקו את הנוסחה לתאים אחרים. לעומת זאת, לא נוספו סימני דולר לתא החיפוש (lookup_cell) שכן רוצים שהוא יהיה דינמי. לאחר הכנסת הנוסחה, גררו את הידית למלא כלפי מטה כדי להחיל את הנוסחה על התאים מטה.

הסבר על הנוסחה

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5): הפונקציה SEARCH מחזירה את המיקום של כל מילת מפתח מתוך הטווח $E$5:$E$7 אם נמצא כמספרים, ואת השגיאה #VALUE! אם לא נמצא. התוצאה תהיה במערך כזה: {15;11;#VALUE!}.
• AGGREGATE(15,6,AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),1) = AGGREGATE(15,6,AGGREGATE(15,6,{15;11;#VALUE!},1),1): הפונקציה AGGREGATE עם function_num של 15 ואופציה 6 תחזיר את הערך הקטן ביותר במערך בהתאם לפרמטר ref2 שווה ל-1, תוך שהיא מתעלמת מערכי שגיאות. לכן, קטע זה יחזיר 11.
• MATCH(MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0),0) = MATCH(MATCH(11,,{15;11;#VALUE!},0),0): סוג ההתאמה match_type 0 מכריח את הפונקציה MATCH לבצע התאמה מדויקת ולהחזיר את המיקום של 11 במערך {15;11;#VALUE!}. לכן, הפונקציה תחזיר 2.
• INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0)) = INDEX($E$5:$E$7,2): הפונקציה INDEX מחזירה את הערך השני בטווח $E$5:$E$7, שהוא bbb.

הערה

• אם אין מילות מפתח בתא, תוחזר שגיאת #NUM!.
• הנוסחה אינה רגישה לאותיות גדולות או קטנות. כדי לבצע התאמה רגישה לאותיות, ניתן פשוט להחליף את הפונקציה SEARCH ב- FIND.

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

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