Obecný vzorec
=LOOKUP(2,1/SEARCH(things,A1),things)
souhrn
Chcete-li v buňce zkontrolovat jednu z několika věcí a vrátit poslední shodu nalezenou v seznamu, můžete použít vzorec založený na funkcích VYHLEDAT a VYHLEDAT. V případě nalezení více shody vrátí vzorec poslední shodu ze seznamu „věcí“.
V zobrazeném příkladu je vzorec v C5:
=LOOKUP(2,1/SEARCH(things,B5),things)
Vysvětlení
Kontext: Máte seznam věcí v pojmenovaném rozsahu „things“ (E5: E8) a chcete zkontrolovat buňky ve sloupci B, abyste zjistili, zda tyto věci obsahují. Pokud ano, chcete vrátit poslední položku z nalezených věcí.
V tomto vzorci se funkce SEARCH používá k vyhledávání buněk ve sloupci B takto:
SEARCH(things,B5)
Když SEARCH najde shodu, vrátí pozici shody v prohledávané buňce. Když vyhledávání nemůže najít shodu, vrátí chybu #HODNOTA. Protože hledáme SEARH více než jednu věc, vrátí více než jeden výsledek. V zobrazeném příkladu SEARCH vrátí pole výsledků, jako je tento:
(8;24;#VALUE!;#VALUE!)
Toto pole se poté použije jako dělitel čísla 1. Výsledkem je pole složené z chyb a desetinných hodnot. Chyby představují věci, které nebyly nalezeny, a desetinné hodnoty představují věci, které nebyly nalezeny. V zobrazeném příkladu vypadá pole takto:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Toto pole slouží jako "lookup_vector" pro funkci LOOKUP. Vyhledávací hodnota je zadána jako číslo 2 a výsledným vektorem je pojmenovaný rozsah „things“. Toto je chytrá část.
Vzorec je konstruován tak, aby vyhledávací vektor nikdy neobsahoval hodnotu větší než 1, zatímco vyhledávací hodnota je 2. To znamená, že vyhledávací hodnota nebude nikdy nalezena. V tomto případě bude LOOKUP odpovídat poslední číselné hodnotě nalezené v poli, která odpovídá poslední "věci" nalezené SEARCH.
Nakonec LOOKUP pomocí pojmenovaného rozsahu „things“ zadaného jako vektor výsledku vrátí poslední nalezenou věc.
S pevně zakódovanými hodnotami
Použití rozsahu jako „věci“ usnadňuje úpravy seznamu hledaných výrazů (a přidání dalších hledaných výrazů), ale není to požadavek. Hodnoty můžete také napevno kódovat přímo do vzorce takto:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
