WYSZUKAJ.PIONOWO w Excelu - budowa, dopasowanie dokładne i przybliżone

Funkcja WYSZUKAJ.PIONOWO (VLOOKUP) w Excelu szuka wartości w pierwszej kolumnie tabeli i zwraca to, co stoi obok niej w tym samym wierszu. Wpisujesz nazwę produktu, dostajesz jego kod albo cenę, bez przewijania setek wierszy.

Niżej masz budowę formuły i dwa przykłady krok po kroku: dopasowanie dokładne, gdy szukasz konkretnej nazwy, i przybliżone, gdy dopasowujesz wartość do progu. Dalej wyszukiwanie po dwóch warunkach, pięć powodów, dla których funkcja zwraca #N/D, sposób, żeby numer kolumny uzupełniał się sam, i WYSZUKAJ.POZIOMO (HLOOKUP) na dane ułożone w wierszach.

W dalszej części poradnika:

1. Budowa funkcji WYSZUKAJ.PIONOWO
2. Dopasowanie dokładne: szukanie po nazwie
3. Dopasowanie przybliżone: progi i stawki
4. WYSZUKAJ.PIONOWO z dwoma warunkami
5. Dlaczego funkcja zwraca #N/D
6. Automatyczne numerowanie kolumn
7. WYSZUKAJ.POZIOMO, czyli szukanie w wierszu
8. Kiedy zamiast tego użyć X.WYSZUKAJ
9. Podsumowanie i plik do pobrania

Funkcja WYSZUKAJ.PIONOWO - budowa

Funkcja WYSZUKAJ.PIONOWO wyszukuje wartości w pierwszej kolumnie zakresu danych i zwraca pierwszą wartość ze wskazanej kolumny tego samego zakresu danych. Jeśli funkcja nie znajdzie wartości, zwróci błąd #N/D.

Poniżej przedstawiam budowę funkcji Wyszukaj.Pionowo

Funkcja składa się z 4 argumentów, przy czym 3 pierwsze są wymagane. Poniżej przedstawiam co one oznaczają:

• szukana_wartośćwartość, która ma zostać znaleziona. Wartość, która ma zostać znaleziona, musi być w pierwszej kolumnie zakresu komórek określonego za pomocą argumentu tabela_tablica.
• tabela_tablicazakres komórek, w których funkcja WYSZUKAJ.PIONOWO szuka wartości szukana_wartość i wartości zwracanej. Możesz użyć nazwanego zakresu lub tabeli i użyć nazw w argumencie zamiast odwołań do komórek. Pierwsza kolumna w zakresie komórek musi zawierać szukana_wartość. Zakres komórek musi również zawierać zwracaną wartość, którą chcesz znaleźć.
• nr_kolumnynumer kolumny (począwszy od 1 dla pierwszej z lewej kolumny w ramach zakresu tabela_tablica) zawierającej wartość zwracaną.
• przeszukiwany_zakres (opcjonalnie)wartość logiczna określająca, czy funkcja WYSZUKAJ.PIONOWO ma znaleźć dopasowanie przybliżone, czy dokładne:
  • PRAWDA - dopasowanie przybliżone. Funkcja szuka najbliższej wartości przy założeniu, że pierwsza kolumna jest posortowana rosnąco. Tej metody Excel używa domyślnie, gdy nie podasz czwartego argumentu.
  • FAŁSZ - dopasowanie dokładne. Funkcja szuka dokładnie tej wartości, której podałeś, a gdy jej nie znajdzie, zwraca #N/D.

W dalszej części poradnika przedstawiam przykład zastosowania funkcji z dopasowaniem dokładnym i przybliżonym

WYSZUKAJ.PIONOWO - dopasowanie dokładne

Zaczniemy od przykładu wyszukiwania dokładnego, które jest najczęściej stosowane.

Na podstawie zakresu z danymi znajdującymi się w zakresie A1:C7 zawierającego nazwy kody i ceny produktu będziemy chcieli wyciągnąć informacje o produktach znajdujących się w kolumnie E czyli dla pralki, lodówki i telewizora.

Aby wyszukać „kod produktu” dla Pralki - w komórce F2 wprowadzamy funkcję:

WYSZUKAJ.PIONOWO(E2; A1:C7;2;FAŁSZ)

Funkcja WYSZUKAJ.PIONOWO wyszukuje wartość z komórki E2 (Pralka) w pierwszej kolumnie od lewej dla zakresu A1:C7.

Po wykryciu pierwszej pozycji licząc od góry (znajdującej się w wierszu nr 5) zwraca wartość znajdującą się w drugiej kolumnie zakresu czyli w kolumnie B wartość „k-25”. Czwarty argument funkcji FAŁSZ określa, że szukamy dokładnie słowa „Pralka”

