WYSZUKAJ.PIONOWO (VLOOKUP) w Excelu: co robi i pułapki

WYSZUKAJ.PIONOWO w gruncie rzeczy robi tylko jedno, ale robi to dobrze: szuka wartości w skrajnie lewej kolumnie zakresu i zwraca ci wartość z innej kolumny w tym samym wierszu. Wpisujesz kod towaru i dostajesz odpowiadający mu stan magazynowy. Wpisujesz numer klienta i dostajesz nazwisko. Gdy tylko ta myśl wskoczy na miejsce, od razu widzisz, do czego funkcja została stworzona i w którym punkcie się zacina.

Większość poradników od razu przeskakuje do przycisków: jakie nawiasy, który argument w którym miejscu. Ja wolę odwrócić kolejność. Ważniejsze od dokładnej sekwencji jest to, kiedy naprawdę potrzebujesz WYSZUKAJ.PIONOWO, kiedy nowsza WYSZUKAJ.X jest rozsądniejszym wyborem i jaki błąd popełnia niemal każdy przynajmniej raz. Gdy to staje się jasne, formuła robi się logiczna, zamiast być ciągiem, którego uczysz się na pamięć i zapominasz tydzień później.

Co właściwie robi WYSZUKAJ.PIONOWO?

Wyobraź sobie dwie listy, które mają ze sobą coś wspólnego. Na jednej jest eksport sprzedaży zawierający wyłącznie kody towarów; na drugiej długa tabela odniesienia z kodami oraz odpowiadającymi im nazwami i cenami. Chcesz przy każdym kodzie z pierwszej listy mieć właściwą nazwę wziętą z drugiej, bez ręcznego zestawiania setek wierszy. To właśnie zadanie, do którego stworzono WYSZUKAJ.PIONOWO: automatycznie łączy dwie tabele dzięki wspólnemu kluczowi.

Funkcja bierze wartość, którą znasz, szuka jej z góry na dół w pierwszej kolumnie wskazanego zakresu, a następnie zwraca wartość leżącą w tym samym wierszu, w kolumnie według twojego wyboru. Jeden szczegół przesądza niemal o wszystkim, co potem idzie źle: WYSZUKAJ.PIONOWO zawsze patrzy w prawo. Kolumna, w której szukasz, musi być skrajnie lewą kolumną twojego zakresu, a odpowiedź musi leżeć na prawo od niej. To nie jest ustawienie, które da się przełączyć, tak po prostu funkcja jest zbudowana.

Nazwa zdradza połowę historii. Słowo pionowo mówi wprost: szukasz z góry na dół wzdłuż kolumny. Istnieje funkcja siostrzana, która robi odwrotnie i szuka poziomo wzdłuż wiersza, ale w praktyce spotykasz ją rzadko, bo dane niemal zawsze stoją w kolumnach jedna pod drugą, a nie w wierszach obok siebie. Gdy raz to zauważysz, staje się oczywiste, dlaczego WYSZUKAJ.PIONOWO pojawia się o wiele częściej niż jej pozioma kuzynka: po prostu pasuje do sposobu, w jaki większość ludzi buduje tabelę.

Weźmy inny obraz: masz listę zamówień z samymi numerami i drugą tabelę, w której każdemu numerowi przypisano status wysyłki. WYSZUKAJ.PIONOWO dostawia status obok numeru, bez przeklejania. Kiedy status w tabeli źródłowej się zmienia, twój przegląd nadąża sam. To pokazuje sedno funkcji: nie przenosi danych na stałe, lecz utrzymuje żywe połączenie, które odświeża się przy każdym przeliczeniu arkusza. Dzięki temu jedno starannie prowadzone źródło zasila wiele zestawień, a ty nie kopiujesz tych samych wartości w kółko.

O jakie cztery rzeczy pyta formuła?

Pod maską WYSZUKAJ.PIONOWO prosi o cztery informacje i warto czytać je nie jak kod, lecz jak cztery zwykłe pytania, na które odpowiadasz.

1. Szukana wartość: czego szukasz? Zwykle komórka z kodem, nazwiskiem lub numerem.
2. Zakres tabeli: w której tabeli należy szukać? Pamiętaj, że kolumna wyszukiwania leży po lewej.
3. Numer indeksu kolumny: licząc od lewej wewnątrz zakresu, która kolumna zawiera odpowiedź?
4. Przybliżenie czy dopasowanie ścisłe: wybierz FAŁSZ dla dopasowania dokładnego. Tego chcesz niemal zawsze.

