WYSZUKAJ.PIONOWO w Excelu: składnia, przykłady i pułapki

WYSZUKAJ.PIONOWO to funkcja, od której wiele osób zaczyna łączenie tabel w Excelu. Mam identyfikator w jednym miejscu, a potrzebuję ceny, nazwy albo kategorii z cennika. Funkcja znajduje ten identyfikator w pierwszej kolumnie wskazanej tabeli i zwraca wartość z tego samego wiersza.

Działa w każdej wersji Excela, więc spotkasz ją w starych raportach i w plikach od klientów. Ma też dwie pułapki, które potrafią dać poprawnie wyglądający, ale zły wynik. Opisuję je niżej. Szerszy przykład z zamówieniami, duplikatami i porównaniem trzech funkcji znajdziesz w artykule WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO i X.WYSZUKAJ: przykład od początku.

Składnia WYSZUKAJ.PIONOWO

=WYSZUKAJ.PIONOWO(szukana_wartość; tabela_tablica; nr_kolumny; [przeszukiwany_zakres])

• szukana_wartość: to, czego szukam, na przykład identyfikator produktu albo adres komórki z tym identyfikatorem.
• tabela_tablica: zakres, w którego pierwszej kolumnie leży szukana wartość. Kolumna z wynikiem musi być na prawo od niej.
• nr_kolumny: numer kolumny wyniku liczony od lewej krawędzi zakresu, a nie od kolumny A arkusza.
• przeszukiwany_zakres: FAŁSZ (albo 0) oznacza dopasowanie dokładne, PRAWDA (albo 1) przybliżone. Pominięty argument oznacza PRAWDA.

W angielskiej wersji Excela ta sama funkcja nazywa się VLOOKUP, a angielskie poradniki oddzielają argumenty przecinkiem. Separator wynika z ustawień regionalnych, więc formułę skopiowaną z takiego poradnika przepisuję na polskie nazwy funkcji i, jeśli trzeba, na średniki.

Przykład: cena produktu po identyfikatorze

Pracuję na małym, syntetycznym cenniku w arkuszu Produkty, w zakresie A1:D6:

Chcę poznać cenę produktu P-103:

=WYSZUKAJ.PIONOWO("P-103"; Produkty!$A$2:$D$6; 4; FAŁSZ)

Wynik to 89,00. Excel znalazł P-103 w pierwszej kolumnie zakresu i oddał wartość z czwartej kolumny tego zakresu, czyli z kolumny Cena.

W prawdziwym pliku nie wpisuję identyfikatora na sztywno. Mam go w komórce obok, na przykład w B2 arkusza zamówień:

=WYSZUKAJ.PIONOWO(B2; Produkty!$A$2:$D$6; 4; FAŁSZ)

Znaki $ blokują zakres cennika. Bez nich po przeciągnięciu formuły w dół zakres przesuwałby się razem z nią i kolejne wiersze szukałyby w coraz mniejszym fragmencie cennika. To częsty powód, dla którego formuła działa w pierwszym wierszu, a w dziesiątym zwraca #N/D!.

Dwie rzeczy o dopasowaniu tekstu warto wiedzieć od razu. Funkcja nie rozróżnia wielkości liter, więc p-103 też znajdzie P-103. Przy dopasowaniu dokładnym obsługuje symbole wieloznaczne: ? oznacza dowolny pojedynczy znak, a * dowolny ciąg znaków. Jeśli szukany tekst zawiera gwiazdkę albo znak zapytania dosłownie, poprzedź go tyldą ~.

Dokładne i przybliżone dopasowanie

Czwarty argument wpisuję zawsze sam. Domyślne PRAWDA zakłada, że pierwsza kolumna jest posortowana rosnąco, i dopasowuje wiersz z największą wartością mniejszą lub równą szukanej. Przy identyfikatorach prawie nigdy tego nie chcę. Na nieposortowanej liście formuła bez czwartego argumentu może zwrócić wartość z innego wiersza bez żadnego komunikatu o błędzie.

Przybliżone dopasowanie ma jednak sensowne zastosowanie: progi. Załóżmy syntetyczną tabelę rabatów w F1:G5, posortowaną rosnąco po pierwszej kolumnie:

