Obecný vzorec
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
souhrn
Chcete-li v buňce zkontrolovat jednu z několika věcí a vrátit první shodu nalezenou v seznamu, můžete použít vzorec INDEX / MATCH, který k vyhledání shody používá SEARCH nebo FIND. V zobrazeném příkladu je vzorec v C5:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kde „things“ je pojmenovaný rozsah E5: E9.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
V tomto příkladu máme seznam barev v pojmenované oblasti zvané „things“ (E5: E9). Chceme zkontrolovat text ve sloupci B a zjistit, zda obsahuje některou z těchto barev. Pokud ano, chceme vrátit název první nalezené barvy.
Při práci zevnitř ven tento vzorec používá funkci ISNUMBER a funkci SEARCH k vyhledání textu v B5 pro každou barvu uvedenou v "věcech", jako je tato:
ISNUMBER(SEARCH(things,B5)
Tento výraz je založen na vzorci (podrobně vysvětleném zde), který kontroluje buňku na jediný podřetězec. Pokud buňka obsahuje podřetězec, vrátí výraz PRAVDA. Pokud ne, vrátí výraz FALSE.
Když dáme tomuto VYHLEDÁVÁNÍ seznam věcí (namísto jedné věci), vrátí nám řadu výsledků. Každá nalezená barva vygeneruje číselnou pozici a barvy nenalezené vygenerují chybu:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Funkce ISNUMBER poté převede výsledky na TRUE / FALSE hodnoty. Jakékoli číslo se stane PRAVDU a jakákoli chyba (nenalezena) se stane NEPRAVDA. Výsledkem je pole jako toto:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Toto pole je vráceno funkci MATCH jako argument pole. Hodnota vyhledávání je PRAVDA a typ shody je nastaven na nulu, aby vynutil přesnou shodu. Pokud existuje odpovídající barva, funkce MATCH vrací pozici první nalezenou PRAVDU. Tato hodnota se přivádí do funkce INDEX jako číslo řádku, přičemž pole obsahuje pojmenovaný rozsah „things“. Pokud existuje alespoň jedna shoda, vrátí INDEX barvu na dané pozici. Pokud není nalezena žádná shoda, vrátí tento vzorec chybu # N / A.
S pevně zakódovanými hodnotami
Pokud nechcete v tomto příkladu nastavit externí pojmenovaný rozsah, jako je „things“, můžete hodnoty napevno naprogramovat do vzorce jako „konstanty pole“ takto:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Získejte první zápas v buňce
Jazyk je zde docela matoucí, ale výše uvedený vzorec vrátí první shodu nalezenou v seznamu věcí, které je třeba hledat. Pokud místo toho chcete vrátit první shodu nalezenou v testované buňce, můžete zkusit následující vzorec:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
V této verzi vzorce je funkce MATCH nastavena tak, aby hledala výsledek tohoto úryvku:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
který používá funkci AGGREGATE k získání minimální hodnoty ve výsledcích vrácených SEARCH. Potřebujeme AGGREGATE zde, protože příchozí pole pravděpodobně bude obsahovat chyby (vrácené SEARCH, když se věci nenajdou), a potřebujeme funkci, která bude tyto chyby ignorovat a stále nám poskytne minimální číselnou hodnotu.
Výsledek z AGGREGATE je vrácen přímo do MATCH jako vyhledávací hodnota, společně se stejným polem vráceným SEARCH. Konečným výsledkem je první shoda nalezená v buňce, nikoli první shoda nalezená v seznamu věcí.