Ten ostatni argument jest cichym winowajcą wielu dziwnych wyników. Jeśli go pominiesz albo ustawisz na przybliżenie, Excel zakłada, że twoja kolumna jest posortowana rosnąco, i chwyta najbliższą wartość, która nie jest większa. Kiedy szukasz dokładnego kodu czy nazwiska, rzadko o to ci chodziło. Stąd zasada praktyczna: wpisz FAŁSZ, chyba że świadomie chcesz przybliżenia, na przykład przy dzieleniu wartości na progi cenowe lub przedziały ocen.

Kiedy WYSZUKAJ.PIONOWO to dobry wybór, a kiedy nie

WYSZUKAJ.PIONOWO pokazuje pełnię możliwości w chwili, gdy musisz związać dwa źródła danych przez wspólną cechę. Zestawić eksport z księgowości z własnym cennikiem. Uzupełnić listę adresów e-mail o nazwisko, które przynależy do każdego z nich. Przełożyć rejestr obecności na działy. We wszystkich tych przypadkach masz klucz występujący na obu listach i chcesz pobrać resztę wiersza. To zadanie centralne, a do niego funkcja jest szybka, niezawodna i łatwa do wyjaśnienia koledze z zespołu.

Obraz z codzienności: prowadzisz listę członków w jednym arkuszu, a składki w drugim. Zamiast porównywać je ręcznie, WYSZUKAJ.PIONOWO wnosi na listę należną kwotę dzięki numerowi legitymacji. Jeśli później kwota zmieni się w źródle, zmiana sama przywędruje przy kolejnym otwarciu pliku. To właśnie ten cichy przepływ czyni funkcję wygodną: pielęgnujesz dane w jednym miejscu, a odczytujesz je w wielu.

Do tego dochodzi skala. To, co przy dziesięciu wierszach dałoby się jeszcze zrobić wzrokiem, przy dziesięciu tysiącach staje się niewykonalne ręcznie, a formuła radzi sobie z tym w mgnieniu oka i tak samo bezbłędnie w setnym wierszu, jak w pierwszym. Właśnie dlatego WYSZUKAJ.PIONOWO tak mocno wrosło w codzienną pracę z arkuszami: skraca godziny ręcznego zestawiania do jednego pociągnięcia formuły w dół.

Jest jedno miejsce, w którym wyszukiwanie przybliżone jest naprawdę przydatne, a często się o nim zapomina: podział na przedziały. Aby znaleźć właściwy rabat przy danej kwocie albo właściwą ocenę przy danej liczbie punktów, przygotowujesz tabelę dolnych granic posortowaną rosnąco i pozwalasz, by WYSZUKAJ.PIONOWO chwyciła wartość tuż poniżej. W tym przypadku przybliżenie jest dokładnie tym, o co ci chodzi. Poza takimi tabelami progów FAŁSZ pozostaje jednak bezpiecznym ustawieniem domyślnym.

Są też sytuacje, w których lepiej po nią nie sięgać. Jeśli odpowiedź leży na lewo od twojej kolumny wyszukiwania, koniec: WYSZUKAJ.PIONOWO nie umie patrzeć w lewo. Jeśli układ twojego źródła zmienia się regularnie, numer indeksu kolumny staje się punktem pęknięcia, bo wstawiona kolumna przesuwa wszystko i formuła nagle wskazuje złe miejsce. A jeśli musisz szukać po dwóch cechach naraz, powiedzmy nazwisko w obrębie konkretnego oddziału, WYSZUKAJ.PIONOWO szybko zmienia się w prowizorkę. Do takich zadań istnieje lepsze narzędzie, do którego zaraz wracam.

Przykład z małą tabelą

Przykład czyni rzecz namacalną. Załóżmy, że masz krótką listę magazynową z kodem, nazwą produktu i dostępną ilością. Chcesz wiedzieć, ile sztuk kodu A-102 jest na stanie, bez samodzielnego czytania listy.

Kod	Produkt	Stan
A-101	Klawiatura	24
A-102	Mysz	60
A-103	Kamera internetowa	12

Co się tutaj dzieje: formuła szuka “A-102” w kolumnie Kod, znajduje wiersz, w którym stoi ten kod, a następnie zwraca trzecią kolumnę zakresu, stan, 60. Chcesz zamiast tego nazwę produktu? Wpisz 2 w miejsce numeru indeksu kolumny. Zauważ, czego zrobić nie możesz: gdybyś miał tylko liczbę 60 i chciał odszukać przypisany do niej kod, ten układ ci go nie poda, bo kod stoi na lewo od stanu. To właśnie granica w lewo w praktyce.