Aby wyszukać cenę produktu musimy zmienić numer kolumny na 3 - ceny znajdują się w kolumnie C

WYSZUKAJ.PIONOWO(E2; A1:C7;3;FAŁSZ)

A po zastosowaniu funkcji WYSZUKAJ.PIONOWO do pozostałych produktów otrzymamy poniższy wynik

Numer kolumny nie musi być wpisywany ręcznie. Trzy sposoby, żeby uzupełniał się sam, opisuję w sekcji Automatyczne numerowanie kolumn.

Co, jeśli wartość powtarza się w tabeli?

- funkcja WYSZUKAJ.PIONOWO zwróci nam tylko wynik z pierwszej znalezionej wartości licząc od góry.

Na poniższym przykładzie widzimy, że produkt o nazwie telewizor znajduje się zarówno w drugiej jak i trzeciej kolumnie - WYSZUKAJ.PIONOWO poda nam wartość tylko z drugiej kolumny

Co, jeśli funkcja nie znajdzie szukanej wartości?

Jeśli w wyszukiwanym zakresie nie będzie szukanej wartości funkcja WYSZUKAJ.PIONOWO zwróci błąd wartości niedostępnej #N/D.

Tutaj jeśli zmienimy 4 argument funkcji na PRAWDA (wyszukiwanie przybliżone) to funkcja nie zwróci nam błędu, ale w tym przypadku zastosowanie tej opcji nie miało by sensu. Poniżej zobacz praktyczny sposób zastosowania wyszukiwania przybliżonego.

WYSZUKAJ.PIONOWO - dopasowanie przybliżone

Drugi przykład przedstawia nam jak automatycznie przypisać wartości rabatu udzielanego w zależności od wielkości zamówienia.

W kolumnie A mamy limit wartości zamówienia po przekroczeniu, którego przypisujemy wartość rabatu znajdującego się w kolumnie B.

Wartość zamówienia klienta znajduje się w komórce D2 tj. 250 zł a w komórce E2 będziemy chcieli automatycznie uzyskać wartości rabatu dla tego zamówienia.

Funkcja WYSZUKAJ.PIONOWO którą wprowadzimy w komórkę E2 będzie wyglądała następująco:

WYSZUKAJ.PIONOWO(D2; A1:B9;2;PRAWDA)

