Generička formula
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sažetak
Da biste testirali ćeliju za jedan od nekoliko nizova i vratili prilagođeni rezultat za prvo pronađeno podudaranje, možete upotrijebiti formulu INDEX / MATCH koja se temelji na funkciji SEARCH. U prikazanom primjeru formula u C5 je:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
gdje su stvari (E5: E8) i rezultati (F5: F8) imenovani rasponi.
Ovo je formula niza i mora se unijeti s Control + Shift + Enter.
Obrazloženje
Ova formula koristi dva imenovana raspona: stvari i rezultate . Ako izravno prenosite ovu formulu, svakako koristite imenovane raspone s istim imenima (definirano na temelju vaših podataka). Ako ne želite koristiti imenovane raspone, umjesto toga upotrijebite apsolutne reference.
Jezgra ove formule je ovaj isječak:
ISNUMBER(SEARCH(things,B5)
To se temelji na drugoj formuli (koja je ovdje detaljno objašnjena) koja provjerava ima li stanica jedan podniz. Ako ćelija sadrži podniz, formula vraća TRUE. Ako nije, formula vraća FALSE.
Budući da dajemo funkciji SEARCH više od jedne stvari koju treba tražiti, u imenovanim stvarima raspona dat će nam više jedan rezultat, u nizu koji izgleda ovako:
(#VALUE!;9;#VALUE!;#VALUE!)
Brojevi predstavljaju podudaranje u stvarima , pogreške predstavljaju stavke koje nisu pronađene.
Da bismo pojednostavili niz, koristimo funkciju ISNUMBER za pretvaranje svih stavki u nizu u TRUE ili FALSE. Bilo koji važeći broj postaje ISTINA, a svaka pogreška (tj. Stvar koja nije pronađena) postaje FALSE. Rezultat je niz poput ovog:
(FALSE;TRUE;FALSE;FALSE)
koji ulazi u funkciju MATCH kao argument lookup_array, sa lookup_value od TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH zatim vraća položaj prvog pronađenog TRUE, u ovom slučaju 2.
Konačno, koristimo funkciju INDEX za dohvaćanje rezultata iz imenovanih rezultata raspona na istoj poziciji:
=INDEX(results,2) // returns "found red"
Raspon rezultata možete prilagoditi bilo kojim vrijednostima koje imaju smisla u vašem slučaju upotrebe.
Sprječavanje lažnih podudaranja
Jedan od problema s ovim pristupom s pristupom ISNUMBER + SEARCH je taj što možete dobiti pogrešna podudaranja iz djelomičnih podudaranja unutar dužih riječi. Na primjer, ako pokušate podudarati s "dr", možda ćete pronaći i "Andrea", "pio", "kapati" itd., Jer se u tim riječima pojavljuje "dr". To se događa jer SEARCH automatski podudara tip "sadrži".
Za brzo rješenje možete riječi za pretraživanje umotati u razmake (npr. "Dr" ili "dr") kako biste izbjegli pronalazak "dr" u drugoj riječi. Ali to neće uspjeti ako se "dr" pojavi prvi ili zadnji u ćeliji.
Ako trebate robusnije rješenje, jedna je mogućnost prvo normalizirati tekst u pomoćnom stupcu i dodati vodeći i prateći prostor. Zatim upotrijebite formulu na ovoj stranici na tekstu u pomoćnom stupcu, umjesto izvornog teksta.