Rozbudujmy tabelę o czwartą kolumnę z ceną. Żeby ją pobrać, zmieniasz numer indeksu kolumny z 3 na 4, i to wszystko, reszta formuły zostaje taka sama. Tu jednak dobrze widać kruchość tego numeru: gdy później wstawisz nową kolumnę między istniejące, cena przesuwa się na piątą pozycję, a formuła z niezmienioną czwórką pokazuje nagle nie to, co trzeba. Numer indeksu nie wie, że dołożyłeś kolumnę, liczy ślepo od lewej i dlatego przy zmiennym układzie tak łatwo się myli.

W prawdziwej pracy rzadko wpisujesz szukaną wartość dosłownie do formuły. Wskazujesz komórkę, na przykład pole, w które ktoś wpisuje kod, żeby odpowiedź zmieniała się w chwili zmiany tego kodu. Jeśli potem kopiujesz formułę w dół przez całą listę, zablokuj zakres tabeli znakami dolara, czyli A$2:C$4 zamiast A2:C4. Jeśli o tym zapomnisz, zakres zsuwa się o wiersz za każdym razem i górne wiersze wypadają z pola widzenia. Drobny szczegół, który wywołuje zaskakująco wiele komunikatów o błędzie u osób pewnych, że ich formuła jest poprawna.

Pułapki: #N/D! i patrzenie w lewo

Błąd, który prędzej czy później napotkasz, to #N/D!, czyli “niedostępne”. Excel jest przy nim uczciwy: nie znalazłem wartości, o którą prosiłeś. Irytujące, ale w istocie pomocne, bo prawie zawsze oznacza, że coś małego się nie zgadza, a nie coś poważnego. Sztuka polega na tym, by nie wpadać w panikę i nie przepisywać całej formuły, lecz spokojnie przejść przez zwykłe przyczyny.

Dlaczego dostajesz #N/D!?

Pierwsza przyczyna jest najbardziej dosłowna: wartości naprawdę nie ma w kolumnie wyszukiwania. Często bywa subtelniej. Spacja na końcu kodu, której oko nie widzi, zero zapisane jako tekst w jednej tabeli i jako liczba w drugiej albo literówka w szukanej wartości. Drugi częsty błąd to zły numer indeksu kolumny, przez który wskazujesz kolumnę poza swoim zakresem. Trzeci to taki, że twój zakres nie zaczyna się od kolumny wyszukiwania, więc Excel szuka w niewłaściwym miejscu.

Warto widzieć w tym diagnozę, a nie ocenę niedostateczną. #N/D!, który pojawia się w każdym wierszu kolumny, wskazuje na problem u podstaw, na przykład zakres nierozpoczynający się od właściwej kolumny albo inny typ danych między dwiema tabelami. #N/D!, który wyskakuje tylko w kilku pojedynczych wierszach, opowiada inną historię: te konkretne pozycje naprawdę brakują na liście odniesienia albo są zapisane nieco inaczej. Odczytanie, gdzie błąd się pojawia, a nie tylko że się pojawia, prowadzi do przyczyny znacznie szybciej.

A potem jest ograniczenie, które nie jest usterką, lecz granicą: WYSZUKAJ.PIONOWO potrafi patrzeć tylko w prawo, nigdy w lewo od skrajnie lewej kolumny. Kto ułożył tabelę tak, że odpowiedź stoi na lewo od klucza, może kombinować, jak chce, nie zadziała. W tym momencie mądrzej jest przestawić tabelę albo użyć funkcji, która tej granicy nie zna. I właśnie tu na scenę wchodzi WYSZUKAJ.X.

WYSZUKAJ.PIONOWO czy WYSZUKAJ.X?

WYSZUKAJ.X to nowszy następca i w punktach, w których WYSZUKAJ.PIONOWO się mozoli, jest po prostu lepszy. Umie patrzeć w lewo, bo osobno wskazujesz, gdzie szukasz, i osobno, gdzie stoi odpowiedź. Nie potrzebuje numeru indeksu kolumny, więc wstawiona kolumna już nie łamie twojej formuły. I domyślnie stoi na dopasowaniu dokładnym, przez co znika klasyczna pułapka zapomnianego argumentu FAŁSZ. Dla kogoś, kto zaczyna dzisiaj, WYSZUKAJ.X jest często spokojniejszym wyborem.

