Formuła ogólna
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Podsumowanie
Aby sprawdzić komórkę pod kątem jednej z kilku rzeczy i zwrócić pierwsze dopasowanie znalezione na liście, możesz użyć formuły INDEKS / DOPASUJ, która używa funkcji SZUKAJ lub ZNAJDŹ w celu zlokalizowania dopasowania. W przedstawionym przykładzie formuła w C5 to:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
gdzie „rzeczy” to nazwany zakres E5: E9.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą kombinacji Control + Shift + Enter.
Wyjaśnienie
W tym przykładzie mamy listę kolorów w nazwanym zakresie o nazwie „rzeczy” (E5: E9). Chcemy sprawdzić tekst w kolumnie B, aby zobaczyć, czy zawiera którykolwiek z tych kolorów. Jeśli tak, to chcemy zwrócić nazwę pierwszego znalezionego koloru.
Działając od wewnątrz, ta formuła wykorzystuje funkcję ISNUMBER i SEARCH do wyszukiwania tekstu w B5 dla każdego koloru wymienionego w „rzeczach”, takich jak ta:
ISNUMBER(SEARCH(things,B5)
To wyrażenie jest oparte na formule (szczegółowo wyjaśnionej tutaj), która sprawdza komórkę pod kątem pojedynczego podciągu. Jeśli komórka zawiera podciąg, wyrażenie zwraca wartość TRUE. Jeśli nie, wyrażenie zwraca FALSE.
Kiedy damy to SZUKAJ, lista rzeczy (zamiast jednej rzeczy) zwróci nam tablicę wyników. Każdy znaleziony kolor wygeneruje pozycję numeryczną, a nie znalezione kolory wygenerują błąd:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Funkcja ISNUMBER konwertuje następnie wyniki na wartości PRAWDA / FAŁSZ. Każda liczba staje się PRAWDA, a każdy błąd (nie znaleziony) staje się FAŁSZEM. Rezultatem jest tablica taka:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Ta tablica jest zwracana do funkcji PODAJ.POZYCJĘ jako argument tablicy. Wartość wyszukiwania to PRAWDA, a typ dopasowania jest ustawiony na zero, aby wymusić dokładne dopasowanie. W przypadku pasującego koloru funkcja PODAJ.POZYCJĘ zwraca pierwszą znalezioną wartość PRAWDA. Ta wartość jest wprowadzana do funkcji INDEKS jako numer wiersza, a nazwany zakres „rzeczy” jest podawany jako tablica. Jeśli jest co najmniej jedno dopasowanie, INDEKS zwraca kolor z tej pozycji. W przypadku braku dopasowania ta formuła zwraca błąd # N / D.
Z wartościami zakodowanymi na stałe
Jeśli nie chcesz konfigurować zewnętrznego nazwanego zakresu, takiego jak „rzeczy” w tym przykładzie, możesz na stałe zakodować wartości w formule jako „stałe tablicowe” w następujący sposób:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Uzyskaj pierwsze dopasowanie w komórce
Język tutaj jest dość zagmatwany, ale powyższy wzór zwróci pierwsze dopasowanie znalezione na liście rzeczy do wyszukania. Jeśli zamiast tego chcesz zwrócić pierwsze dopasowanie znalezione w testowanej komórce, możesz wypróbować następującą formułę:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
W tej wersji formuły funkcja MATCH jest skonfigurowana do wyszukiwania wyniku tego fragmentu:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
który używa funkcji AGGREGATE, aby uzyskać minimalną wartość w wynikach zwracanych przez funkcję SEARCH. Potrzebujemy tutaj AGREGATUJ, ponieważ przychodząca tablica prawdopodobnie będzie zawierać błędy (zwracane przez WYSZUKIWANIE, gdy coś nie zostanie znalezione), a potrzebujemy funkcji, która zignoruje te błędy i nadal poda nam minimalną wartość liczbową.
Wynik funkcji AGGREGATE jest zwracany bezpośrednio do funkcji PODAJ.POZYCJĘ jako wartość wyszukiwania, razem z tą samą tablicą zwracaną przez funkcję WYSZUKAJ. Ostateczny wynik to pierwsze dopasowanie znalezione w komórce, a nie pierwsze dopasowanie znalezione na liście rzeczy.
