מצא את התאמה הראשונה שאינה מכילה

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

כיצד למצוא את ההתאמה הראשונה שאינה מכילה את המחרוזת “TOOL”?

כדי למצוא את מיקום הערך הראשון שאינו מכיל את המחרוזת “TOOL” ברשימה שלמעלה, ניתן להשתמש בפונקציות SEARCH ו-ISNUMBER כדי ליצור מערך של ערכי TRUE ו-FALSE, כאשר TRUE מייצג ערך שמכיל את המחרוזת “TOOL”, ו-FALSE מייצג ערך שאינו מכיל את המחרוזת. לאחר מכן, הפונקציה MATCH תאתר את ה-FALSE הראשון במערך ותחזיר את מיקום הערך הראשון שאינו מכיל את המחרוזת “TOOL”.

תחביר כללי

=MATCH(FALSE,ISNUMBER(SEARCH(lookup_value,range)),0)

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

כדי למצוא את מיקום הערך הראשון שאינו מכיל את המחרוזת “TOOL”, אנא העתק או הזן את הנוסחה למטה בתא E5, והקש Ctrl + Shift + Enter כדי לקבל את התוצאה:

=MATCH(FALSE,ISNUMBER(SEARCH("TOOL",B5:B11)),0)

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

=MATCH(FALSE,ISNUMBER(SEARCH(E4,B5:B11)),0)

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

=MATCH(FALSE,(ISNUMBER(SEARCH(E4,B5:B11)),0)

• SEARCH(E4,B5:B11): הפונקציה SEARCH מחפשת את המחרוזת TOOL (הערך ב-E4) בכל ערך בטווח B5:B11, ומחזירה את מספרי מיקומי ההתחלה של המחרוזת בערכים אלו במערך: {3;3;4;#VALUE!;1;3;#VALUE!}.
• ISNUMBER(ISNUMBER(SEARCH(E4,B5:B11))) = ISNUMBER(ISNUMBER({3;3;4;#VALUE!;1;3;#VALUE!})): הפונקציה ISNUMBER הופכת את המספרים במערך ל-TRUEs, ואת הערכים האחרים ל-FALSEs. לכן, התוצאה תהיה כך: {TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}.
• MATCH(FALSE,(ISNUMBER(SEARCH(E4,B5:B11)),0) = MATCH(FALSE,({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE},0): הסוג ההתאמה 0 מכריח את הפונקציה MATCH לבצע התאמה מדויקת. הפונקציה מחזירה את מיקום ה-FALSE המדויק הראשון במערך. לכן, הפונקציה תחזיר 4 מכיוון שה-FALSE הראשון נמצא במקום ה4.

דברים נוספים לדעת

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

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

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