Wyszukiwanie jakiejś wartości w tabeli, a potem przenoszenie jej w inne miejsce w arkuszu. To codzienność. Mamy nazwę produktu i musimy zerknąć w cennik, ile on kosztuje. Mamy nazwiska uczniów i chcemy wyciągnąć ich ocenę z jakiegoś przedmiotu. W końcu możemy mieć też przychód roczny i wybrać któryś próg podatkowy. Te wszystkie problemy pięknie rozwiązuje vlookup zwany po polsku wyszukaj.pionowo. (przypominam, że Arkusze Google tolerują pisanie funkcji zarówno po polsku jak i po angielsku).
Tu znajdziesz plik źródłowy. W nim są wszystkie przykłady
- Jak działa vlookup?
- Co jeśli identyfikator nie stoi w pierwszej kolumnie?
- Wyszukiwanie w innych arkuszach i innych plikach.
- Wyciąganie na raz wartości z kilku kolumn
- A co zrobić jeśli funkcją vlookup chcemy wyciągnąć 100 kolumn z jednego wiersza?
- Jak wydobyć szybko kolumny parzyste lub nieparzyste?
- Jak wykorzystać vlookup do określania progów rabatowych, przedziałów i innych działań, które robi się “wg tabelki”, ale wartości przypadają gdzieś pomiędzy…
Jak działa vlookup?
Składnia funkcji na początku może być myląca, bo nie wiadomo o co chodzi z tymi indeksami i posortowaniem. =WYSZUKAJ.PIONOWO(kryteria_wyszukiwania; zakres; indeks; sortowany)
=VLOOKUP(kryteria_wyszukiwania; zakres; indeks; posortowany)
- kryteria wyszukiwania – to wartość którą już mamy – np. unikalny kod produktu.
- zakres – to tabela w której będziemy szukać wartości. Np. cennik. Konieczne jest by w pierwszej kolumnie od lewej strony tej tabeli znajdowała się wartość ustalona w kryteriach (wspomniany kod produktu).
- indeks – to numer kolumny z której ma być pobrana poszukiwana wartość (np. jeśli cena jest w 3 kolumnie badanego) to wpisujemy po prostu 3
- posortowany – jeśli szukamy konkretnej wartości, a nie przedziału, to wpisujemy false (najczęściej spotykane). O wersji z sortowaniem opowiem na końcu.

