Funkcje Wyszukaj w Excelu (V/H/X Lookup)

Każdy, kto sporządza raporty, analizuje czy wizualizuje dane spotyka się z problemem rozproszenia informacji. Czasem mamy różne raporty z różnych systemów czy modułów. Czasem nasze dane „żyją” sobie w oddzielnych miejscach (zakładkach), bo tak wolimy lub tak musi być z jakiegoś powodu. Nie zmienia to jednak faktu, że aby zobaczyć większy obraz i wyciągnąć wnioski będziemy musieli  bardzo często nasze dane w jakiś sposób połączyć. I dopiero wtedy np. odpalić tabelę przestawną czy zrobić inne podsumowanie. Czasem też wystarczy przeorganizować nasze raporty, trackery, aby mieć wszystko w jednym miejscu. Ale nie raz natrafimy na ograniczenia, które nas w tym zatrzymają. Lub będzie to zbyt czasochłonne. W takim przypadku z pomocą przychodzą funkcje Wyszukaj w Excelu. Ze starszych mamy do dyspozycji Wyszukaj Pionowo oraz Poziomo, ale jest też nowa gwiazda, czyli Wyszukaj X 😉 W poniższym artykule pokażę na czym polegają, czym się różnią, a także jakie mają ograniczenia. Zapraszam!

Po co te lookupy? Dlaczego jest ich tak wiele?

Po pierwsze, warto nadmienić pewne historyczne tło. Wyjaśnia to także dlaczego mamy różne funkcje Wyszukaj w Excelu i dlaczego warto na przyszłość przestawić się na ich najnowszą odsłonę czyli Xlookup. Chociaż trzeba nadmienić też, że to najmłodsze dzieło twórców Microsoftu i jeśli macie starszą wersję programu, to po prostu jej tam nie znajdziecie. Ale od początku…Tak przy okazji bedę troche zamiennie używał angielskie i polskie odpowiedniki. Dla przypomnienia Vlookup to Wyszukaj Pionowo, Hlookup to Wyszukaj Poziomo a X to…X 😉

Vlookup (Wyszukaj Pionowo) jest z Excelem ponoć od początku czyli od lat 80-tych…o tak długo 😀 Ma prawie tyle samo lat co ja 😉 Z uwagi na swoją specyfikę użycia Vlookup i Hlookup stanowią tak naprawdę parę uzupełniających się funkcji. Wyszkaj Pionowo koncentruje się na porównaniu kolumn a Poziomo na wierszach. Patrząc na różne szkolenia i praktykę powiedziałbym, że to co zmieniło i zmienia życie wielu osobom to głównie Vlookup. Jest dużo częstszy w użyciu. Z uwagi na przypuszczalne ograniczenia Microsoft wprowadził dwie osobne funkcje. Nadszedł jednak rok 2019 i nastała mała rewolucja czyli XLookup (X.Wyszukaj). Po pierwsze, nowa funkcja łączy możliwości Vlookup i Hlookup. Nie ma bowiem już potrzeby stosowania dwóch oddzielnych formuł. Nowa obsługuje tak kolumny jak i wiersze. Poza tym eliminuje niektóre ograniczenia poprzednich formuł i dodaje nowe rzeczy, o czym nieco później.

Poniżej opisuje wszystkie trzy funkcje z rodziny Wyszukaj (dla ścisłości jest też „goły” Lookup ale jego opszuczam), ale jeśli macie office 365 lub Excel po 2019 roku to polecam przejście na Xlookup. Najprawdopodobniej to tylko kwestia czasu, kiedy nowy król zatriumfuje na dobre 😀 Panie i Panowie prześledźmy zatem funkcje Wyszukaj w Excelu i zobaczmy, jak przydatne to narzędzia są 😀

Uwagi techniczno-organizacyjne 😊

Poniżej załączam plik Excel z przykładami z tego artykułu, jeśli chcecie na bieżąco poćwiczyć. Nazwy na zrzutach ekranu są po polsku, ale staram się poniżej podawać też angielskie odpowiedniki. U góry zamieszczam również dokładnie użytą formułę. Uważajcie również na separatory wewnątrz funkcji. Moje ustawienia regionalne mogą być inne niż u was, a więc może to być przecinek zamiast średnika 😊 Poniższe opisy nie wyczerpują też w 100 % tego, jak formuły działają, ale skupiam się na najważniejszych aspektach i porównaniu.

