Splošna formula
=LOOKUP(2,1/SEARCH(things,A1),things)
Povzetek
Če želite v celici preveriti eno od več stvari in vrniti zadnje ujemanje na seznamu, lahko uporabite formulo, ki temelji na funkcijah POGLED in ISKANJE. V primeru najdenih več zadetkov bo formula vrnila zadnje ujemanje s seznama "stvari".
V prikazanem primeru je formula v C5:
=LOOKUP(2,1/SEARCH(things,B5),things)
Pojasnilo
Kontekst: v imenovanem obsegu "stvari" imate seznam stvari (E5: E8) in želite preveriti celice v stolpcu B, ali vsebujejo te stvari. V tem primeru želite vrniti zadnji element iz najdenih stvari.
V tej formuli se funkcija SEARCH uporablja za iskanje celic v stolpcu B, kot je ta:
SEARCH(things,B5)
Ko SEARCH najde ujemanje, vrne položaj ujemanja v iskani celici. Ko iskanje ne najde zadetka, vrne napako #VALUE. Ker SEARH-u dajemo več kot eno stvar za iskanje, bo vrnil več kot en rezultat. V prikazanem primeru SEARCH vrne vrsto rezultatov, kot je ta:
(8;24;#VALUE!;#VALUE!)
Ta matrika se nato uporabi kot delilec za številko 1. Rezultat je matrika, sestavljena iz napak in decimalnih vrednosti. Napake predstavljajo stvari, ki jih ni mogoče najti, decimalne vrednosti pa stvari, ki jih najdemo. V prikazanem primeru je matrika videti tako:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Ta matrika služi kot "lookup_vector" za funkcijo LOOKUP. Iskalna vrednost je podana kot številka 2, vektor rezultata pa je imenovani obseg "stvari". To je pameten del.
Formula je zgrajena tako, da iskalni vektor nikoli ne bo vseboval vrednosti, večje od 1, medtem ko je iskalna vrednost 2. To pomeni, da iskalne vrednosti nikoli ne bo mogoče najti. V tem primeru se LOOKUP ujema z zadnjo številsko vrednostjo, ki jo najdemo v matriki, kar ustreza zadnji "stvari", ki jo je našlo ISKANJE.
Končno, z uporabo imenovanega obsega "stvari", dobavljenega kot vektor rezultata, LOOKUP vrne zadnjo ugotovljeno stvar.
S trdo kodiranimi vrednostmi
Uporaba obsega, kot je »stvari«, omogoča enostavno spreminjanje seznama iskalnih izrazov (in dodajanje več iskalnih izrazov), vendar to ni pogoj. Vrednosti lahko tudi trdo kodirate neposredno v formulo, kot je ta:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
