Funkcja WYSZUKAJ PIONOWO: 5 sposobów użycia, o których pewnie nie wiesz
Funkcja WYSZUKAJ PIONOWO chyba powoli odchodzi do lamusa… W każdym razie, jeśli jesteś analitykiem, tudzież potrzebujesz bardziej elastycznych narzędzi – pewnie używasz relacji w Power Pivot. A jeżeli dysponujesz nową wersją Excela – funkcji XLOOKUP.
Dla całej reszty z nas (większości?) funkcja WYSZUKAJ PIONOWO, nadal może być i będzie użytecznym narzędziem. Jeśli używasz jej na co dzień, to ten artykuł pomoże Ci poszerzyć zakres możliwości. A jeśli masz z nią jakieś trudności – polecam najpierw lekturę innych moich artykułów.
Przeczytaj również:

Ściągnij plik początkowy, jeżeli chcesz przećwiczyć rozwiązania opisane w artykule.
Policz prowizję, dzięki najlepszemu dopasowaniu
Musisz policzyć odpowiednią wartość prowizji, na podstawie wartości sprzedaży. Na przykład sprzedawca, który zrealizuje sprzedaż do 10 000 zł – nie otrzyma prowizji. Za to ten, który zrealizuje sprzedaż od 10 000 zł włącznie do 20 000 zł – otrzyma prowizję w wysokości 1% sprzedaży.
Zazwyczaj takie zadanie realizujemy budując zagnieżdżoną formułę z funkcją JEŻELI. I to jest ok, dopóki masz mało warunków. Im więcej warunków, tym bardziej złożona staje się formuła. A dla higieny naszych arkuszy (a raczej ograniczaniu ryzyka popełnienia błędów) powinniśmy upraszczać formuły.
Niewiele osób wie, że w takich przypadkach można wykorzystać WYSZUKAJ PIONOWO, używając w czwartym argumencie: 1 (PRAWDA) – najlepsze dopasowanie, zamiast 0 (FAŁSZ) – dokładne dopasowanie.

W powyższym przykładzie, dla wartości >= 10 000 zł i mniejszej niż 20 000 zł, funkcja zwróci prowizję 1%. Z kolej dla sprzedaży >=20 000 zł i mniejszej niż 30 000 zł, prowizja wyniesie 2%. I tak dalej. Tak właśnie działa WYSZUKAJ PIONOWO z najlepszym dopasowaniem.
Jak pewnie zauważyłeś wartości wyszukiwane (pierwsza lewa kolumna w tabeli źródłowej) są posortowane rosnąco. To nie przypadek, tylko warunek konieczny, aby funkcja zwracała prawidłowe wartości.
Wyszukaj niekompletną wartość
Zdarza Ci się, że dane źródłowe w których wyszukujesz mają nieco inną postać niż byś sobie życzył? Mnie tak 🙂 Jest sposób i na to.
W przykładzie poniżej, chcemy znaleźć ceny dla naszych produktów: krzesła, stołu, ławki i kanapy. Ale dane źródłowe z cenami, ściągnięte z systemu, zawierają jeszcze dodatkowe oznaczenia. Mamy zatem nazwę naszego produktu – Krzesło – wpisaną jako fragment większego ciągu znaków 1_Krzesło_WXX_2020.
Mógłbyś podzielić dane źródłowe, za pomocą narzędzia TEKST JAKO KOLUMNY, ze wstążki DANE ale jak zwykle – jest lepszy sposób. W Excelu (i nie tylko) – „*” (gwiazdka) – zastępuje dowolny ciąg znaków. Okazuje się, że jeżeli użyjesz jej w WYSZUKAJ PIONOWO to spełni dokładnie taką rolę. Wpisując:
"*"&"Krzesło"&"*"
dajesz znać, że szukasz dowolnego ciągu znaków, który w środku zawiera słowo „krzesło”. Zobacz jak to zrobić na poniższym przykładzie.

Wyszukiwanie częściowe z zamianą tekstu na liczbę
W poprzednim akapicie pisałem, o problemie jaki mamy w użyciem WYSZUKAJ PIONOWO, gdy poszukiwane przez nas wartości mają zmienioną formę. Tak jest w sytuacji, gdy szukasz tylko fragmentu jakiegoś tekstu – powiedzmy identyfikatora produktu, a w danych źródłowych ten identyfikator jest „zaszyty” w identyfikatorze transakcji.
I ponownie, dzięki połączeniu WYSZUKAJ PIONOWO z inną funkcją jesteś w stanie sobie doskonale poradzić. W naszym przykładzie id produktu występuje jako pierwsze cztery znaki w id transakcji. Możemy zatem użyć funkcji LEWY, aby wyciągnąć te pierwsze 4 znaki i użyć ich jako argumentu w WYSZUKAJ PIONOWO.
Gdyby okazało się, że poszukiwany przez Ciebie fragment jest w innym miejscu (na przykład w środku) możesz analogicznie użyć funkcji FRAGMENT TEKSTU.