A przy okazji zachęcam też do innych moich wpisów nt. przydatnych funkcji w analizie danych. Znajdziecie je pod tymi linkami: 10 przydatnych funkcji oraz 9 przydatnych funkcji w przypadku analizy daty i czasu. A i przy okazji zapraszam również na kanał na YouTube.

Wyszukaj.Pionowo czyli VlookUP

Zacznijmy od dotychczasowej gwiazdy, czyli Wyszukaj.Pionowo, która jest nieoceniona. To coś, co zmienia życie 😀 Chociaż jak wszystko ma także swoje ograniczenia. Zresztą wszystkie funkcje Wyszukaj w Excelu jakieś mają 😉

Żeby zrozumieć w skrócie, o co w tym wszystko chodzi, popatrzcie na poniższe tabele. Ta po lewej zawiera pewne dane (imię i miasto), ale potrzebujemy także daty urodzenia, co znajduje się w tabeli po prawej. Za pomocą funkcji Vlookup można bardzo szybko „dociągnąć” brakujące informacje. Ale tylko wówczas, gdy mamy wspólną część, tj. kolumnę, która pozwoli połączyć oba zbiory. W tym przypadku taki wspólny mianownik to kolumna z imieniem. Oczywiście poniższe tabelki maja tylko po kilka wierszy, ale jeśli mamy do czynienia z setkami czy tysiącami wierszy, to Vlookup uratuje nam życie 🙂 Poza tym, jak widać na przykładzie, imiona nie muszą wystąpić w tej samej kolejności. Oczywiście obie tabelki mogą być w różnych zakładkach, a nawet w oddzielnych plikach. Chociaż w tym drugim przypadku uważałbym, gdyż łatwo o tym zapomnieć i np. usunąć coś, skąd czerpaliśmy jakieś dane. Wtedy lepiej skopiować takie informacje do jednego pliku.

Powyższą składnię można opisać w taki oto sposób. Weź imię z kolumny B, następnie szukaj go w kolumnie G i jeśli znajdziesz to zwróć informację z kolumny H z tego samego rzędu (druga kolumna licząc od G – stąd w formule trzeci argument to 2). Weź pod uwagę tylko dokładne dopasowania (w składni odpowiada temu liczba 0 na końcu funkcji).

Jedne z popularnych ograniczeń dla Wyszkuaj.Pionowo

Jak wszystko, ta formuła ma też ograniczenia. Jedne z popularnych to, że kolumna po której łączymy (tu imię) musi być w określonej lokalizacji. A bardziej po polsku? Otóż w tabelce po prawej stronie Data Urodzenia, czyli to co chcemy dostać, musi być na prawo od kolumny po której szukamy czyli Imię. Może to być dowolna odległość (kolumna H, I, J itd.) ale musi być po prawej stronie.

Tu też pewna specyfika dotycząca liczenia tej odległości. W moim przypadku drugi argument funkcji to zakres w kolumnach G:H. Czyli szukam imienia w kolumnie G i w razie znalezienia zwracam wartość z kolejnej kolumny czyli H. Odległość pomiędzy kolumnami to 2 (G to 1, H to 2), a więc taką wartość wstawiłem jako trzeci argument. Gorzej, jeśli te odleglości są większe i trzeba liczyć i uważać, na to co zwracamy, czy to będzie np. 10 czy 11 kolumn na prawo.

Kolejna rzecz to unikalne wartości. Wyszkuaj.Pionowo (ahhh wolę angielską nazwę 😉) działa najbardziej sensownie, jeśli kolumna w tabelce, z której coś dociągamy (tu tabela po prawej z datą urodzin) ma unikalne wartości. Jak widać powyżej kolumna G zawiera imiona, które się nie powtarzają. No właśnie, a co się stanie, jakby nie były unikalne? Coż…Vlookup zwróci pierwszą wartość, co widać w lekko zmodyfikowanym przykładzie poniżej. Mamy dwóch Janków, a data została wzięta dla pierwszego od góry. W tej sytuacji lepiej, aby imiona były wraz z nazwiskami, co znacznie ograniczyłoby takie ryzyko. W praktyce najlepsze są unikalne kody, numery lub tp.