Funkcja szuka wartość 250 zł w kolumnie A jak widzisz takiej wartości nie ma (dla wyszukiwania dokładnego - FAŁSZ otrzymalibyśmy błąd #N/D), z racji tego, że wybraliśmy opcję wyszukiwania przybliżonego funkcja będzie chciała odnaleźć wartość najbardziej zbliżoną do szukanej.

Wynikiem tej funkcji jest rabat w wysokości 4% odpowiadający kwocie 200 zł.

Jeśli wprowadzimy wartość identyczną z szukaną to wynik będzie odpowiadał tej wartości przykładowo 400 zł - rabat 5%.

Podczas stosowania wyszukiwania przybliżonego funkcji WYSZUKAJ.PIONOWO istotne jest ułożenie wartości w kolejności rosnącej, ponieważ Excel przeszukuje zakres wartości od góry do dołu i zwraca ostatni wiersz który jest mniejszy lub równy szukanej wartości.

Zobacz też jak wyszukać wartości na przecięciu wiersza i kolumny za pomocą funkcji INDEKS (INDEX) i PODAJ.POZYCJĘ (MATCH)  

WYSZUKAJ.PIONOWO z dwoma warunkami

WYSZUKAJ.PIONOWO szuka tylko po jednej kolumnie. Gdy ta sama nazwa produktu występuje w dwóch magazynach, funkcja odda pierwszy wiersz od góry i nie zapyta, który magazyn Cię interesuje.

Rozwiązanie polega na zbudowaniu z dwóch warunków jednego klucza. Dodaj kolumnę pomocniczą, w której sklejasz oba pola operatorem & (więcej o nim w poradniku o łączeniu tekstu w Excelu):

Pionowa kreska rozdziela wartości i to nie jest ozdoba. Bez niej „Pralka” z magazynu „12″ i „Pralka1″ z magazynu „2″ dają ten sam klucz, a funkcja zwróci wartość z nie tego wiersza, co trzeba, nie zgłaszając przy tym żadnego błędu.

Taki sam klucz układasz w miejscu wyszukiwania i dopiero jego podajesz funkcji:

Kolumna pomocnicza musi być pierwszą kolumną zakresu, bo tylko tam WYSZUKAJ.PIONOWO zagląda. Dlatego zakres w przykładzie zaczyna się od kolumny C, nie od A.

Jeśli nie chcesz dokładać kolumny do cudzej tabeli, ten sam wynik dają INDEKS i PODAJ.POZYCJĘ, a w Excelu 365 jedna funkcja X.WYSZUKAJ (XLOOKUP).

Dlaczego WYSZUKAJ.PIONOWO zwraca #N/D

Błąd #N/D znaczy dokładnie jedno: funkcja nie znalazła szukanej wartości w pierwszej kolumnie zakresu. Zwykle nie dlatego, że jej tam nie ma, a dlatego, że Excel widzi ją inaczej niż Ty. Pięć przyczyn, od najczęstszej:

1. Spacja na końcu tekstu. „Pralka ” i „Pralka” to dla Excela dwie różne wartości, a na ekranie wyglądają tak samo. Sprawdzisz to porównaniem =E2=USUŃ.ZBĘDNE.ODSTĘPY(E2): wynik FAŁSZ znaczy, że gdzieś siedzi zbędny odstęp.
2. Liczba zapisana jako tekst. Kod „00123″ wpisany jako tekst nie znajdzie 123 zapisanego jako liczba. Poznasz to po wyrównaniu: tekst przykleja się do lewej krawędzi komórki, liczba do prawej.
3. Szukana wartość nie jest w pierwszej kolumnie zakresu. Funkcja przeszukuje wyłącznie pierwszą kolumnę tego, co podasz jako drugi argument. Jeśli kody masz w kolumnie B, zakres musi zaczynać się od B, a nie od A.
4. Zakres przesunął się przy kopiowaniu. Zapis A1:C7 przeciągnięty o wiersz w dół staje się A2:C8, więc z tabeli wypada pierwszy wiersz. Zablokuj zakres znakiem dolara: $A$1:$C$7 (klawisz F4 robi to za Ciebie).
5. Dopasowanie przybliżone na nieposortowanych danych. Z argumentem PRAWDA pierwsza kolumna musi być posortowana rosnąco. Jeśli nie jest, funkcja nie zwróci błędu, tylko złą wartość, i to jest groźniejsze od #N/D, bo nic nie rzuca się w oczy.

Jak ukryć #N/D, zamiast go oglądać

Gdy brak wartości jest normalny, bo nowego produktu po prostu nie ma jeszcze w cenniku, owiń funkcję w JEŻELI.BŁĄD (IFERROR):

Drugi argument to tekst, który pokaże się w miejsce błędu. Pusty cudzysłów "" zostawi komórkę pustą.

Jedna uwaga: JEŻELI.BŁĄD zasłania każdy błąd, nie tylko #N/D. Jeśli pomylisz numer kolumny i funkcja zwróci #ADR!, też zobaczysz „brak w cenniku” i będziesz szukać problemu w danych zamiast w formule. Dlatego najpierw ustal przyczynę, a dopiero potem ukrywaj błąd.

WYSZUKAJ.PIONOWO to jedna lekcja. Reszta Excela czeka w tym samym kursie

✓Wyszukiwanie i lista rozwijana w jednej lekcji

✓Błąd #N/D i funkcja JEŻELI.BŁĄD, czyli to, co wyżej

✓Funkcje daty, wykresy i formatowanie warunkowe

✓40 lekcji i 52 ćwiczenia, gwarancja zwrotu 30 dni

Zobacz kurs Excel Podstawowy

Automatyczne numerowanie kolumn funkcji Wyszukaj Pionowo

WYSZUKAJ.PIONOWO ma przy kopiowaniu jedną uciążliwość: przeciągnięcie formuły w prawo nie zmienia trzeciego argumentu, czyli numeru kolumny. Trzeba go poprawiać ręcznie w każdej komórce. Są na to trzy sposoby:

1. W miejsce trzeciego argumentu wstaw funkcję NR.KOLUMNY (COLUMN). Po przeciągnięciu formuły w prawo numer kolumny uzupełni się sam. Wada: dodanie kolumny przed tabelą rozsypie wyniki.
2. Zamiast numeru wpisz stałą tablicową w nawiasie klamrowym, na przykład {2;3;4}. Kolejne kolumny pobierają się po kolei, bez przeciągania.
3. Użyj funkcji PODAJ.POZYCJĘ, która sama znajdzie numer kolumny po jej nagłówku. Najodporniejszy z trzech sposobów, bo przestawienie kolumn niczego nie psuje.

Jak działa WYSZUKAJ.POZIOMO w Excelu

WYSZUKAJ.POZIOMO to ta sama funkcja co WYSZUKAJ.PIONOWO, tylko obrócona o 90 stopni. Szuka wartości w pierwszym wierszu tabeli i zwraca to, co stoi pod nią, w wybranym wierszu. Przydaje się, gdy nagłówki biegną w poprzek arkusza: miesiące w kolejnych kolumnach, a pod nimi sprzedaż, koszty i marża.

Różnica jest jedna: trzeci argument to numer wiersza, a nie kolumny. Wiersz z nagłówkami ma numer 1, pierwszy pod nim numer 2 i tak dalej. Ostatni argument działa tak samo jak w wersji pionowej: FAŁSZ szuka dokładnie tej wartości, a PRAWDA dopasowuje do progu i wymaga nagłówków posortowanych rosnąco od lewej do prawej.

Przykład: raport zajmuje zakres A1:M4. W pierwszym wierszu są nazwy miesięcy, w kolumnie A opisy wierszy: sprzedaż, koszty, marża. Wpisujesz miesiąc w komórce P1 i chcesz zobaczyć jego koszty, które stoją w trzecim wierszu tabeli:

Zmieniasz miesiąc w P1, a wynik przelicza się sam. Pułapki są te same co w wersji pionowej:

• Funkcja patrzy tylko w dół, więc wiersz z szukaną wartością musi być pierwszym wierszem zakresu.
• Numer wiersza nie zmienia się sam przy kopiowaniu formuły. Pomaga ten sam trik co wyżej, tylko z funkcją NR.WIERSZA (ROW) zamiast NR.KOLUMNY.
• Brak szukanej wartości kończy się błędem #N/D, który ukryjesz funkcją JEŻELI.BŁĄD tak samo jak przy wyszukiwaniu w kolumnie.

W nowszym Excelu obie funkcje zastępuje jedna. O niej jest następna sekcja.

Kiedy zamiast WYSZUKAJ.PIONOWO użyć X.WYSZUKAJ

WYSZUKAJ.PIONOWO ma trzy ograniczenia, z którymi nie da się nic zrobić: patrzy wyłącznie w prawo, wymaga numeru kolumny i sama nie obsługuje braku wyniku. Wersja pozioma ma te same, tylko patrzy w dół zamiast w prawo. W Excelu 365 i 2021 znikają, bo jest funkcja X.WYSZUKAJ. Wskazujesz w niej kolumnę do przeszukania i kolumnę z wynikiem, w dowolnej kolejności, a tekst na wypadek braku dopisujesz wprost w funkcji.

WYSZUKAJ.PIONOWO zostaje przydatna w dwóch sytuacjach: gdy plik ma trafić do kogoś ze starszym Excelem, bo X.WYSZUKAJ zwróci u niego błąd #NAZWA?, i gdy potrzebujesz dopasowania przybliżonego do progów, jak w przykładzie z rabatami wyżej.

Podsumowanie: WYSZUKAJ.PIONOWO w skrócie

Funkcja WYSZUKAJ.PIONOWO umożliwia wyszukiwanie wartości w pierwszej kolumnie tabeli i zwracanie odpowiadających im danych z innych kolumn tego samego wiersza. 

Wyszukiwanie może odbywać się na dwa sposoby: dopasowanie dokładne (gdy ostatni argument funkcji to FAŁSZ) - używane wtedy, gdy szukamy konkretnej, dokładnej wartości, np. kodu produktu; oraz dopasowanie przybliżone (gdy ostatni argument to PRAWDA) - stosowane, gdy dane w pierwszej kolumnie są posortowane rosnąco, a chcemy znaleźć wartość mieszczącą się w przedziale (np. dla progów punktowych lub stawek podatkowych). 

Numeru kolumny nie musisz wpisywać ręcznie. Stała tablicowa, funkcja NR.KOLUMNY albo PODAJ.POZYCJĘ uzupełnią go same, a formuła przestaje się psuć przy każdej zmianie w układzie tabeli.

🚀 Następny poziom

WYSZUKAJ.PIONOWO patrzy tylko w prawo. INDEKS i PODAJ.POZYCJĘ nie mają tego ograniczenia

✓Wyszukiwanie w dowolną stronę: INDEKS i PODAJ.POZYCJĘ

✓WYSZUKAJ.POZIOMO i wyszukiwanie po dwóch warunkach

✓SUMA.WARUNKÓW, LICZ.WARUNKI i funkcje tekstowe

✓40 lekcji i 17 zadań, gwarancja zwrotu 30 dni

Zobacz kurs Excel Średniozaawansowany

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy