Wyszukiwanie Danych ·

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

Arkusz Excela z formułą WYSZUKAJ.PIONOWO zwracającą nazwę i cenę produktu

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)
Monitor650
Klawiatura120
Mysz60

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)

Dlaczego dostajesz #N/D! i jak to naprawić

To najczęstszy błąd przy tej funkcji. Trzy najbardziej typowe przyczyny:

  1. 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".
  2. 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.
  3. 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"

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:

KategoriaPodkategoriaLimit
KomputeroweInne50
BiznesoweInne120
SportoweInne30

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:

  1. 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.
  2. 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.
  3. 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łach INDEKS, 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