=WYSZUKAJ.PIONOWO(F2;A2:D6;3;FAŁSZ) (po angielsku VLOOKUP, a bez kropki „wyszukaj pionowo”) szuka wartości z F2 w pierwszej kolumnie A2:D6 i zwraca wartość z trzeciej kolumny tego samego wiersza. FAŁSZ na końcu oznacza „tylko dopasowanie dokładne”. Wybierz inny produkt w F2, a cena się zmieni. Tabela pokazuje angielską wersję formuły, =VLOOKUP(F2,A2:D6,3,FALSE), ale możesz w niej wpisywać formuły także po polsku, ze średnikami.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=WYSZUKAJ.PIONOWO(F2;A2:D6;3;FAŁSZ)Kliknij G2, aby zobaczyć obrysowaną tabelę A2:D6. Zmień 3 w formule na 2, a G2 zwróci kategorię zamiast ceny, bo Category to druga kolumna tabeli. Dopasowanie nie rozróżnia wielkości liter: pear znajduje Pear.
Składnia funkcji WYSZUKAJ.PIONOWO
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argument | Co to jest | W przykładzie |
|---|---|---|
lookup_value (szukana_wartość) | Wartość do znalezienia. | F2 (Pear) |
table_array (tabela_tablica) | Przeszukiwana tabela. WYSZUKAJ.PIONOWO szuka tylko w jej pierwszej kolumnie. | A2:D6 |
col_index_num (nr_indeksu_kolumny) | Która kolumna tabeli ma zostać zwrócona, licząc od pierwszej kolumny tabeli (1). | 3 (Price) |
range_lookup (przeszukiwany_zakres) | FAŁSZ albo 0 dla dopasowania dokładnego. PRAWDA, 1 albo nic dla dopasowania przybliżonego. | FAŁSZ |
Numer kolumny liczy się od początku tabeli, a nie od kolumny A arkusza. W tabeli, która zaczyna się w kolumnie C, col_index_num 2 oznacza kolumnę D. Liczba większa niż szerokość tabeli zwraca #ADR! (po angielsku #REF!), a 0 zwraca #ARG! (#VALUE!). Tabele na tej stronie pokazują angielskie nazwy błędów.
W polskim Excelu argumenty oddziela się średnikami, bo przecinek jest separatorem dziesiętnym: =WYSZUKAJ.PIONOWO(F2;A2:D6;3;FAŁSZ).
Wybór zwracanej kolumny funkcją PODAJ.POZYCJĘ
Wpisana na sztywno 3 psuje się po cichu, gdy ktoś wstawi kolumnę w środek tabeli: formuła nadal zwraca trzecią kolumnę, w której teraz jest coś innego. Pozwól, żeby PODAJ.POZYCJĘ (MATCH) znalazła numer kolumny na podstawie nagłówka. Tutaj G1 to lista rozwijana: wybierz Stock albo Category, a G2 się dostosuje.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=WYSZUKAJ.PIONOWO(F2;A2:D6;PODAJ.POZYCJĘ(G1;A1:D1;0);FAŁSZ)PODAJ.POZYCJĘ(G1;A1:D1;0) zwraca pozycję "Price" w wierszu nagłówków, czyli 3, a WYSZUKAJ.PIONOWO używa jej jako numeru kolumny: 0,8 dla Carrot. W polskim Excelu cała formuła to =WYSZUKAJ.PIONOWO(F2;A2:D6;PODAJ.POZYCJĘ(G1;A1:D1;0);FAŁSZ). To wyszukiwanie dwukierunkowe: wiersz wybrany według produktu, kolumna według nagłówka. Ten sam pomysł zapisany z INDEKS zamiast WYSZUKAJ.PIONOWO znajdziesz na stronie INDEKS i PODAJ.POZYCJĘ.
Dopasowanie przybliżone: WYSZUKAJ.PIONOWO z PRAWDA
Z PRAWDA jako ostatnim argumentem WYSZUKAJ.PIONOWO nie szuka równej wartości. Znajduje największą wartość mniejszą lub równą szukanej. Tego potrzebujesz przy przedziałach: progach podatkowych, ocenach, stawkach wysyłki, poziomach prowizji. Pierwsza kolumna musi być posortowana od najmniejszej do największej.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
=WYSZUKAJ.PIONOWO(E2;$A$2:$B$5;2;PRAWDA)Wyniku Bena, 4,200, nie ma w kolumnie A. Największa wartość, która go nie przekracza, to 1000, więc dostaje 3%. Wynik Cary, 5,000, pasuje dokładnie do wiersza 5000 i dostaje 5%. Wynik Deva, 12,500, jest powyżej ostatniego przedziału i dostaje ostatnią stawkę, 8%. Wartość poniżej pierwszego przedziału (tutaj ujemna sprzedaż) zwraca #N/D! (#N/A), dlatego tabela zaczyna się od 0.
Znaki $ w $A$2:$B$5 trzymają tabelę na miejscu, gdy kopiujesz F2 w dół do F5. Bez nich F3 przeszukiwałaby A3:B6 i pomijała pierwszy przedział.
Pominięcie czwartego argumentu działa jak PRAWDA. Na nieposortowanej liście produktów to cichy błąd: Excel szuka tak, jakby lista była posortowana, i może zwrócić cenę z niewłaściwego wiersza albo #N/D! dla wartości, która jest na liście. Gdy szukasz nazw, kodów albo identyfikatorów, zawsze kończ formułę argumentem FAŁSZ.
Dlaczego WYSZUKAJ.PIONOWO zwraca #N/D!
#N/D! oznacza „nie znaleziono”. Tabela poniżej pokazuje trzy częste przyczyny, a kolumna G powtarza każde wyszukiwanie objęte funkcjami JEŻELI.ND i USUŃ.ZBĘDNE.ODSTĘPY.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A Szukanej wartości nie ma w zakresie wyszukiwania.W polskim Excelu: =WYSZUKAJ.PIONOWO(E2;$A$2:$D$6;3;FAŁSZ)- Wartości nie ma w tabeli. Kiwi nie ma w A2:A6. To prawdziwe „nie znaleziono”, a
JEŻELI.ND(...;"Not found")zamienia je w czytelny tekst. Zmień E2 na Apple, a obie kolumny pokażą cenę. - Dodatkowe spacje. E3 zawiera
"Milk "ze spacją na końcu, więc nie równa sięMilk.USUŃ.ZBĘDNE.ODSTĘPY(E3)(TRIM) ją usuwa i G3 znajduje cenę. Jeśli spacje są w tabeli, wyczyść raz kolumnę A tą funkcją zamiast robić to w każdym wyszukiwaniu. - Wartość jest w innej kolumnie. Fruit istnieje, ale w kolumnie B. WYSZUKAJ.PIONOWO przeszukuje tylko pierwszą kolumnę tabeli, więc E4 zawodzi w obu kolumnach. Zacznij tabelę od przeszukiwanej kolumny albo użyj X.WYSZUKAJ, która przyjmuje kolumnę przeszukiwaną i zwracaną osobno.
Wokół wyszukiwania używaj JEŻELI.ND zamiast JEŻELI.BŁĄD. JEŻELI.ND wyłapuje tylko #N/D!, więc #ADR! wynikający ze złego numeru kolumny nadal będzie widoczny, a nie ukryty jako „Not found”.
Jeszcze dwie przyczyny:
- Liczby zapisane jako tekst. Jeśli kolumna A zawiera kody produktów wpisane jako tekst (często po imporcie, z małym zielonym trójkątem w rogu), a F2 zawiera liczbę 101,
=WYSZUKAJ.PIONOWO(F2;A2:B6;2;FAŁSZ)zwraca #N/D!, chociaż 101 jest na liście. Zamień jedną stronę:=WYSZUKAJ.PIONOWO(F2&"";A2:B6;2;FAŁSZ)szuka tekstu "101", a=WYSZUKAJ.PIONOWO(WARTOŚĆ(F2);A2:B6;2;FAŁSZ)szuka liczby, gdy F2 jest tekstem. - Dopasowanie przybliżone na nieposortowanych danych, opisane w sekcji powyżej.
WYSZUKAJ.PIONOWO zwraca 0 zamiast pustej komórki
Gdy komórka, na którą trafi WYSZUKAJ.PIONOWO, jest pusta, Excel pokazuje 0, a nie pustą komórkę. Zero w kolumnie Stock wygląda wtedy jak „brak towaru”, chociaż stanu nigdy nie wpisano. Dodaj do formuły &"" albo sprawdź długość wyniku:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
W polskim Excelu: =WYSZUKAJ.PIONOWO(F2;A2:D6;4;FAŁSZ)&"" i =JEŻELI(DŁ(WYSZUKAJ.PIONOWO(F2;A2:D6;4;FAŁSZ))=0;"";WYSZUKAJ.PIONOWO(F2;A2:D6;4;FAŁSZ)). Pierwsza formuła jest krótsza, ale każdą zwracaną liczbę zamienia w tekst, więc późniejsza SUMA ją pominie. Druga zachowuje liczby jako liczby.
WYSZUKAJ.PIONOWO z innego arkusza
Przed tabelą wpisz nazwę arkusza i !. Gdy budujesz formułę w Excelu, kliknij kartę drugiego arkusza i zaznacz zakres: Excel wpisze Prices!A2:B6 za ciebie. Tutaj karta Orders wyszukuje ceny na karcie Prices.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
=WYSZUKAJ.PIONOWO(B2;Prices!$A$2:$B$6;2;FAŁSZ)Otwórz kartę Prices i zmień cenę Apple: suma zamówienia się zaktualizuje. Dwa szczegóły:
- Nazwa arkusza ze spacjami wymaga apostrofów:
=WYSZUKAJ.PIONOWO(B2;'Price list'!$A$2:$B$6;2;FAŁSZ). - Tabela z innego skoroszytu dodaje nazwę pliku w nawiasach kwadratowych,
[Prices.xlsx]Prices!$A$2:$B$6. Gdy ten plik jest zamknięty, Excel pokazuje w formule jego pełną ścieżkę, a wyszukiwanie nadal działa na podstawie zapisanego pliku.
WYSZUKAJ.PIONOWO z symbolami wieloznacznymi (dopasowanie częściowe)
Przy FAŁSZ szukana wartość może zawierać symbole wieloznaczne: * zastępuje dowolną liczbę znaków, a ? dokładnie jeden. "*"&E2&"*" znajduje pierwszy produkt, którego nazwa zawiera tekst z E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
=WYSZUKAJ.PIONOWO("*"&E2&"*";A2:C6;3;FAŁSZ)"coffee" pasuje i do Iced coffee, i do Coffee beans; WYSZUKAJ.PIONOWO zwraca pierwszy od góry, $2.90. Zmień E2 na bean, aby dostać $8.50, albo na juice. Aby wyszukać prawdziwą gwiazdkę albo znak zapytania, postaw przed nim tyldę: "~*".
WYSZUKAJ.PIONOWO w lewo
WYSZUKAJ.PIONOWO nie zwróci kolumny leżącej na lewo od przeszukiwanej: col_index_num liczy tylko w prawo, a liczby ujemne dają błąd. Aby znaleźć produkt dla danej ceny, przeszukaj kolumnę C i zwróć kolumnę A za pomocą X.WYSZUKAJ albo INDEKS i PODAJ.POZYCJĘ:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
W polskim Excelu: =X.WYSZUKAJ(2,4;C2:C6;A2:A6) i =INDEKS(A2:A6;PODAJ.POZYCJĘ(2,4;C2:C6;0)). Obie zwracają Bread dla danych z pierwszej tabeli. X.WYSZUKAJ ma pełne wyjaśnienie.
Ćwiczenie: koszt wysyłki według wagi
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
Twoja kolej: Każdy koszt obowiązuje od swojej wagi aż do następnej wagi na liście. W E2 użyj WYSZUKAJ.PIONOWO, aby zwrócić koszt wysyłki paczki o wadze z D2.
WYSZUKAJ.PIONOWO z dwoma kryteriami
WYSZUKAJ.PIONOWO przyjmuje jedną szukaną wartość. Aby dopasować dwie kolumny, zbuduj kolumnę pomocniczą, która je łączy, postaw ją na początku tabeli i wyszukaj tak samo połączony tekst. Kolumna A poniżej to =B2&"-"&C2 skopiowane w dół, więc zawiera Coffee-Small, Coffee-Large i tak dalej.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
Twoja kolej: Kolumna A łączy produkt i rozmiar myślnikiem. W G2 zwróć cenę dla produktu z E2 i rozmiaru z F2.
Separator ma znaczenie: "Tea"&"Large" daje TeaLarge, które do niczego w kolumnie A nie pasuje. W Excelu 2021 i Microsoft 365 możesz obyć się bez kolumny pomocniczej dzięki =X.WYSZUKAJ(1;(B2:B6=E2)*(C2:C6=F2);D2:D6); wyszukiwanie z wieloma kryteriami pokazuje tę formułę i wersję z INDEKS/PODAJ.POZYCJĘ.
Najczęściej zadawane pytania
Jak użyć funkcji WYSZUKAJ.PIONOWO w Excelu?
Wpisz =WYSZUKAJ.PIONOWO( i podaj cztery argumenty: szukaną wartość, tabelę (jej pierwsza kolumna musi zawierać tę wartość), numer zwracanej kolumny i FAŁSZ dla dopasowania dokładnego. =WYSZUKAJ.PIONOWO("Pear";A2:D6;3;FAŁSZ) znajduje Pear w kolumnie A i zwraca wartość z kolumny C tego wiersza. W angielskim Excelu: =VLOOKUP("Pear",A2:D6,3,FALSE).
Co oznacza PRAWDA albo FAŁSZ na końcu WYSZUKAJ.PIONOWO?
FAŁSZ (albo 0) oznacza dopasowanie dokładne i zwraca #N/D!, gdy wartości brakuje. PRAWDA (albo 1, albo pominięty argument) oznacza dopasowanie przybliżone: największą wartość mniejszą lub równą szukanej, co działa tylko wtedy, gdy pierwsza kolumna jest posortowana rosnąco.
Dlaczego WYSZUKAJ.PIONOWO zwraca #N/D!?
Wartość nie została znaleziona w pierwszej kolumnie tabeli. Typowe przyczyny to literówka, dodatkowa spacja ("Milk " to nie "Milk"), liczba zapisana jako tekst tylko po jednej stronie albo wartość, która leży w innej kolumnie. Obejmij formułę funkcją JEŻELI.ND, aby pokazać własny tekst: =JEŻELI.ND(WYSZUKAJ.PIONOWO(F2;A2:D6;3;FAŁSZ);"Not found").
Czy WYSZUKAJ.PIONOWO może szukać w lewo?
Nie. WYSZUKAJ.PIONOWO zwraca tylko kolumny na prawo od pierwszej kolumny tabeli. Użyj =X.WYSZUKAJ(F2;C2:C6;A2:A6) w Excelu 2021 albo Microsoft 365 albo =INDEKS(A2:A6;PODAJ.POZYCJĘ(F2;C2:C6;0)) w każdej wersji.
Jak użyć WYSZUKAJ.PIONOWO z innego arkusza?
Przed zakresem wpisz nazwę arkusza i wykrzyknik: =WYSZUKAJ.PIONOWO(B2;Prices!$A$2:$B$6;2;FAŁSZ). Jeśli nazwa arkusza zawiera spację, ujmij ją w apostrofy: 'Price list'!$A$2:$B$6.
