חיפוש תלוי רישיות

ייתכן שאתה יודע שניתן לשלב את פונקציות ה-INDEX ו-MATCH, או להשתמש בפונקציית VLOOKUP כדי לחפש ערכים ב-Excel. עם זאת, החיפושים הללו אינם תלויי רישיות. לכן, כדי לבצע התאמה תלוית רישיות, עליך לנצל את הפונקציות EXACT ו-CHOOSE.

בצע חיפוש תלוי רישיות עם INDEX ו-MATCH
בצע חיפוש תלוי רישיות עם VLOOKUP

בצע חיפוש תלוי רישיות עם INDEX ו-MATCH

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

תחביר כללי

=INDEX(return_range,MATCH(TRUE,EXACT(lookup_value,lookup_range),0))

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

כדי לדעת את הציון של YUKI, אנא העתק או הזן את הנוסחה למטה בתא G6, והקש Ctrl + Shift + Enter כדי לקבל את התוצאה:

=INDEX(D5:D14,MATCH(TRUE,EXACT("YUKI",B5:B14),0))

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

=INDEX(D5:D14,MATCH(TRUE,EXACT(G5,B5:B14),0))

הסבר לנוסחה

=INDEX(D5:D14,MATCH(TRUE,EXACT("YUKI",B5:B14),0))

• EXACT(G5,B5:B14): הפונקציה EXACT משווה את מחרוזות הטקסט בטווח שמות התלמידים B5:B14 נגד "YUKI", הערך בתא G5, מחזירה TRUE אם הערך בתאים בין B5 ל-B14 זהה בדיוק ל-YUKI, אחרת FALSE. אז, נקבל מערך של TRUE ו-FALSE כך:
  {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}
• MATCH(TRUE,MATCH(TRUE,EXACT(G5,B5:B14),0),0) = MATCH(TRUE,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE},0),0): הפרמטר match_type 0 דורש מהפונקציה MATCH למצוא התאמה מדויקת. הפונקציה תחזיר את המיקום של הערך המדויק “TURE” במערך, שהוא 10, מכיוון שה-TRUE היחיד נמצא במקום ה-10 במערך.
• INDEX(D5:D14,MATCH(TRUE,EXACT(G5,B5:B14),0)) = INDEX(D5:D14,10): הפונקציה INDEX מחזירה את הערך העשירי בטווח הציונים D5:D14, שהוא A.

בצע חיפוש תלוי רישיות עם VLOOKUP

כדי לדעת את המדינה שממנה מגיע JIMMY עם הפונקציה VLOOKUP, עליך לנצל את הפונקציות CHOOSE ו-EXACT כדי לבצע חיפוש תלוי רישיות. הפונקציה EXACT תבצע השוואה תלוית רישיות בין JIMMY וכל מחרוזת טקסט בטווח שמות התלמידים. לאחר מכן נוכל להשתמש ב-CHOOSE כדי לשלב את תוצאת ה-EXACT והערכים בטווח המדינות. כעת הגיע הזמן לפונקציה VLOOKUP להראות את יכולתה.

תחביר כללי

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(lookup_value,lookup_range),return_range),2,FALSE)

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

כדי לדעת את המדינה שממנה מגיע JIMMY, אנא העתק או הזן את הנוסחה למטה בתא G9, והקש Ctrl + Shift + Enter כדי לקבל את התוצאה:

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT("JIMMY",B5:B14),C5:C14),2,FALSE)

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

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(G8,B5:B14),C5:C14),2,FALSE)

הסבר לנוסחה

=VLOOKUP(TRUE,CHOOSE({1,2},EXACT(G8,B5:B14),C5:C14),2,FALSE)

• EXACT(G8,B5:B14): הפונקציה EXACT משווה את מחרוזות הטקסט בטווח שמות התלמידים B5:B14 נגד הערך בתא G8, JIMMY, מחזירה TRUE אם הערך בטווח שמות התלמידים זהה בדיוק ל-JIMMY, אחרת FALSE. אז, נקבל מערך של TRUE ו-FALSE כך:
  {FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
• CHOOSE({1,2},EXACT(G8,B5:B14),C5:C14) = CHOOSE({1,2},{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},C5:C14): פרמטר ה-index_num של CHOOSE {1,2} ממזג את מערך ה-EXACT והערכים מ C5:C14 למערך דו-ממדי כזה:
  {FALSE,"China";FALSE,"England";FALSE,"America";FALSE,"India";TRUE,"America";FALSE,"India";FALSE,"America";FALSE,"China";FALSE,"England";FALSE,"China"}
• VLOOKUP(TRUE,CHOOSE({1,2},EXACT(G8,B5:B14),C5:C14),2,FALSE) = VLOOKUP(TRUE,{FALSE,"China";FALSE,"England";FALSE,"America";FALSE,"India";TRUE,"America";FALSE,"India";FALSE,"America";FALSE,"China";FALSE,"England";FALSE,"China"},2,FALSE): הפרמטר range_lookup FALSE דורש מהפונקציה VLOOKUP לחפש את הערך המדויק “TRUE” בעמודה הראשונה של המערך הדו-ממדי, ולהחזיר את ההתאמה המדויקת בעמודה השנייה, שהיא America.

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

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