Všeobecný vzorec
=LOOKUP(2,1/SEARCH(things,A1),things)
Zhrnutie
Ak chcete v bunke skontrolovať jednu z niekoľkých vecí a vrátiť poslednú zhodu nájdenú v zozname, môžete použiť vzorec založený na funkciách HĽADAŤ a HĽADAŤ. V prípade nájdenia viacerých zhôd vráti vzorec poslednú zhodu zo zoznamu „vecí“.
V zobrazenom príklade je vzorec v C5:
=LOOKUP(2,1/SEARCH(things,B5),things)
Vysvetlenie
Kontext: V pomenovanom rozsahu „things“ (E5: E8) máte zoznam vecí a chcete skontrolovať bunky v stĺpci B, či obsahujú tieto veci. Ak je to tak, chcete vrátiť poslednú položku z „vecí“, ktorá sa našla.
V tomto vzorci sa funkcia SEARCH používa na vyhľadávanie buniek v stĺpci B takto:
SEARCH(things,B5)
Keď SEARCH nájde zhodu, vráti pozíciu zhody v prehľadávanej bunke. Ak vyhľadávanie nenájde zhodu, vráti chybu #HODNOTA. Pretože dávame SEARH hľadať viac ako jednu vec, vráti to viac ako jeden výsledok. V zobrazenom príklade funkcia SEARCH vráti pole výsledkov, ako je tento:
(8;24;#VALUE!;#VALUE!)
Toto pole sa potom použije ako deliteľ čísla 1. Výsledkom je pole zložené z chýb a desatinných hodnôt. Chyby predstavujú veci, ktoré sa nenašli, a desatinné hodnoty predstavujú veci, ktoré sa nenašli. V zobrazenom príklade pole vyzerá takto:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Toto pole slúži ako „vyhľadávací_vektor“ pre funkciu LOOKUP. Vyhľadávacia hodnota sa dodáva ako číslo 2 a vektor výsledku je pomenovaný rozsah „things“. Toto je šikovná časť.
Vzorec je konštruovaný takým spôsobom, že vyhľadávací vektor nikdy nebude obsahovať hodnotu väčšiu ako 1, zatiaľ čo vyhľadávacia hodnota je 2. To znamená, že vyhľadávaná hodnota nebude nikdy nájdená. V takom prípade sa funkcia LOOKUP zhoduje s poslednou číselnou hodnotou nájdenou v poli, ktorá zodpovedá poslednej „veci“ nájdenej výrazom SEARCH.
Nakoniec pomocou pomenovaného rozsahu „veci“ dodaného ako vektor výsledku bude funkcia LOOKUP vracať poslednú nájdenú vec.
S pevne zakódovanými hodnotami
Použitie rozsahu ako „veci“ uľahčuje úpravy zoznamu hľadaných výrazov (a pridanie ďalších hľadaných výrazov), ale nie je to potrebné. Hodnoty môžete tiež naprogramovať priamo do vzorca takto:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
