Formuła ogólna
=LOOKUP(2,1/SEARCH(things,A1),things)
Podsumowanie
Aby sprawdzić komórkę pod kątem jednej z kilku rzeczy i zwrócić ostatnie dopasowanie znalezione na liście, możesz użyć formuły opartej na funkcjach WYSZUKAJ i SZUKAJ. W przypadku znalezienia wielu dopasowań formuła zwróci ostatnie dopasowanie z listy „rzeczy”.
W przedstawionym przykładzie formuła w C5 to:
=LOOKUP(2,1/SEARCH(things,B5),things)
Wyjaśnienie
Kontekst: masz listę rzeczy z nazwanego zakresu „rzeczy” (E5: E8) i chcesz sprawdzić komórki w kolumnie B, aby zobaczyć, czy zawierają te rzeczy. Jeśli tak, chcesz zwrócić ostatnią pozycję z „rzeczy”, które zostały znalezione.
W tej formule funkcja SZUKAJ służy do wyszukiwania komórek w kolumnie B w następujący sposób:
SEARCH(things,B5)
Gdy funkcja SEARCH znajduje dopasowanie, zwraca pozycję dopasowania w wyszukiwanej komórce. Gdy wyszukiwanie nie może znaleźć dopasowania, zwraca błąd #ARG. Ponieważ dajemy SEARH więcej niż jedną rzecz do wyszukania, zwróci więcej niż jeden wynik. W przedstawionym przykładzie funkcja SEARCH zwraca tablicę wyników w następujący sposób:
(8;24;#VALUE!;#VALUE!)
Ta tablica jest następnie używana jako dzielnik dla liczby 1. Wynikiem jest tablica składająca się z błędów i wartości dziesiętnych. Błędy reprezentują rzeczy, których nie znaleziono, a wartości dziesiętne reprezentują rzeczy znalezione. W pokazanym przykładzie tablica wygląda następująco:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Ta tablica służy jako „lookup_vector” dla funkcji LOOKUP. Wartość wyszukiwania jest podawana jako liczba 2, a wektorem wynikowym jest nazwany zakres „rzeczy”. To jest sprytna część.
Formuła jest skonstruowana w taki sposób, że wektor wyszukiwania nigdy nie będzie zawierał wartości większej niż 1, podczas gdy wartość wyszukiwania wynosi 2. Oznacza to, że wartość wyszukiwania nigdy nie zostanie znaleziona. W takim przypadku LOOKUP dopasuje ostatnią wartość liczbową znalezioną w tablicy, która odpowiada ostatniej „rzeczy” znalezionej przez SEARCH.
Wreszcie, używając nazwanego zakresu „rzeczy” podanego jako wektor wynikowy, funkcja WYSZUKAJ zwraca ostatnią znalezioną rzecz.
Z wartościami zakodowanymi na stałe
Korzystanie z zakresu, takiego jak „rzeczy”, ułatwia modyfikowanie listy wyszukiwanych haseł (i dodawanie kolejnych terminów wyszukiwania), ale nie jest to wymagane. Możesz także zakodować wartości bezpośrednio w formule w następujący sposób:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