Różnicę widać dobrze, gdy powtórzysz ten sam przykład. Tam, gdzie przy WYSZUKAJ.PIONOWO trzeba było jeszcze podać zakres i numer kolumny 3, przy WYSZUKAJ.X wskazujesz dwie rzeczy osobno: kolumnę, w której szukasz, i kolumnę, z której bierzesz odpowiedź. Przesuń później kolumnę albo wstaw jedną, a wynik pozostaje poprawny, bo nie ma już kruchego numeru pozycji, który mógłby wskazać złe miejsce. To sprawia, że formuły mające przetrwać miesiące są znacznie mniej podatne na błędy, dokładnie ta praca, przy której WYSZUKAJ.PIONOWO z czasem zaczyna zgrzytać.

Wiąże się z tym jeden warunek. WYSZUKAJ.X jest dostępna w Microsoft 365 oraz w Office 2021 i nowszych. Jeśli pracujesz na starszej wersji Office, funkcji po prostu nie ma i WYSZUKAJ.PIONOWO pozostaje narzędziem, które masz. Jeśli udostępniasz plik komuś, kto wciąż siedzi na tak starej wersji, jest kolejny powód, by zostać przy WYSZUKAJ.PIONOWO, bo formuła WYSZUKAJ.X pokazuje mu błąd. Moja rada jest trzeźwa: masz 365 albo Office 2021, naucz się WYSZUKAJ.X, a WYSZUKAJ.PIONOWO trzymaj tylko tam, gdzie twoje pliki inaczej przestałyby być zgodne.

Jakiego Excela do tego potrzebujesz?

Obie funkcje mieszkają w tym samym Excelu, który możesz mieć na dwa sposoby. Pierwszy to Microsoft 365, subskrypcja opłacana rocznie lub miesięcznie, dostarczająca zawsze najnowszą wersję. Drugi to Office 2021, który kupujesz jednorazowo i który działa dalej bez powracających opłat. W obu jest Excel znający zarówno WYSZUKAJ.PIONOWO, jak i WYSZUKAJ.X, więc dla tych funkcji nie musisz koniecznie wybierać subskrypcji.

Który z dwóch pasuje do ciebie, zależy od tego, jak pracujesz, a nie od tego, której funkcji używasz. Jeśli chcesz zawsze najnowszych nowości i usług w chmurze wokół nich, subskrypcja jest naturalną drogą; ten wybór wyjaśniam szerzej w co to jest Microsoft 365. Jeśli Excel taki, jaki jest, w zupełności ci wystarcza i wolisz zapłacić raz, zakup jednorazowy zwykle wychodzi taniej w przeliczeniu na kilka lat. Obie drogi zestawiam obok siebie w Office 365 kontra Office 2021.

Aby dało się to utrzymać w ryzach

Jeśli miałbyś przejąć jeden nawyk, niech to będzie ten: domyślnie wpisuj FAŁSZ dla dopasowania dokładnego i traktuj przybliżenie jako wyjątek, a nie regułę. Odsuwasz tym większość cichych błędów, tych, w których formuła owszem daje odpowiedź, tylko błędną. Nadaj poza tym swoim zakresom nazwę zamiast nagiego ciągu komórek, a później odczytasz, co formuła robi, i nic się nie przesunie, kiedy wstawisz kolumnę.

Druga rzecz warta nawyku dotyczy samych danych. Zanim obwinisz formułę, sprawdź, czy klucz po obu stronach wygląda identycznie: te same spacje, ten sam typ, ta sama wielkość liter tam, gdzie ma ona znaczenie. Wiele błędów #N/D! bierze się nie z formuły, lecz z niechlujnego źródła, w którym kod raz jest liczbą, a raz tekstem. Kwadrans na uporządkowanie kolumny klucza oszczędza później mnóstwo zgadywania i sprawia, że formuła nagle zaczyna zwracać to, czego oczekujesz.

A kiedy przyłapiesz się na naginaniu WYSZUKAJ.PIONOWO, żeby patrzyła w lewo albo szukała po dwóch cechach, potraktuj to jako sygnał. To nie znak, że nie rozumiesz funkcji, lecz że napierasz na jej granicę. Masz Excela z WYSZUKAJ.X, przejdź na nią w tym jednym przypadku; nie masz, przestaw tabelę tak, by klucz trafił na lewo. Wtedy funkcja dalej robi to, w czym jest dobra, a ty nie tracisz popołudnia na formułę, która nigdy nie miała szans zadziałać.