Oglądając przykład, zauważysz, że po fragmencie z funkcją LEWY dodaje zero. Dlaczego? Ponieważ wyciągnąłbym id produktu jako tekst, a w danych źródłowych ten sam identyfikator występuje jako liczba. Jak wiesz dla WYSZUKAJ PIONOWO to znacząca różnica. Użycie operacji matematycznej, w tym przypadku dodawania zamienia tekst na liczby, a żeby nie zmieniać wartości id – po prostu dodaję zero. Dla wnikliwych – odejmowanie zadziała podobnie, ale mnożenie już nie. Jeżeli wiesz dlaczego nie – pochwal się w komentarzu 😉
Uniknij zera w wyniku wyszukiwania
Jeżeli w danych źródłowych nie będzie poszukiwanej przez WYSZUKAJ PIONOWO wartości, to funkcja zwróci błąd: N/D! – błąd wartości niedostępnej. Jeśli natomiast poszukiwana wartość będzie, ale nie będzie do niej przypisanej „korespondującej” wartości, której poszukujemy (pusta komórka) – to w wyniku dostaniemy 0 (zero).
Aby tego uniknąć (zera), do funkcji WYSZUKAJ PIONOWO wystarczy „dokleić” tzw. pusty znak, czyli dwa cudzysłowy „”. Będzie to wyglądało następująco:
=WYSZUKAJ.PIONOWO(E11;$B$11:$C$18;2;0)&""

WYSZUKAJ PIONOWO z dwoma warunkami wyszukiwania
Standardowo, funkcja WYSZUKAJ PIONOWO działa z jednym warunkiem wyszukiwania. Co jeśli masz dwa warunki? Przykładowo, masz zamiar zrobić podwyżki pracownikom na podstawie oceny ich wynagrodzenia (A – oznacza wynagrodzenie poniżej benchmarku, B – jesteśmy w benchmarku, a C – powyżej benchmarku) Jeśli mielibyśmy na tym poprzestać, to standardowe podejście nam wystarczy.
Jednak, gdy chcielibyśmy różnicować podwyżki także w zależności od działu w jakim ktoś pracuje, to już musimy pokombinować. Jedną z metod jest wykorzystanie: nazw oraz funkcji ADR.POŚR.
la każdego działu definiujemy inną tabelę podwyżkową, nazywamy jej zakres tak jak nazwane są nasze działy: Sprzedaż, Finanse, Logistyka. Za pomocą funkcji ADR.POŚR przekształcisz tekst „Sprzedaż” na zdefiniowaną wcześniej nazwę. Żeby lepiej zrozumieć działanie całego mechanizmu, pobierz plik w którym znajdziesz końcowe rozwiązania i więcej szczegółów dotyczących funkcji ADR.POŚR.
Całość wygląda tak:
{=WYSZUKAJ.PIONOWO(D11;ADR.POŚR(C11);2;0)}
Nawiasy klamrowe oznaczają, że jest to formuła tablicowa (musisz ja zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER) Jest to konieczne, ponieważ funkcja ADR.POŚR zwraca zakres komórek, a nie pojedynczą komórkę.


Podsumowanie
Jestem ciekawy, czy znałeś takie zastosowania funkcji WYSZUKAJ PIONOWO. Nie zawsze są one popularne i powszechne, a jak widzisz ułatwiają życie.
A może znasz jeszcze inne? Podziel się z nami w komentarzu.
Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.
6 Comments
Tomek · 14 stycznia 2020 at 11:31
Wow, super modyfikacje zastosowania wyszukaj.pionowo!
Łukasz Pietrzak · 14 stycznia 2020 at 17:09
Prawda? Ja też je lubię 🙂
Ygrek · 25 marca 2020 at 22:20
A ja sie zmagam z sytuacja, gdy w kolumnie wartosci wyszukiwanych (pierwsza kolumna) jest pusta komorka. Nie znalazlem w internecie zadnych podpowiedzi co wtedy zrobic. -pzdr
Łukasz Pietrzak · 30 marca 2020 at 19:26
Nie wiem czy dobrze Cię rozumiem, ale gdy komórka jest pusta to co ma zostać wyszukane? Szukana wartość powinna mieć jakieś znaczenie i nie być pusta. Ale pewnie chodziło o coś innego.
Magda · 24 lutego 2021 at 21:33
W przykładzie z zamianą tekstu na liczbę mnożenie przez 0 daje 0 😉 Chyba, że chodziło o mnożenie przez dowolną liczbę – wtedy zadziała tylko mnożenie przez 1 🙂 P.S. świetnie przykłady!
Łukasz Pietrzak · 25 lutego 2021 at 07:32
Dzięki Magda 🙂 Tak, dokładnie. Oczywiście jak przemnożymy przez zero to dostaniemy zero i WYSZUKAJ.PIONOWO nie zadziała.