Wszystkie funkcje Wyszukaj w Excelu mają te ograniczenia, chociaż XLookup wprowadza pewną elastyczność, ale o tym poniżej.

Wyszukaj.Poziomo czyli HlookUp

Powyższa funkcja ma też odmianę Wyszukaj.Poziomo czyli HlookUp. Działa analogicznie, ale przy szukaniu i zwracaniu wartości patrzymy nie na kolumny, ale na wiersze.  W poniższym przykładzie mamy dwie tabele. W tej powyżej jest trochę więcej informacji. Natomiast w tabelce poniżej chcemy „wyciągnąc” tylko wartości za pierwszy kwartał i tylko dla dwóch owoców. I w tej drugiej tabeli poniżej zastosowałem fromułę Wyszukaj.Poziomo.

Składnia również jest bardzo podobna do Vlookup, ale musimy myśleć „poziomo” 😉

Najpierw wybieramy komórkę z szukaną wartością (tu A12 czyli nazwa owocu). Następnie wybieramy zakres szukania i tu jest ważna różnica w stosunku do Vlookup. Nie podajemy kolumny  (np. A, B itd.), tylko numery wierszy lub ich zakres. A więc informuję Excela, żeby poruszał się w wierszach od 2-go do 3-go (stąd 2:3 w formule). Tam są bowiem informacje nt. szukanej wartości dla typu owocu i jego sprzedaży. Następnie w trzecim argumencie fromuły informuję, że w razie znalezienia szukanej wartości (tu nazwa owocu), chcę zwrócić wartość z drugiego wiersza zakresu. Ostatni argument to 0 czyli szukamy dokładnych dopasowań.

I tu uwaga, bo można się troche zakręcić, jeśli chodzi o wiersze i z którego „dociągamy” informacje. Fizycznie sprzedaż dla owoców za pierwszy kwartał znajduje się w trzecim wierszu arkuszu. Natomiast nazwy owoców, po których szukam są w wierszu drugim. Stąd w formule w drugim argumencie mamy „2:3”. I w takim zakresie Excel operuje. Jeśli coś znajdzie w wierszu drugim to zwraca informację z wiersza trzeciego. Skąd to wie? Ponieważ w koljenym argumencie funkcji jest podane „2”. Ale chodzi o 2-gi wiersz z szukanego zakresu. A więc fizycznie to trzeci wiersz w arkuszu, ale drugi licząc w zakrescie 2:3. Jeśli zakres byłby większy (np: 2:4), to można by go prosić o zwrócenie wartości z kolejnego wiersza poniżej itd.

Ograniczenia HlookUp

Także tu mamy parę rzeczy, na które musimy uważać. I są one bardzo podobne do tych opisanych przy okazji Vlookupa. Ale oczywiscie znów patrzymy „poziomo” 😉 Patrzymy zatem na unikalność wartości w szukanym zakresie. Jeśli będzie się coś powtarzać (w przykładzie powyżej to nazwy owoców w drugim wierszu), to wówczas formuła zwróci sprzedaż dla pierwszej od lewej. Ponadto, tak jak w przypadku Vlookupa patrzyliśmy na wartości tylko na prawo od przeszukiwanej kolumny, tak i tu patrzymy tylko w jednym kierunku tj. w dół. W powyższym przyładzie mogę zwracać wartości dla wierszy poniżej tego z nazwami owoców.

X. Wyszukaj czyli XlookUp

I jak się sprawują funkcje Wyszukaj w Excelu dotychczas? Poczekajcie jeszcze na więcej 😀 Przechodzimy bowiem do gwiazdy, któa teoretycznie jest najbardziej skomplikowana. Jeśli jednak zrozumiecie powyższe starsze siostry, to w miarę szybko poznacie logikę działania także tej najmłodszej.

