Generička formula
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Sažetak
Da biste provjerili u ćeliji jednu od nekoliko stvari i vratili prvo podudaranje nađeno na popisu, možete upotrijebiti formulu INDEX / MATCH koja koristi SEARCH ili FIND za pronalaženje podudaranja. U prikazanom primjeru formula u C5 je:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
gdje je "stvari" imenovani raspon E5: E9.
Napomena: ovo je formula niza i mora se unijeti Control + Shift + Enter.
Obrazloženje
U ovom primjeru imamo popis boja u imenovanom rasponu pod nazivom "stvari" (E5: E9). Želimo provjeriti tekst u stupcu B da li sadrži bilo koju od ovih boja. Ako je tako, želimo vratiti ime prve pronađene boje.
Radeći iznutra prema van, ova formula koristi funkciju ISNUMBER i SEARCH kako bi pretražila tekst u B5 za svaku boju navedenu u "stvarima" poput ove:
ISNUMBER(SEARCH(things,B5)
Ovaj se izraz temelji na formuli (koja je ovdje detaljno objašnjena) koja provjerava u ćeliji jedan podniz. Ako ćelija sadrži podniz, izraz vraća TRUE. Ako nije, izraz vraća FALSE.
Kada ovom PRETRAŽIVANJU damo popis stvari (umjesto jedne stvari), vratit će nam niz rezultata. Svaka pronađena boja generirat će numerički položaj, a boje koje nisu pronađene generirat će pogrešku:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Funkcija ISNUMBER zatim pretvara rezultate u vrijednosti TRUE / FALSE. Bilo koji broj postaje ISTINA, a svaka pogreška (nije pronađena) postaje FALSE. Rezultat je niz poput ovog:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Taj se niz vraća funkciji MATCH kao argument niza. Vrijednost traženja je TRUE, a vrsta podudaranja postavljena je na nulu kako bi se postiglo točno podudaranje. Kada postoji odgovarajuća boja, MATCH vraća poziciju koja je prva pronađena TRUE. Ova se vrijednost uvodi u funkciju INDEX kao broj retka, a kao niz daje se imenovani raspon "things". Kada postoji barem jedno podudaranje, INDEX vraća boju na taj položaj. Kad se ne pronađe podudaranje, ova formula vraća pogrešku # N / A.
S teško kodiranim vrijednostima
Ako ne želite postaviti vanjski imenovani raspon poput "stvari" u ovom primjeru, vrijednosti možete tvrdo kodirati u formulu kao "konstante niza" poput ove:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Dobijte prvu utakmicu u ćeliji
Jezik ovdje prilično je zbunjujući, ali gornja formula vratit će prvo podudaranje na popisu stvari koje treba potražiti. Ako umjesto toga želite vratiti prvo podudaranje pronađeno u ćeliji koja se testira, možete isprobati formulu poput ove:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
U ovoj verziji formule, funkcija MATCH postavljena je za traženje rezultata ovog isječka:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
koja koristi AGREGATE funkciju za dobivanje minimalne vrijednosti u rezultatima koje vraća SEARCH. Ovdje nam treba AGREGATE, jer će dolazni niz vjerojatno sadržavati pogreške (vraćene SEARCH-om kad stvari nisu pronađene), a trebamo funkciju koja će te pogreške ignorirati i još uvijek će nam dati minimalnu brojčanu vrijednost.
Rezultat iz AGREGATE vraća se izravno u MATCH kao vrijednost pretraživanja, zajedno s istim nizom koji vraća SEARCH. Konačni rezultat je prvo pronađeno podudaranje u ćeliji, a ne prvo pronađeno na popisu stvari.
