Formula generică
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
rezumat
Pentru a verifica o celulă pentru unul dintre mai multe lucruri și pentru a returna prima potrivire găsită în listă, puteți utiliza o formulă INDEX / MATCH care folosește SEARCH sau FIND pentru a localiza o potrivire. În exemplul prezentat, formula din C5 este:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
unde „lucruri” este gama numită E5: E9.
Notă: aceasta este o formulă matrice și trebuie introdusă cu Control + Shift + Enter.
Explicaţie
În acest exemplu, avem o listă de culori dintr-o gamă numită numită „lucruri” (E5: E9). Vrem să verificăm textul din coloana B pentru a vedea dacă conține oricare dintre aceste culori. Dacă da, dorim să returnăm numele primei culori găsite.
Lucrând din interior spre exterior, această formulă folosește funcția ISNUMBER și funcția SEARCH pentru a căuta textul din B5 pentru fiecare culoare listată în „lucruri” de genul acesta:
ISNUMBER(SEARCH(things,B5)
Această expresie se bazează pe o formulă (explicată în detaliu aici) care verifică o celulă pentru un singur șir. Dacă celula conține sub șirul, expresia returnează TRUE. Dacă nu, expresia returnează FALS.
Când oferim această CĂUTARE o listă de lucruri (în loc de un singur lucru) ne va oferi înapoi o serie de rezultate. Fiecare culoare găsită va genera o poziție numerică, iar culorile care nu sunt găsite vor genera o eroare:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Funcția ISNUMBER convertește apoi rezultatele în valori TRUE / FALSE. Orice număr devine ADEVĂRAT și orice eroare (care nu este găsită) devine FALS. Rezultatul este o matrice ca aceasta:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Această matrice este returnată la funcția MATCH ca argument al matricei. Valoarea de căutare este TRUE și tipul de potrivire este setat la zero pentru a forța o potrivire exactă. Când există o culoare potrivită, MATCH returnează prima poziție TRUE găsită. Această valoare este introdusă în funcția INDEX ca număr de rând, cu intervalul numit „lucruri” furnizat ca matrice. Când există cel puțin o potrivire, INDEX returnează culoarea în poziția respectivă. Când nu se găsește nicio potrivire, această formulă returnează eroarea # N / A.
Cu valori codificate greu
Dacă nu doriți să configurați un interval numit extern, cum ar fi „lucruri” în acest exemplu, puteți codifica valorile în formulă ca „constante de matrice” de genul acesta:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Obțineți primul meci în celulă
Limba de aici este destul de confuză, dar formula de mai sus va returna prima potrivire găsită în lista de lucruri de căutat. Dacă în schimb doriți să returnați prima potrivire găsită în celula testată, puteți încerca o formulă de genul acesta:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
În această versiune a formulei, funcția MATCH este configurată pentru a căuta rezultatul acestui fragment:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
care folosește funcția AGREGARE pentru a obține valoarea minimă în rezultatele returnate de CĂUTARE. Avem nevoie de AGGREGATE aici, deoarece matricea de intrare probabil va conține erori (returnate de SEARCH atunci când lucrurile nu sunt găsite) și avem nevoie de o funcție care să ignore acele erori și să ne dea totuși valoarea numerică minimă.
Rezultatul din AGGREGATE este returnat direct la MATCH ca valoare de căutare, împreună cu aceeași matrice returnată de SEARCH. Rezultatul final este primul meci găsit în celulă, nu primul meci găsit în lista lucrurilor.
