Formuła ogólna
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Podsumowanie
Aby przetestować komórkę pod kątem jednego z kilku ciągów i zwrócić niestandardowy wynik dla pierwszego znalezionego dopasowania, możesz użyć formuły INDEKS / PODAJ.POZYCJĘ opartą na funkcji SZUKAJ. W przedstawionym przykładzie formuła w C5 to:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
gdzie rzeczy (E5: E8) i wyniki (F5: F8) są nazwane zakresami.
To jest formuła tablicowa i należy ją wprowadzić za pomocą Control + Shift + Enter.
Wyjaśnienie
Ta formuła wykorzystuje dwa nazwane zakresy: rzeczy i wyniki . Jeśli przenosisz tę formułę bezpośrednio, pamiętaj, aby używać nazwanych zakresów o tych samych nazwach (zdefiniowanych na podstawie Twoich danych). Jeśli nie chcesz używać nazwanych zakresów, użyj zamiast tego odwołań bezwzględnych.
Rdzeniem tej formuły jest ten fragment:
ISNUMBER(SEARCH(things,B5)
Jest to oparte na innej formule (szczegółowo wyjaśnionej tutaj), która sprawdza komórkę pod kątem pojedynczego podciągu. Jeśli komórka zawiera podciąg, formuła zwraca wartość PRAWDA. Jeśli nie, formuła zwraca FALSE.
Ponieważ dajemy funkcji SEARCH więcej niż jedną rzecz do wyszukania, w nazwanym zakresie rzeczy , da nam to więcej niż jeden wynik w tablicy, która wygląda następująco:
(#VALUE!;9;#VALUE!;#VALUE!)
Liczby reprezentują dopasowania w rzeczach , błędy to elementy, których nie znaleziono.
Aby uprościć tablicę, używamy funkcji ISNUMBER do konwersji wszystkich elementów w tablicy na PRAWDA lub FAŁSZ. Każda poprawna liczba staje się PRAWDA, a każdy błąd (np. Rzecz nie znaleziona) staje się FAŁSZEM. Rezultatem jest tablica taka:
(FALSE;TRUE;FALSE;FALSE)
który trafia do funkcji MATCH jako argument lookup_array, z wartością lookup_value równą TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
Następnie funkcja MATCH zwraca pozycję pierwszej znalezionej wartości TRUE, w tym przypadku 2.
Na koniec używamy funkcji INDEKS, aby pobrać wynik z nazwanego zakresu wyników w tej samej pozycji:
=INDEX(results,2) // returns "found red"
Możesz dostosować zakres wyników za pomocą dowolnych wartości, które mają sens w Twoim przypadku użycia.
Zapobieganie fałszywym dopasowaniom
Jednym z problemów z tym podejściem z podejściem ISNUMBER + SEARCH jest to, że możesz otrzymać fałszywe dopasowania z częściowych dopasowań wewnątrz dłuższych słów. Na przykład, jeśli spróbujesz dopasować „dr”, możesz również znaleźć „Andrea”, „pił”, „kroplówka” itd., Ponieważ „dr” pojawia się w tych słowach. Dzieje się tak, ponieważ SEARCH automatycznie dopasowuje „typ zawiera”.
Aby szybko rozwiązać problem, możesz zawijać wyszukiwane słowa w znaki spacji (np. „Dr” lub „dr”), aby uniknąć znajdowania „dr” w innym słowie. Ale to się nie powiedzie, jeśli „dr” pojawi się jako pierwszy lub ostatni w komórce.
Jeśli potrzebujesz bardziej niezawodnego rozwiązania, jedną z opcji jest znormalizowanie tekstu najpierw w kolumnie pomocnika i dodanie spacji początkowej i końcowej. Następnie użyj wzoru z tej strony w tekście w kolumnie pomocniczej zamiast oryginalnego tekstu.