Po pierwsze, świetna rzecz to to, że X. Wyszukaj działa poziomo (wiersze) i pionowo (kolumny). A więc na wstępie nie mamy rozkminy, co należy wybrać. Mamy pełną elastyczność. Wróćmy zatem na chwilę do poprzedniego przykładu z Vlookup i zbudujmy formułę z X. Wyszukaj, która będzie dawała taki sami rezultat. Wyglądałaby następująco:

A więc w tym wypadku przepis na formułę wyglądałby tak: biorę wartość w komórce B3 (tu imię w pierwszej tabeli) i przeszukuje kolumnę G (imiona w drugiej tabeli) a następnie jeśli znajdę pasującą wartość (imię) to zwracam/kopiuję informację z kolumny H (data urodzenia w drugiej tabeli).

Tu warto zaznaczyć pewne różnice w stosunku do Vlookup. Pierwszy argument jest taki sam. Później jednak nie potrzebujemy (wręcz nie możemy) zaznaczyć zakresu kolumn z szukaną i zwracaną wartością (np. kolumny G do H). Zaznaczamy natomiast tylko kolumnę, gdzie spodziewamy się naszych wartości (imion), a więc w tym przypadku to kolumna G. To z kolei implikuje zmianę także w 3-im argumencie funkcji. Nie musimy liczyć i mówić Excelowi czy ma zwrócić 2-gą…czy 5-ą wartość na prawo od przeszukiwanej kolumny. Zaznaczamy natomiast tylko tą kolumnę z której chcemy coś „dociągnąć” (tu kolumna H czyli data urodzenia).

Xlookup działa również poziomo

Analogicznie możemy zastosować X.Wyszukaj do drugiego powyższego przykładu z Wyszukaj Poziomo. Ta sama funkcja, tylko przeszukujemy wiersze. Jedyna różnice to taka że 2-gi i 3-i argument odnoszą się do wierszy. Można je zapisać jak poniżej (2:2 oznacza cały drugi wiersz). Można też zaznaczyć myszką dokładnie wybrane komórki, wtedy zapis będzie troche inny, ale działanie takie samo.

W powyższym przykładzie mówimy zatem Excelowi, żeby zrobił tak: weź wartość z komórki A12 (nazwa owocu) i szukaj go w 2-gim wierszu (2:2). Jeśli znajdziesz odpowiednik to zwróć wartość z 3-go wiersza (3:3).

Elastyczność X.Wyszukaj

Powyższe zmiany w budowaniu funkcji X.Wyszukaj pozwalają na dodatkową elastczność względem dotychczasowych formuł. Pamiętacie pewnie problem, że zwracane wartości musiały być na prawo (Vlookup) albo poniżej (Hlookup) przeszukiwanej kolmny/wiersza? Coż….tu tego nie ma 🙂 Mamy swobodę, gdzie znajdują się kolumny/wiersze z których zwaracamy szukane informacje.

W scenariuszu poniżej mamy mała modyfikację poprzedniego przykładu. Kolumna z datą urodzenia znajduje się tym razem na lewo od kolumny, którą przeszukujemy (imię). I wsystko działa 🙂 Po prostu w 3-im argumencie funkcji zaznaczyłem komórki z kolumny G. Funkcja Xlookup ogarnia to bez problemu 🙂 Analogicznie mamy do czynienia z wierszami.

X Lookup rzeczy dodatkowe 😊

Ale to nie wszystko 😊 Twórcy Excela zadbali o kilka dodatkowych funkcji niedostępnych w poprzednich wersjach lookupa.  A więc funkcje Wyszkuaj w Excelu potrafią jeszcze więcej 🙂 Jedna z super fajnych opcji to zwracanie za jednym razem nie tylko wartości z jednej kolumny/wiersza ale z kilku. Czyli że jak…? 😉 Łatwiej pokazać niż opisać.

Spójrzcie poniżej. Do poprzedniego przykładu z imionami i datami urodzin dodałem jeszcze jedną kolumnę (wyniki testu).  I teraz najlepsze…Formuła nadal obecna jest tylko w kolumnie D. Ale z uwagi na wspomniany mechanizm dostajemy również informacje w kolumnie E nt. wyników testow (pochodzące z kolumny J w drugiej tabeli).

