Jak funkcja VLOOKUP przeszukuje tabelę

VLOOKUP oznacza wyszukiwanie pionowe. Funkcja przeszukuje pierwszą kolumnę tabeli od góry do dołu w poszukiwaniu podanej wartości, a następnie zwraca wartość z tej samej linii, ale z innej kolumny.

Można wyobrazić sobie książkę telefoniczną: najpierw znajduje się nazwisko, a potem odczytuje numer z tego samego wiersza. VLOOKUP robi dokładnie to samo w arkuszu kalkulacyjnym.

V przypomina, że funkcja wyszukuje pionowo (w dół kolumny). W kolejnych scenach poznają Państwo jej cztery części i zastosują ją do rzeczywistej tabeli cen.

VLOOKUP przyjmuje cztery informacje oddzielone przecinkami:

• lookup_value - wartość, której należy szukać
• table_array - zakres komórek zawierający dane
• col_index_num - numer kolumny, z której należy zwrócić wartość
• [range_lookup] - TRUE dla dopasowania przybliżonego, FALSE dla dopasowania dokładnego

Nawiasy kwadratowe oznaczają, że ostatni argument jest opcjonalny, ale niemal zawsze należy go jawnie ustawić. Oto schemat formuły:

Wyobraźmy sobie niewielką tabelę produktów w komórkach A1:C4:

• Nagłówki w wierszu 1: Kod, Nazwa, Cena
• Wiersz 2: A100, Apple, 0.50
• Wiersz 3: B200, Banana, 0.30
• Wiersz 4: C300, Cherry, 1.20

Pierwsza kolumna (Kod) to miejsce, w którym VLOOKUP będzie szukać. Pozostałe kolumny zawierają dane, które można zwrócić. Wyszukamy produkt na podstawie jego kodu i pobierzemy jego cenę.

Aby znaleźć cenę produktu o kodzie B200, należy wyszukać B200 w kolumnie 1 i zwrócić wartość z kolumny 3 (Cena):

Mówiąc to głośno: wyszukaj wartość "B200" w tabeli A1:C4, a po jej znalezieniu zwróć wartość z 3. kolumny, używając dokładnego dopasowania (FALSE).

Wynik to 0.30. VLOOKUP znalazła B200 w wierszu 3, a następnie odczytała wartość z trzeciej kolumny.

Wartość col_index_num liczy się od lewej krawędzi table_array, a nie od kolumny A arkusza.

W naszym zakresie A1:C4 kolumny mają następujące numery:

• Kolumna 1 = Kod (kolumna wyszukiwania)
• Kolumna 2 = Nazwa
• Kolumna 3 = Cena

Aby zwrócić Nazwę, należy użyć indeksu 2, a dla Ceny — indeksu 3. Indeks 1 po prostu zwraca wyszukaną wartość.

Wpisywanie na stałe wartości "B200" zdarza się rzadko. Zwykle szukana wartość znajduje się w innej komórce. Załóżmy, że ktoś wpisuje kod w komórce E2. Zamiast stałego tekstu należy wskazać w funkcji VLOOKUP tę komórkę.

Od tej pory każda zmiana wartości w E2 automatycznie zaktualizuje wynik. Właśnie tak funkcje wyszukiwania znajdują zastosowanie w fakturach, panelach i polach wyszukiwania.

Funkcja VLOOKUP ma jedną ścisłą zasadę: może przeszukiwać wyłącznie skrajną lewą kolumnę zakresu table_array. Nie może przeszukać kolumny 2 i następnie cofnąć się do kolumny 1.

Dlatego kolumna, w której ma odbywać się wyszukiwanie, musi być pierwszą kolumną zakresu. Jeśli kody znajdują się w kolumnie B, należy rozpocząć zakres table_array od kolumny B, na przykład B1:D4.

To ograniczenie do lewej kolumny jest najczęstszą przyczyną frustracji związanej z funkcją VLOOKUP. W jednej z kolejnych lekcji pokazano, jak je obejść.

W zakresie table_array można uwzględnić wiersz nagłówka albo go pominąć. Oba rozwiązania działają:

• A1:C4 uwzględnia nagłówki (Code, Name, Price)
• A2:C4 pomija nagłówki

W przypadku dopasowania dokładnego (FALSE) nagłówki nie powodują błędnych wyników, ponieważ nie będą pasować do kodu produktu. Wiele osób je uwzględnia, aby zakres był łatwiejszy do odczytania. Należy tylko pamiętać, że numer kolumny nadal jest liczony od lewej strony wybranego zakresu.

Załóżmy, że tworzą Państwo fakturę. Kod produktu znajduje się w komórce A10, a z tabeli chcą Państwo pobrać jego nazwę i cenę.

Nazwa w B10:

Cena w C10:

Jedna tabela może zasilać wiele komórek. Wpisanie kodu tylko raz uzupełnia pozostałe dane — na tym polega praktyczna siła funkcji VLOOKUP.

Czy zauważyli Państwo znaki $ w odwołaniu $A$1:$C$4? Podczas kopiowania funkcji VLOOKUP w dół kolumny chcą Państwo, aby wartość wyszukiwania się zmieniała (A10, A11, A12...), ale aby tabela pozostała w tym samym miejscu.

Odwołania bezwzględne ze znakami dolara blokują położenie tabeli. Bez nich kopiowanie w dół przesunęłoby tabelę poza dane i spowodowało błędy. Należy zablokować table_array, a lookup_value pozostawić jako odwołanie względne.

Tabela z danymi często znajduje się na innej karcie. Aby odwołać się do zakresu w arkuszu o nazwie Products, należy umieścić nazwę arkusza i wykrzyknik przed zakresem.

Jeśli nazwa arkusza zawiera spacje, należy ująć ją w apostrofy, na przykład 'Price List'!A:C. Wyszukiwanie działa dokładnie tak samo — funkcja po prostu odczytuje dane z innej karty.

Znają już Państwo podstawy funkcji VLOOKUP:

• Przeszukuje pierwszą kolumnę w dół zakresu table_array
• Przyjmuje cztery argumenty: lookup_value, table_array, col_index_num oraz range_lookup
• Wartość col_index_num jest liczona od lewej krawędzi zakresu
• W większości przypadków należy używać wartości FALSE, aby uzyskać dopasowanie dokładne
• Tabelę należy zablokować za pomocą $, aby nie przesuwała się podczas kopiowania w dół

W następnej części poznają Państwo różnicę między dopasowaniem dokładnym a przybliżonym.