=WYSZUKAJ.PIONOWO(1200; $F$2:$G$5; 2; PRAWDA)

Wynik to 5%, bo 1000 jest największym progiem nie większym niż 1200. Dla kwoty 499,99 dostanę 0%, dla 5000 już 8%. Dla kwoty ujemnej, mniejszej od pierwszego progu, funkcja zwróci #N/D!. Próg dopisany pod tabelą wymaga też rozszerzenia zakresu w formule, a próg wstawiony w środku bez sortowania może rozjechać wyniki. Dlatego przy takich tabelach dodaję obok krótką notkę „sortuj rosnąco i sprawdź zakres”.

Błędy i co mi mówią

Najgroźniejszy jest jednak wynik bez błędu, który pochodzi z niewłaściwego wiersza albo z niewłaściwej kolumny. Dlatego po napisaniu formuły sprawdzam ręcznie dwa, trzy wiersze, w tym jeden, którego klucza w słowniku na pewno nie ma.

Na brak dopasowania reaguję świadomie. Jeśli produkt może nie istnieć w cenniku, opisuję to w wyniku zamiast chować:

=JEŻELI.ND(WYSZUKAJ.PIONOWO(B2; Produkty!$A$2:$D$6; 4; FAŁSZ); "brak w cenniku")

Wybieram JEŻELI.ND zamiast JEŻELI.BŁĄD, bo przechwytuje tylko #N/D!. Błąd #ADR! po złym numerze kolumny nadal będzie widoczny i nie zniknie pod napisem „brak w cenniku”.

Numer kolumny to najsłabsze miejsce formuły

Załóżmy, że ktoś wstawi w cenniku nową kolumnę Stan magazynu między Kategorią a Ceną. Excel rozszerzy zakres w formule do $A$2:$E$6, ale liczba 4 zostanie taka sama. Formuła zacznie zwracać stan magazynu zamiast ceny. Nie pojawi się żaden błąd, zmienią się tylko liczby.

Mam na to dwa sposoby. Pierwszy zostaje przy WYSZUKAJ.PIONOWO i liczy numer kolumny po nagłówku za pomocą PODAJ.POZYCJĘ:

=WYSZUKAJ.PIONOWO(B2; Produkty!$A$2:$E$6; PODAJ.POZYCJĘ("Cena"; Produkty!$A$1:$E$1; 0); FAŁSZ)

Drugi to X.WYSZUKAJ, w którym osobno wskazuję kolumnę kluczy i kolumnę wyniku. Po wstawieniu kolumny Excel przesuwa odwołanie razem z kolumną Cena, więc formuła dalej zwraca cenę. Tak wygląda ona po zmianie, gdy cena jest już w kolumnie E:

=X.WYSZUKAJ(B2; Produkty!$A$2:$A$6; Produkty!$E$2:$E$6; "brak w cenniku")

Kiedy wybieram inną funkcję

WYSZUKAJ.PIONOWO zostawiam w plikach, które muszą działać w starszych wersjach Excela, oraz w istniejących raportach, które działają poprawnie. Nie przepisuję ich tylko dla nowszej nazwy funkcji.

W nowym pliku wybieram X.WYSZUKAJ, jeśli wszyscy odbiorcy ją mają. Według Microsoft jest dostępna między innymi w Microsoft 365, Excelu 2021 i 2024 oraz w Excelu w przeglądarce, ale nie w Excelu 2016 i 2019. Pozwala szukać w lewo, ma wbudowaną wartość na brak dopasowania i domyślnie szuka dokładnie.

Gdy plik ma działać wszędzie, a potrzebuję wyszukiwania w lewo, łączę INDEKS z PODAJ.POZYCJĘ. Ten sam pomysł w układzie wierszy zamiast kolumn realizuje WYSZUKAJ.POZIOMO.

Krótkie ćwiczenie

Przepisz cennik z przykładu do arkusza i dodaj obok trzy identyfikatory: P-102, p-104 i P-110. Napisz formułę z dokładnym dopasowaniem i sprawdź, który wynik jest ceną, a który brakiem. Potem wstaw kolumnę między Kategorią a Ceną i zobacz, co stało się z wynikami. Na koniec popraw formułę jednym z dwóch sposobów opisanych wyżej i upewnij się, że ceny wróciły na swoje miejsce.