A za to odpowiada malutka zmiana w funkcji w 3-im argumencie. W tym przypadku dodałem kolumnę J czyli mamy zakares I:J (lub bardziej precyzyjnie jak tu czyli I3:J8),  a więc mówimy Excelowi, aby pobrał informację z tych dwóch kolumn. Wówczas formuła zwraca obie informacje (urodziny i wynik testu) w kolumnie D i E. Oczywiście musimy zostawić kolumnę E jako pustą, aby zrobić odpowiednie miejsce. Inaczej formuła pokaże błąd 😉

Dla ułatwienia formuła pojawia się także automatycznie w kolumnie E. Ma ona tylko nieco jaśniejszy kolor, co wskazuje, że sama formuła została napisana w innej komórce (w kolumnie D)

X LookUp dodatki w opcjonalnych argumentach

Gratisy, gratisy, kto ich nie lubi? 🙂 Także tu ekpia z Microsoftu nie zawiodła i funkcje Wyszukaj w Excelu mają coś do zaoferowania. W przypadku X.Wyszukaj mamy całkeim sporo dodatkowych i opcjonalnych argumentów. Nię będę tu opisywać wszystkiego, ale skupię się na trzeach ciekawszych opcjach.

Pierwsza to kontrola nad informacją, którą wyświetlamy w sytuacji, kiedy funkcja nie znajdzie nic do zwrócenia. Standardowo mamy podobny mechanizm jak w Wyszukaj.Pionowo czy Poziomo, czyli mało symptayczny znak jak #N/D

X.Wyszukaj umożliwia nam jednak ustawienie jakiegoś komunikatu.

Odpowiada za to pierwszy z opcjonalnych argumentów. Wpisujemy po prostu tekst w cudzysłowie i tyle 😊

Kolejna ciekawa i innowacyjna opcja to możliwość szukania po tzw. wild card czyli tylko fragmencie tekstu. Standardowo Vlookup i Hlookup szukały według całej zawartości komórki i nawet niewielka zmiana powodowała, że formuła nic nie znajdowała. Tu mamy większą swobodę. Powiedzmy, że na naszej liście ktoś wstawił po jednej stronie pełne imię Jan, ale w szukanej liście mamy zdrobnienie czyli Janek. Standardowo formuła zwróci błąd.

Można jednak zastosować mechanizm, który powie Excelowi, aby szukał wszystkiego zaczynającego się od Jan. Musimy wówczas dodać gwiazdkę w kolumnie B plus wprowadzić do formuły dodatkowy argument „2”, który uaktywnia takie szukanie.

Tu jeszcze taki techniczny niuans. Za powyższym mechanizm odpowiada 5-y argument funkcji. Pierwsze 3 są wymagane, ale kolejne 3 (poniżej zaznaczone w kwadratowym nawiasie) są opcjonalne. Jednakże, jeśli zdecydujemy się na jego wykorzystanie to poprzedni opcjonlany, czyli 4-y argument powinien pozostać pusty, jak poniżej (dwa średniki i nic między nimi).

X.Wyszukaj – wybierz kierunek przeszukiwania

I na koniec jeszcze jedna z dodatkowych opcji. W starszych funkcjach Wyszukaj w Excelu musieliśmy pamiętać, aby w tabeli z której dociągamy informacje, nasza kolumna-klucz po której się łączymy (tu Imię) była unikalna. Tu w sumie jest podobnie, ale mamy pewną dodatkową rzecz do konfiguracji. Jeśli wiemy, że te wartości nie są unikalne, możemy powiedzieć Excelowi, aby sprawdzał je od góry lub od dołu. W ten sposób może zwrócić dwie różne wartości i mamy nad tym kontrolę.

Decyduje o tym ostatni (6-y) argument funkcji. Wstawiając „1” mówimy Excelowi, aby szukał od góry.

Zmieniając zaś argument na „-1” mówimy Excelowi, aby szukał od dołu.

I to byłoby tyle na dziś 🙂 Solidna porcja informacji, która ułatwia analizę danych, jest za nami. Funkcje wyszukaj w Excelu pozdrawiają i gorąco polecają się 🙂

Pozdrawiam i ja,

Michał