WYSZUKAJ.PIONOWO w Excelu – jak jej używać, żeby nie wyskakiwał #N/D!

Do czego służy WYSZUKAJ.PIONOWO
WYSZUKAJ.PIONOWO odpowiada na jedno konkretne pytanie: "mam wartość w jednej tabeli, chcę na jej podstawie wyciągnąć powiązaną wartość z innej tabeli". Klasyczny przykład: masz listę zamówień z nazwą produktu i chcesz dociągnąć do niej cenę z osobnego cennika. Albo masz numer pracownika i chcesz dociągnąć jego dział z listy kadrowej.
Załóżmy, że masz taki cennik w arkuszu "Cennik":
| A (Produkt) | B (Cena) |
|---|---|
| Monitor | 650 |
| Klawiatura | 120 |
| Mysz | 60 |
I listę zamówień w innym arkuszu, kolumna A to produkt, a w kolumnie B chcesz zobaczyć cenę:
=WYSZUKAJ.PIONOWO(A2;Cennik!A:B;2;FAŁSZ)
Rozbijamy składnię na cztery argumenty
=WYSZUKAJ.PIONOWO(szukana_wartość; tabela; numer_kolumny; typ_porównania)
- szukana_wartość – to, co chcesz znaleźć (np.
A2, czyli nazwa produktu z twojej listy zamówień). - tabela – zakres, w którym Excel ma szukać. Pierwsza kolumna tego zakresu MUSI zawierać wartość, której szukasz. To najczęstsza przyczyna błędów – ludzie podają zakres, w którym szukana kolumna nie jest pierwsza.
- numer_kolumny – licząc od pierwszej kolumny zakresu (nie od kolumny arkusza!), która kolumna ma zostać zwrócona. W przykładzie wyżej
2oznacza drugą kolumnę zakresuCennik!A:B, czyli kolumnę B. - typ_porównania –
FAŁSZ(albo0) dla dopasowania dokładnego,PRAWDA(albo1) dla dopasowania przybliżonego. 95% codziennych zastosowań wymagaFAŁSZ.
Dlaczego dostajesz #N/D! i jak to naprawić
To najczęstszy błąd przy tej funkcji. Trzy najbardziej typowe przyczyny:
- Literówka albo dodatkowa spacja w nazwie produktu w jednej z tabel. Excel porównuje tekst znak po znaku – "Monitor " (ze spacją na końcu) to dla niego inny tekst niż "Monitor".
- Format liczby a format tekstu. Jeśli szukasz numeru "1023" zapisanego jako liczba, a w tabeli ten numer jest zapisany jako tekst (albo odwrotnie), dopasowanie się nie powiedzie.
- Zapomniany typ_porównania. Jeśli pominiesz czwarty argument albo wpiszesz
PRAWDA, Excel szuka najbliższego dopasowania w sortowanej tabeli – co przy nieposortowanych danych daje losowe, błędne wyniki, a nie#N/D!. To bywa bardziej niebezpieczne niż sam błąd, bo formuła "działa", tylko zwraca coś niewłaściwego.
Szybki test diagnostyczny: użyj =DŁ(A2) i porównaj z długością tej samej wartości w tabeli źródłowej. Jeśli liczby się różnią, masz gdzieś spację albo niewidoczny znak.
Dlaczego liczba kolumny psuje się po dodaniu nowej kolumny
Drugi częsty problem: dodajesz nową kolumnę do tabeli cennika, a formuła WYSZUKAJ.PIONOWO zaczyna zwracać dane z niewłaściwej kolumny. Dzieje się tak, bo numer kolumny w tej funkcji jest wpisany "na sztywno" jako liczba – Excel nie przelicza go automatycznie po zmianie struktury tabeli. To jedna z głównych wad tej funkcji w porównaniu do nowszych rozwiązań.
Blokowanie zakresu tabeli przy kopiowaniu formuły
Jeśli kopiujesz formułę WYSZUKAJ.PIONOWO do wielu wierszy, zakres tabeli musi być zablokowany znakiem dolara, inaczej przy kopiowaniu "przesunie się" i formuła zacznie szukać w pustych komórkach:
=WYSZUKAJ.PIONOWO(A2;Cennik!$A$2:$B$100;2;FAŁSZ)
Sam argument szukana_wartość (A2) zostaje względny, żeby przy kopiowaniu formuły w dół zmieniał się na A3, A4 itd. Zakres tabeli ($A$2:$B$100) ma zostać stały dla każdego wiersza.
WYSZUKAJ.PIONOWO z innego pliku
Funkcja działa też między skoroszytami, ale wymaga, żeby plik źródłowy był otwarty (albo żeby ścieżka była prawidłowa po zamknięciu). Odwołanie wygląda tak:
=WYSZUKAJ.PIONOWO(A2;[Cennik.xlsx]Arkusz1!$A:$B;2;FAŁSZ)
W praktyce odwołania między plikami bywają kłopotliwe – jeśli przeniesiesz lub zmienisz nazwę pliku źródłowego, formuła się rozsypie. Jeśli regularnie łączysz dane z kilku plików, lepszym rozwiązaniem długoterminowo jest Power Query, które samo zarządza połączeniami.
Kiedy WYSZUKAJ.PIONOWO nie wystarcza
Szukanie w lewo
WYSZUKAJ.PIONOWO potrafi szukać tylko w kolumnach na prawo od kolumny z wartością szukaną. Jeśli potrzebujesz dociągnąć dane z kolumny znajdującej się po lewej stronie, klasyczne rozwiązanie to połączenie INDEKS i PODAJ.POZYCJĘ:
=INDEKS(Cennik!A:A;PODAJ.POZYCJĘ(A2;Cennik!B:B;0))
To rozwiązanie szuka wartości z A2 w kolumnie B cennika, a zwraca odpowiadającą wartość z kolumny A – niezależnie od tego, która kolumna jest "pierwsza".
Dwa warunki jednocześnie
Gdy potrzebujesz dopasować po dwóch kolumnach naraz (np. produkt ORAZ magazyn), WYSZUKAJ.PIONOWO sam nie da rady. Typowe obejście to formuła pomocnicza łącząca oba warunki w jeden tekst (=A2&B2) i wyszukiwanie po takim połączonym kluczu, albo użycie INDEKS z PODAJ.POZYCJĘ i tablicą warunków.
X.WYSZUKAJ jako nowsza alternatywa
W nowszych wersjach Excela (365) dostępna jest funkcja X.WYSZUKAJ, która rozwiązuje część problemów WYSZUKAJ.PIONOWO: szuka w obu kierunkach, nie wymaga liczenia numeru kolumny i ma wbudowaną obsługę "nie znaleziono":
=X.WYSZUKAJ(A2;Cennik!A:A;Cennik!B:B;"brak danych")
Jeśli masz dostęp do X.WYSZUKAJ, warto się do niej przestawić – jest bardziej odporna na zmiany w strukturze tabeli i krótsza w zapisie przy dociąganiu wielu kolumn naraz.
Checklist, zanim zgłosisz, że formuła "nie działa"
- Sprawdź, czy szukana kolumna jest naprawdę pierwszą kolumną w podanym zakresie.
- Sprawdź czwarty argument – powinien być
FAŁSZprzy dopasowaniu dokładnym. - Sprawdź, czy nie masz niewidocznych spacji (
=DŁ()na obu wartościach). - Sprawdź, czy typy danych się zgadzają (liczba jako liczba, nie jako tekst).
- Sprawdź, czy zakres tabeli jest zablokowany dolarami, jeśli kopiujesz formułę w wielu wierszach.
Po przejściu tej listy 90% przypadków "WYSZUKAJ.PIONOWO nie działa" wyjaśnia się samo – i to właśnie te drobne niezgodności w danych, a nie błąd w logice formuły, odpowiadają za większość zgłoszeń.
Przeszukiwanie tabeli w drugą stronę – kiedy nie wiesz, w której kolumnie jest wartość
WYSZUKAJ.PIONOWO zawsze zakłada, że wiesz z góry, w której kolumnie leży odpowiedź. Czasem jest odwrotnie: masz tabelę z kilkoma kolumnami i chcesz się dowiedzieć, w której z nich znajduje się konkretna wartość. Załóżmy, że masz tabelę ocen uczniów w układzie: kolumna B to "Matematyka", C to "Polski", D to "Angielski", a w każdej z nich wpisane są nazwiska uczniów, którzy zdali dany przedmiot na piątkę. Chcesz w jednej formule sprawdzić, w której kolumnie (czyli z jakiego przedmiotu) dany uczeń ma piątkę:
{=INDEKS($B$1:$D$1;SUMA(($B$2:$D$10=G2)*PODAJ.POZYCJĘ(KOLUMNA($B$2:$D$10);KOLUMNA($B$2:$D$10))))}
To formuła tablicowa (wprowadzana przez Ctrl+Shift+Enter) i wygląda groźnie, ale rozwiązuje realny problem, z którym WYSZUKAJ.PIONOWO sobie nie poradzi – bo szuka tylko w jednej, wskazanej kolumnie, a tu szukamy "poziomo", po nazwach nagłówków.
Wyszukiwanie po dwóch warunkach naraz – przykład z kategorią i podkategorią
Wróćmy do klasycznego problemu: tabela z kategoriami i podkategoriami, gdzie ta sama podkategoria (np. "Inne") powtarza się przy różnych kategoriach. Przykładowe dane:
| Kategoria | Podkategoria | Limit |
|---|---|---|
| Komputerowe | Inne | 50 |
| Biznesowe | Inne | 120 |
| Sportowe | Inne | 30 |
Jeśli użytkownik wybiera kategorię "Biznesowe" i podkategorię "Inne", samo WYSZUKAJ.PIONOWO po kolumnie "Podkategoria" zwróci zawsze pierwszy wiersz z "Inne" (czyli błędny limit 50, nie 120). Rozwiązanie to połączenie obu warunków w jeden klucz pomocniczy albo formuła tablicowa:
=INDEKS(C2:C4;PODAJ.POZYCJĘ(1;(A2:A4="Biznesowe")*(B2:B4="Inne");0))
Wpisana jako formuła tablicowa (Ctrl+Shift+Enter), sprawdza jednocześnie oba warunki i zwraca właściwy wiersz – w tym przykładzie limit 120.
Warianty tego samego zadania – jak dociągnąć więcej niż jedną kolumnę
Jeśli potrzebujesz z cennika dociągnąć jednocześnie cenę i jednostkę miary, masz kilka realnych opcji:
- Dwa osobne WYSZUKAJ.PIONOWO – najprostsze, ale wymaga powtórzenia formuły dla każdej kolumny i ręcznej zmiany numeru kolumny w każdej z nich.
- Jedno X.WYSZUKAJ zwracające zakres kolumn –
=X.WYSZUKAJ(A2;Cennik!A:A;Cennik!B:C)zwraca od razu dwie kolumny (cenę i jednostkę) jedną formułą, rozlewając wynik na dwie komórki. - INDEKS z PODAJ.POZYCJĘ policzonym raz, w kolumnie pomocniczej – jeśli masz wiele kolumn do dociągnięcia, policz pozycję raz (
=PODAJ.POZYCJĘ(A2;Cennik!A:A;0)), a potem używaj jej w kilku formułachINDEKS, zamiast każdorazowo szukać tej samej wartości od nowa. To szybsze przy dużych zbiorach danych.
Częste błędy, które nie są literówkami
- Mylenie numeru kolumny arkusza z numerem kolumny w zakresie. Jeśli zakres to
D:F, a chcesz kolumnę F, to argument to3(trzecia kolumna zakresu), nie6(szósta kolumna arkusza). - Brak blokady zakresu przy kopiowaniu formuły w prawo. Dolar blokujący kolumnę (
$A:$B) jest równie ważny jak blokujący wiersz, jeśli kopiujesz formułę zarówno w dół, jak i w prawo. - Założenie, że WYSZUKAJ.PIONOWO rozróżnia wielkość liter. Nie rozróżnia – "monitor" i "Monitor" to dla niej ta sama wartość, co bywa zaletą, a czasem niespodzianką.
- Szukanie liczby zapisanej jako tekst wśród liczb. Numer "0501" wpisany jako tekst (z wiodącym zerem) nigdy nie dopasuje się do liczby
501– to częsty problem przy numerach telefonów, kodach produktów czy numerach PESEL.