Na oglądanym przykładzie widać, że kod produktu pobierany jest z komórki A6, a następnie funkcja wyszukuje go w cenniku i podaje jego nazwę (druga kolumna zakresu G2:I14) oraz cenę (trzecia kolumna tego zakresu).
Dane do korzystania z vlookupa zwykle układa się tak żeby identyfikatory (unikalne nazwy lub kody) były po lewej stronie. Wówczas wyszukiwanie przebiega gładko.
Co jeśli identyfikator nie stoi w pierwszej kolumnie?
Możemy oczywiście skopiować i wkleić odpowiednie kolumny tak by nam pasowało, ale nie zawsze mamy prawo modyfikacji i nie zawsze jest miejsce w arkuszu. W tej sytuacji możemy się posłużyć zapisem klamrowym opisywanym we wcześniejszym wpisie.
Załóżmy zatem, że mamy cennik, którego nie mamy prawa ruszyć, a chcemy móc wyszukiwać kod produktu do znanej nam jego nazwy. W takiej sytuacji trzeba zrobić zakres tymczasowy. Będzie on miał postać: {H2:H14\G2:G14}
– dla przypomnienia – klamrowy nawias pozwala umieścić wewnątrz kilka zakresów, które będą traktowane jako jeden. Teraz funkcja vlookup widzi H2:H14 w jednej kolumnie i na prawo od niego G2:G14. W tej sytuacji vlookup będzie szukał identyfikatorów w kolumnie H, a wartości poda z kolumny G (bo prosimy go by wyświetlił nam wartość z drugiej kolumny. =vlookup(B6;{H2:H14\G2:G14};2;FALSE)

Wyszukiwanie w innych arkuszach i innych plikach.
Funkcja vlookup może oczywiście działać pomiędzy różnymi zakładkami w tym samym pliku (wówczas musimy przed nazwą zakresu podać nazwę arkusza z wykrzyknikiem – np. Arkusz1!B7:B100, ale może też korzystać z danych trzymanych w innych plikach. Wówczas tam gdzie normalnie pojawiał się zakres, wstawiamy funkcję IMPORTRANGE, której zadaniem będzie ściągnięcie danych z innego pliku.
Zatem możemy zrobić formułę:
=vlookup(a1;importrange(“url arkusza”;”Arkusz1!a1:d1000″);4;false)
Wówczas funkcja vlookup znajdzie nam odpowiednią wartość w kolumnie D (czyli 4 kolumnie) w innym pliku w zakładce Arkusz1, tak jak by to było w naszym pliku. Ważne, że musimy mieć prawa czytania tego pliku i pozwolić na dostęp (świeżo po wpisaniu pierwszej funkcji odnoszącej się do nowego pliku, wyskoczy błąd #REF i okienko w którym musimy kliknąć “nadaj dostęp”).
Wyciąganie na raz wartości z kilku kolumn
Standardowo wartość indeks to numer kolumny z tabeli którą przeszukujemy i chcemy wyświetlić.
Ale to nie musi być tylko jedna kolumna. Jeśli zagnieździmy formułę vlookup z arrayformula, możemy zacząć wpisywać listę indeksów, które mają wylądować obok siebie. W formacie {indeks1 \ indeks2 \ indeks 3}. W takiej sytuacji jednym poleceniem możemy wywołać wartości z kilku kolumn.

Dodając Arrayformula możemy też rozciągnąć vlookup w dół, czyli wpisać cały zakres kryteriów wyszukiwania. Zamiast A1 to A1:A100 i uzyskamy 100 wyszukiwań.
Łącząc to z poprzednim sposobem – możemy jednym poleceniem wypełnić wszystkie potrzebne nam komórki:

A co zrobić jeśli funkcją vlookup chcemy wyciągnąć 100 kolumn z jednego wiersza?
Wiemy już, że możemy wyciągnąć zawartość kilku kolumn przy pomocy zapisu klamrowego. {2\4\6} da nam zawartość trzech kolumn z tabeli. Ale co zrobić jeśli chcemy wyciągnąć ich 100? Trzeba pisać taki wielki ciąg liczb: {1\2\3\4\5…} ?
Na szczęście nie. Możemy się posłużyć formułą sequence
, która zrobi to za nas.
=SEQUENCE(ile wierszy; ile kolumn; liczba początkowa; przeskok)
W tym wypadku musimy wyprodukować liczby od 1 do 100 w jednym wierszu. Więc formuła będzie wyglądać tak:
=sequence(1;100)
Dwóch ostatnich zmiennych mogę nie wpisywać bo domyślnie wynoszą one 1).
=arrayformula( vlookup(A1;tabela;sequence(1;100);FALSE) )
Tak zrobiona formuła wyciągnie nam 100 kolumn z tabeli o nazwie tabela.
Jak wydobyć szybko kolumny parzyste lub nieparzyste?
Podobnie jak w poprzednim przykładzie – w miejsce indeksu wstawimy funkcję sequence.
Tabelę parzystych od 2 do 100 uzyskamy używając =sequence(1;50;2;2)
Tabelę nieparzystych od 1 do 99: =sequence(1;50;1;2)
=arrayformula( vlookup(A1;tabela;sequence(1;50;2;2);FALSE) )
Jak wykorzystać vlookup do określania progów rabatowych, przedziałów i innych działań, które robi się “wg tabelki”, ale wartości przypadają gdzieś pomiędzy…
Tu użyjemy w końcu ostatniego parametru formuły vlookup określającego czy tabela jest posortowana (true / false).
Przykład – zamów powyżej 1000zł, a dostaniesz rabat 10%, a powyżej 5000 zł – rabat 15%, 10 000 zł – rabat 20%, 20 000 zł – rabat 25% itd. Można to zrobić za pomocą formuły IF, ale jeśli wartości jest dużo to szkoda czasu. Mamy też mniej pisania, bo funkcja odnosi się do liczb w arkuszu.
W arkuszu wygląda to tak:

W komórce b28 wpisujemy kwotę “z palca” – 13 500zł. Nie występuje ona w tabeli, więc żeby vlookup nie wypluł nam błędu, ustawiamy ostatni parametr na true
. Od tej pory formuła uznaje, że tabela jest posortowana i będzie przypisywać wartości idąc od góry, aż trafi na ostatnią spełnioną. W tym wypadku wychodzi, że ostatnim spełnionym progiem jest 10 000 i dostajemy rabat 20%.