Formula generică
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
rezumat
Pentru a testa o celulă pentru una dintre mai multe șiruri și pentru a returna un rezultat personalizat pentru prima potrivire găsită, puteți utiliza o formulă INDEX / MATCH bazată pe funcția SEARCH. În exemplul prezentat, formula din C5 este:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
unde lucrurile (E5: E8) și rezultatele (F5: F8) sunt denumite intervale.
Aceasta este o formulă matrice și trebuie introdusă cu Control + Shift + Enter.
Explicaţie
Această formulă utilizează două intervale denumite: lucruri și rezultate . Dacă portați direct această formulă, asigurați-vă că utilizați intervale denumite cu aceleași nume (definite pe baza datelor dvs.). Dacă nu doriți să utilizați intervale denumite, utilizați în schimb referințe absolute.
Nucleul acestei formule este acest fragment:
ISNUMBER(SEARCH(things,B5)
Aceasta se bazează pe o altă formulă (explicată în detaliu aici) care verifică o celulă pentru un singur șir. Dacă celula conține sub șirul, formula returnează TRUE. Dacă nu, formula returnează FALS.
Deoarece oferim funcției SEARCH mai mult de un lucru de căutat, în lucrurile din gama numită , ne va oferi mai mult rezultatul unic, într-o matrice care arată astfel:
(#VALUE!;9;#VALUE!;#VALUE!)
Numerele reprezintă potriviri în lucruri , erorile reprezintă elemente care nu au fost găsite.
Pentru a simplifica tabloul, folosim funcția ISNUMBER pentru a converti toate articolele din tablou fie la TRUE, fie la FALSE. Orice număr valid devine ADEVĂRAT și orice eroare (adică un lucru care nu a fost găsit) devine FALS. Rezultatul este o matrice ca aceasta:
(FALSE;TRUE;FALSE;FALSE)
care intră în funcția MATCH ca argument lookup_array, cu o valoare de căutare a TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH returnează apoi poziția primului TRUE găsit, 2 în acest caz.
În cele din urmă, folosim funcția INDEX pentru a extrage un rezultat din rezultatele intervalului numit în aceeași poziție:
=INDEX(results,2) // returns "found red"
Puteți personaliza intervalul de rezultate cu orice valoare are sens în cazul dvs. de utilizare.
Prevenirea potrivirilor false
O problemă cu această abordare cu abordarea ISNUMBER + SEARCH este că puteți obține potriviri false din meciuri parțiale în cuvinte mai lungi. De exemplu, dacă încercați să potriviți „dr”, puteți găsi și „Andrea”, „băut”, „picurare” etc., deoarece „dr” apare în aceste cuvinte. Acest lucru se întâmplă deoarece SEARCH face automat o potrivire „de tip conține”.
Pentru o rezolvare rapidă, puteți înfășura cuvinte de căutare în caractere spațiale (adică „dr” sau „dr”) pentru a evita găsirea „dr” într-un alt cuvânt. Dar acest lucru va eșua dacă „dr” apare prima sau ultima într-o celulă.
Dacă aveți nevoie de o soluție mai robustă, o opțiune este să normalizați mai întâi textul într-o coloană de ajutor și să adăugați un spațiu principal și final. Apoi utilizați formula de pe această pagină pe textul din coloana de ajutor, în locul textului original.
