✅ Jak sprawić, by Excel VLOOKUP uwzględniał wielkość liter

Spisie treści

Rozróżnianie wielkości liter w funkcji WYSZUKAJ.PIONOWO

Domyślnie w wartości wyszukiwania w funkcji WYSZUKAJ.PIONOWO nie jest rozróżniana wielkość liter. Na przykład, jeśli wartość wyszukiwania to MATT, mat lub Matt, wszystko jest takie samo dla funkcji WYSZUKAJ.PIONOWO. Zwróci pierwszą pasującą wartość niezależnie od wielkości liter.

Rozróżnianie wielkości liter w funkcji WYSZUKAJ.PIONOWO

Załóżmy, że masz dane, jak pokazano poniżej:

Jak widać, istnieją trzy komórki o tej samej nazwie (A2, A4 i A5), ale z inną wielkością liter. Po prawej (w E2:F4) mamy trzy nazwy (Matt, MATT i matt) wraz z ich wynikami w matematyce.

Funkcja WYSZUKAJ.PIONOWO w programie Excel nie jest przystosowana do obsługi wartości wyszukiwania z uwzględnieniem wielkości liter. W powyższym przykładzie, bez względu na wielkość liter w wyszukiwanej wartości (Matt, MATT lub matt), zawsze zwróci 38 (co jest pierwszą pasującą wartością).

W tym samouczku dowiesz się, jak rozróżniać wielkość liter w funkcji WYSZUKAJ.PIONOWO poprzez:

Korzystanie z kolumny pomocniczej.
Bez używania kolumny pomocniczej i formuły.

Rozróżnianie wielkości liter VLOOKUP - korzystanie z kolumny pomocniczej

Kolumna pomocnicza może służyć do uzyskania unikalnej wartości wyszukiwania dla każdego elementu w tablicy wyszukiwania. Pomaga to w rozróżnianiu imion o różnej wielkości liter.

Oto kroki, aby to zrobić:

Wstaw kolumnę pomocnika po lewej stronie kolumny, z której chcesz pobrać dane. W poniższym przykładzie musisz wstawić kolumnę pomocniczą między kolumnami A i C.
W kolumnie pomocnika wprowadź formułę =ROW(). Wstawi numer wiersza w każdej komórce.
Użyj następującej formuły w komórce F2, aby uzyskać wynik wyszukiwania z rozróżnianiem wielkości liter.
```
=WYSZUKAJ.PIONOWO(MAKS(DOKŁADNE(E2,$A$2:$A$9)*(WIERSZ($A$2:$A$9))),$B$2:$C$9,2,0)
```
Skopiuj i wklej go dla pozostałych komórek (F3 i F4).

Notatka: Ponieważ jest to formuła tablicowa, użyj klawiszy Control + Shift + Enter zamiast po prostu wchodzić.

Jak to działa?

Rozłóżmy formułę, aby zrozumieć, jak to działa:

EXACT (E2, $ A $ 2: $ A $ 9) - ta część porównuje wartość wyszukiwania w E2 ze wszystkimi wartościami w A2: A9. Zwraca tablicę TRUE/FALSE, gdzie TRUE jest zwracane, gdy istnieje dokładne dopasowanie. W tym przypadku, gdy wartością w E2 jest Matt, zwróci następującą tablicę:
{PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}.
EXACT (E2, $ A $ 2: $ A $ 9) * (ROW ($ A $ 2: $ A $ 9) - ta część mnoży tablicę TRUE/FALSE przez numer wiersza A2: A9. Gdziekolwiek jest PRAWDA, jest podaje numer wiersza, w przeciwnym razie daje 0. W tym przypadku zwróci {2;0;0;0;0;0;0;0}.
MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) - ta część zwraca maksymalną wartość z tablicy liczb. W takim przypadku zwróci 2 (czyli numer wiersza, w którym występuje dokładne dopasowanie).
Teraz po prostu używamy tej liczby jako wartości wyszukiwania i używamy tablicy wyszukiwania jako B2:C9.

Uwaga: Kolumnę pomocnika można wstawić w dowolnym miejscu zestawu danych. Upewnij się tylko, że znajduje się po lewej stronie kolumny, z której chcesz pobrać dane. Następnie należy odpowiednio dostosować numer kolumny w funkcji WYSZUKAJ.PIONOWO.

Teraz, jeśli nie jesteś fanem kolumny pomocniczej, możesz również przeprowadzić wyszukiwanie z uwzględnieniem wielkości liter bez kolumny pomocniczej.

Rozróżnianie wielkości liter VLOOKUP - bez kolumny pomocniczej

Nawet jeśli nie chcesz korzystać z kolumny pomocniczej, nadal musisz mieć wirtualną kolumnę pomocniczą. Ta wirtualna kolumna nie jest częścią arkusza, ale jest tworzona w formule (jak pokazano poniżej).

Oto formuła, która poda wynik bez kolumny pomocniczej:

=WYSZUKAJ.PIONOWO(MAX(DOKŁADNIE(D2,$A$2:$A$9)*(WIERSZ($A$2:$A$9))),WYBIERZ({1,2},WIERSZ($A$2:$A$9) ,$B$2:$B$9,2,0)

Jak to działa?

Formuła wykorzystuje również koncepcję kolumny pomocniczej. Różnica polega na tym, że zamiast umieszczać kolumnę pomocnika w arkuszu, traktuj ją jako wirtualne dane pomocnicze, które są częścią formuły.

Oto część, która działa jako dane pomocnicze (podświetlona na pomarańczowo):

=WYSZUKAJ.PIONOWO(MAKS(DOKŁADNIE(D2,$A$2:$A$9)*(WIERSZ($A$2:$A$9)))WYBIERZ({1,2};WIERSZ($A$2:$A$9);$B$2:$B$9),2,0)

Pokażę Ci, co mam na myśli przez wirtualne dane pomocnicze.

Na powyższej ilustracji, gdy wybieram część WYBIERZ formuły i naciskam F9, pokazuje wynik, który dałaby formuła WYBIERZ.

Wynik to {2,38;3,88;4,57;5,82;6,55;7,44;8,75;9,38}

Jest to tablica, w której przecinek oznacza następną komórkę w tym samym wierszu, a średnik oznacza, że kolejne dane znajdują się w następnym wierszu. W związku z tym ta formuła tworzy 2 kolumny danych - jedna kolumna zawiera numer wiersza, a druga wynik matematyczny.

Teraz, gdy używasz funkcji WYSZUKAJ.PIONOWO, po prostu szuka ona wartości wyszukiwania w pierwszej kolumnie (z tych wirtualnych danych dwukolumnowych) i zwraca odpowiedni wynik. Wartość wyszukiwania tutaj jest liczbą, którą otrzymujemy z kombinacji funkcji MAX i EXACT.

Pobierz przykładowy plik