Generička formula
=LOOKUP(2,1/SEARCH(things,A1),things)
Sažetak
Za provjeru ćelije za jednu od nekoliko stvari i vraćanje zadnjeg podudaranja nađenog na popisu možete upotrijebiti formulu koja se temelji na funkcijama LOOKUP i SEARCH. U slučaju pronađenog više podudaranja, formula će vratiti posljednje podudaranje s popisa "stvari".
U prikazanom primjeru formula u C5 je:
=LOOKUP(2,1/SEARCH(things,B5),things)
Obrazloženje
Kontekst: imate popis stvari u imenovanom rasponu "stvari" (E5: E8) i želite provjeriti stanice u stupcu B da li sadrže te stvari. Ako je to slučaj, želite vratiti posljednju stavku iz "stvari" koja je pronađena.
U ovoj se formuli funkcija SEARCH koristi za pretraživanje stanica u stupcu B poput ove:
SEARCH(things,B5)
Kada SEARCH pronađe podudaranje, vraća položaj podudaranja u ćeliji koja se pretražuje. Kada pretraga ne može pronaći podudaranje, vraća pogrešku #VALUE. Budući da SEARH-u dajemo više stvari koje treba tražiti, vratit će više od jednog rezultata. U prikazanom primjeru, SEARCH vraća niz rezultata poput ovog:
(8;24;#VALUE!;#VALUE!)
Taj se niz zatim koristi kao djelitelj broja 1. Rezultat je niz sastavljen od pogrešaka i decimalnih vrijednosti. Pogreške predstavljaju stvari koje nisu pronađene, a decimalne vrijednosti predstavljaju stvari koje su pronađene. U prikazanom primjeru niz izgleda ovako:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Ovaj niz služi kao "lookup_vector" za funkciju LOOKUP. Vrijednost pretraživanja daje se kao broj 2, a vektor rezultata je imenovani raspon "stvari". Ovo je pametni dio.
Formula je konstruirana na takav način da tražilica nikada neće sadržavati vrijednost veću od 1, dok je vrijednost traženja 2. To znači da vrijednost traženja nikada neće biti pronađena. U ovom će se slučaju LOOKUP podudarati s posljednjom numeričkom vrijednošću pronađenom u polju, koja odgovara posljednjoj "stvari" pronađenoj u SEARCH.
Konačno, koristeći imenovani raspon "stvari" isporučene kao vektor rezultata, LOOKUP vraća zadnju pronađenu stvar.
S teško kodiranim vrijednostima
Korištenje raspona poput "stvari" olakšava izmjenu popisa pojmova za pretraživanje (i dodavanje dodatnih pojmova za pretraživanje), ali to nije uvjet. Također možete tvrdo kodirati vrijednosti izravno u formulu poput ove:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
