XLOOKUP(): Najbardziej wszechstronna funkcja wyszukiwania w Excelu

Jeśli kiedykolwiek używałeś VLOOKUP() i utknąłeś, bo musiałeś szukać w lewo, XLOOKUP() rozwiązuje ten problem. To nowsza funkcja wyszukiwania w Excelu. Może zastąpić VLOOKUP(), HLOOKUP(), a nawet większość formuł INDEX() i MATCH() jedną prostą formułą.

XLOOKUP() potrafi szukać w dowolnym kierunku, zwracać więcej niż jedną kolumnę naraz i wyświetlać własny komunikat, gdy brakuje wartości. 

W tym przewodniku najpierw przejdziemy przez składnię. Następnie zbudujemy prawdziwe formuły krok po kroku, używając jednego prostego zbioru danych od początku do końca. Omówimy też dopasowania dokładne i przybliżone, zwracanie więcej niż jednej kolumny, obsługę błędów oraz porównanie XLOOKUP() z VLOOKUP() i INDEX()/MATCH(). 

Jak używać XLOOKUP() w Excelu

Zbudujmy prawdziwą formułę XLOOKUP() od zera. 

Nasz zestaw danych zawiera pięć produktów, każdy z identyfikatorem, nazwą, kategorią i ceną, w kolumnach od A do D, z nagłówkami w wierszu 1.

Zbiór danych dla XLOOKUP(). Obraz: autor.

Załóżmy, że chcesz znaleźć cenę „P104”. Przejdźmy przez to krok po kroku.

Wyszukaj pojedynczą wartość

Kliknij pustą komórkę, w której ma pojawić się wynik. Ja używam F2. I wpisz następującą formułę:

=XLOOKUP("P104", A2:A6, D2:D6)

W tej formule: 

• „P104” to lookup_value, czyli to, czego szukamy

• A2:A6 to lookup_array, zakres, w którym Excel szukał

• D2:D6 to return_array, zakres, z którego Excel pobrał odpowiedź

Excel przeszukuje kolumnę A, „Product ID”, znajduje „P104” w wierszu 5 i zwraca odpowiadającą wartość z kolumny D: 45 USD.

Zamiast wpisywać „P104” bezpośrednio, możesz odwołać się do komórki, aby zmieniać wartość wyszukiwaną bez edycji samej formuły. U nas komórka A5 zawiera „P104”, więc formuła staje się:

=XLOOKUP(A5, A2:A6, D2:D6)

Użyj XLOOKUP() w Excelu. Obraz: autor.

Teraz możesz wpisać dowolny identyfikator produktu do A5, a cena zaktualizuje się automatycznie.

Zwróć wartość z innej kolumny

W tym kroku widać prawdziwą różnicę między XLOOKUP() a VLOOKUP(). Nie jesteś skazany na zwracanie wartości tylko z kolumny na prawo od kolumny wyszukiwania.

Powiedzmy, że zamiast ceny chcesz kategorię dla „P102”. Ten sam układ, tylko wskaż return_array na kolumnę C, „Category”:

=XLOOKUP(A3, A2:A6, C2:C6)

Tutaj A3 zwraca Home. Jeśli zmienisz na A5, zaktualizuje się na Electronics. Podobnie, ponownie zmień return_array, aby pobrać nazwę produktu, cenę lub dowolną inną kolumnę — wszystko przy tym samym lookup_value i lookup_array.

XLOOKUP() zwraca wartość z innej kolumny. Obraz: autor.

W przypadku VLOOKUP() musiałbyś liczyć kolumny od kolumny wyszukiwania i za każdym razem dostosowywać numer indeksu kolumny, gdy chcesz inny wynik. W XLOOKUP() po prostu wskazujesz bezpośrednio żądaną kolumnę. 

Skopiuj formułę w dół wielu wierszy

Do tej pory wyszukiwaliśmy jedną wartość naraz. Ale większość prawdziwych arkuszy musi wyszukiwać wiele wartości jednocześnie, na przykład pełną listę identyfikatorów zamówień, z których każdy potrzebuje pasującej ceny.

Oto jak to ustawić: kolumna A, zaczynając od A2, już zawiera naszą listę identyfikatorów produktów. Chcemy, aby pasujące ceny pojawiły się obok każdego z nich, w kolumnie F.

Aby to zrobić, kliknij F2 i wpisz formułę, odwołując się do A2 jako wartości wyszukiwanej, tak:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Teraz kliknij ponownie F2, a następnie przeciągnij uchwyt wypełniania (mały kwadrat w prawym dolnym rogu komórki) w dół przez pozostałe wiersze.

Excel kopiuje formułę w dół, a każdy wiersz zwraca teraz właściwą cenę dla swojego identyfikatora produktu. Identyfikator z A2 dostaje swoją cenę w F2, z A3 w F3 i tak dalej.

Skopiuj formułę w dół do innych komórek. Obraz: autor.

Zwróć uwagę na znaki dolara wokół A2:A6 i D2:D6. To tzw. odwołanie bezwzględne. Blokuje zakres, aby nie przesuwał się podczas kopiowania formuły w dół. 

Ma to tu znaczenie, bo każdy wiersz musi przeszukać tę samą listę produktów, tylko za każdym razem z inną wartością wyszukiwaną.

Jak używać dopasowania dokładnego i przybliżonego w XLOOKUP()

Domyślnie XLOOKUP() szuka dopasowania dokładnego. Jeśli go nie znajdzie, zwróci błąd, chyba że ustawisz argument if_not_found omówiony wcześniej. 

Ale XLOOKUP() potrafi też znaleźć najbliższe dopasowanie, używając argumentu match_mode. 

Dopasowanie dokładne (domyślnie)

Nie musisz tu nic dopisywać. Każda formuła, którą do tej pory zbudowaliśmy, już używa dopasowania dokładnego, bo to domyślne zachowanie XLOOKUP().

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel szuka dokładnej wartości z A2 i zwraca wynik tylko wtedy, gdy znajdzie to precyzyjne dopasowanie gdzieś w zakresie wyszukiwania.

Jeśli chcesz to zapisać jawnie, dodaj zero jako czwarty argument:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() zwraca dopasowanie dokładne. Obraz: autor.

Widzisz spację przed 0? To pomija if_not_found, bo zostawiamy je puste, i przechodzi od razu do match_mode. Najczęściej możesz ten argument całkowicie pominąć i pozwolić Excelowi domyślnie użyć dopasowania dokładnego.

Dopasowanie przybliżone

Załóżmy teraz, że twoje dane nie mają wartości dla każdego możliwego wyszukiwania. Pomyśl o strukturze prowizji, gdzie handlowcy zarabiają różne stawki w zależności od przedziału, w który wpada ich łączna sprzedaż. Nie będziesz mieć wiersza dla każdej kwoty, tylko dla początku każdego zakresu.

Tu pomaga dopasowanie przybliżone. 

Ustaw match_mode na -1, a XLOOKUP() znajdzie największą wartość mniejszą lub równą twojej wartości wyszukiwanej.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Załóżmy, że twój zakres wyszukiwania zawiera progi sprzedaży 0 USD, 5000 USD i 10 000 USD, każdy powiązany z inną stawką prowizji. 

Przedstawiciel z 7500 USD sprzedaży nie pasuje dokładnie do żadnego wiersza. Z match_mode ustawionym na -1 XLOOKUP() znajdzie 5000 USD, największy próg, który nie przekracza 7500 USD, i zwróci stawkę prowizji z tego wiersza.

Ustaw match_mode na 1, a XLOOKUP() odwróci logikę. Znajdzie najmniejszą wartość większą lub równą twojej wartości wyszukiwanej. To przydatne np. dla przedziałów wagi przesyłki, gdzie zaokrąglasz w górę do wyższego progu.

XLOOKUP() zwraca dopasowanie przybliżone. Obraz: autor.

Uwaga: dopasowanie przybliżone zakłada, że zakres wyszukiwania jest posortowany — rosnąco dla -1, malejąco dla 1. Jeśli nie jest posortowany prawidłowo, XLOOKUP() może zwrócić błędny wynik bez zgłoszenia błędu, co jest gorsze. Dlatego zawsze podwójnie sprawdź kolejność sortowania, zanim na tym polegniesz.

Jak wyszukiwać wartości po lewej stronie za pomocą XLOOKUP()

VLOOKUP() przeszukuje tylko skrajnie lewą kolumnę zakresu i zwraca wartość z kolumny po jej prawej stronie; w drugą stronę nie zadziała. Jego trzeci argument to nie zakres, tylko numer kolumny liczony od lewej krawędzi. 

Nie da się powiedzieć mu „idź jedną kolumnę w lewo”, więc jeśli potrzebna wartość jest na lewo od kolumny wyszukiwania, utkniesz z przebudową danych lub dodawaniem pomocniczej kolumny, żeby wyszukiwanie zadziałało.

XLOOKUP() nie ma tego ograniczenia, bo wskazujesz osobne zakresy do wyszukiwania i do zwrotu.

Weźmy naszą listę produktów: identyfikator produktu jest w kolumnie A, nazwa produktu w kolumnie B. 

Załóżmy, że masz nazwę produktu i chcesz znaleźć jego identyfikator; odwrotność tego, co potrafi VLOOKUP().

W C9 wpisz Bluetooth Speaker (nazwa, która występuje na liście). W E2 wpisz:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() przeszukuje kolumnę B w poszukiwaniu „Bluetooth Speaker”, znajduje to w wierszu 5 i zwraca „P104” z kolumny A, mimo że kolumna A jest na lewo od kolumny B. 

XLOOKUP() wyszukuje wartości po lewej, w przeciwieństwie do VLOOKUP(). Obraz: autor.

Jak zwracać wiele kolumn za pomocą XLOOKUP()

XLOOKUP() może zwracać kilka kolumn jednocześnie w jednej formule, korzystając z tzw. dynamicznych tablic. W tym celu wskaż return_array na zakres zamiast pojedynczej kolumny, a XLOOKUP() rozleje cały pasujący wiersz na tyle komórek, ile potrzeba. 

Wypróbujmy to na naszej liście produktów. 

W C9 wpisuję identyfikator produktu, np. „P104”. W E2 wpisuję:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Zwróć uwagę, że return_array to tutaj B2:D6, trzy kolumny szerokości, obejmujące nazwę produktu, kategorię i cenę. Po naciśnięciu Enter XLOOKUP() znajduje „P104” w kolumnie A, po czym zwraca Bluetooth Speaker, Electronics i 45 USD jednocześnie, rozlewając się do E9, F9 i G9.

XLOOKUP() zwraca wiele kolumn jedną formułą. Obraz: autor.

To właśnie znaczy zakres rozlewu (spill range). E9 zawiera właściwą formułę, a F9 i G9 wypełniają się same, bez własnych formuł. 

Kliknij F9, a pasek formuły pokaże wyszarzoną wersję oryginalnej formuły, a nie coś wpisanego bezpośrednio tam. Spróbuj pisać w F9, gdy rozlew z E9 jest aktywny, a Excel zgłosi błąd rozlewu. 

Jak to naprawić, omówimy w sekcji Częste błędy.

Jak obsługiwać brakujące wartości w XLOOKUP()

Domyślnie XLOOKUP() wyrzuca błąd #N/A, gdy nie znajduje dopasowania. Argument if_not_found może to naprawić, wyświetlając to, co chcemy, zamiast domyślnego błędu.  

Załóżmy, że w C9 wpisuję identyfikator produktu, którego nie ma w moich danych, np. „P999”.

Zwracanie własnego komunikatu

W G2 wpisuję:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Ponieważ „P999” nie występuje w kolumnie A, XLOOKUP() całkowicie pomija błąd i zwraca „Not Found”. Zmień frazę, jak chcesz, np. „Check Product ID” albo „No Match”.

Wyświetl własny komunikat zamiast domyślnego błędu. Obraz: autor.

Zwracanie pustej komórki

Czasem chcesz pustą komórkę bez tekstu. Użyj do tego dwóch cudzysłowów bez niczego pomiędzy, tak:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Możesz tego użyć, gdy formuła jest w raporcie i zbędne „Not Found” wyglądałoby niechlujnie obok kolumny prawdziwych wartości.

Wyświetl pustą komórkę zamiast domyślnego błędu. Obraz: autor.

Pozostawienie jako rzeczywisty błąd

Jeśli całkowicie pominiesz if_not_found, XLOOKUP() wraca do #N/A. To nie zawsze jest złe. Jeśli budujesz formułę na własny użytek i chcesz natychmiast wyłapać literówkę lub brak wpisu, pojawienie się #N/A jest bardziej pomocne niż cicha pusta komórka, która ukrywa problem.

Uwaga: if_not_found wyłapuje tylko brakujące dopasowania. Jeśli twój lookup_array i return_array mają różne rozmiary, na przykład, XLOOKUP() i tak wyrzuci błąd #VALUE!, a if_not_found go nie zatrzyma. Tę konkretną sytuację omówimy niżej w sekcji Częste błędy.

Zaawansowane funkcje XLOOKUP()

Przyjrzyjmy się zaawansowanym funkcjom XLOOKUP()

Wyszukiwanie od dołu do góry

Ustaw search_mode na -1, a XLOOKUP() będzie skanować zakres od dołu do góry, zamiast z góry na dół. Użyj tego, gdy dane mają duplikaty, a ty chcesz najnowszy wpis, nie pierwszy.

Powiedzmy, że w moim zbiorze danych mam trzy zduplikowane identyfikatory klienta. W komórce E2 wprowadzam domyślną formułę, która użyje podejścia z góry na dół: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

To jest liczone od początku tabeli i wyświetla wynik z pierwszego dopasowania. 

Ale jeśli chcę szukać od dołu, użyję tej formuły: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

W tej formule -1 mówi Excelowi, by zaczął szukać od dołu zbioru danych. To zwróci 340, bo to pierwsza pasująca wartość od dołu.  

XLOOKUP() skanuje dane od dołu do góry. Obraz: autor.

Tryb wyszukiwania binarnego

Ustaw search_mode na 2, a XLOOKUP() zamiast sprawdzać wiersz po wierszu przełączy się na wyszukiwanie binarne. 

Jak to działa: 

• Najpierw patrzy na środkowy element zakresu. 

• Jeśli to nie jest dopasowanie, odrzuca połowę, gdzie wartość nie może się znajdować, potem patrzy na środek tego, co zostało. 

• Powtarza to, za każdym razem dzieląc dane na pół, aż znajdzie dopasowanie. 

• Ustaw search_mode na -2, a działa tak samo, tylko zaczyna od ostatniego elementu zamiast od pierwszego.

Tutaj w E2 wpisz:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Ta formuła zwraca zamiast tego 200, zupełnie inny wiersz.

Tryb wyszukiwania binarnego daje zły wynik. Obraz: autor.

Ponieważ wyszukiwanie binarne działa tylko wtedy, gdy lookup_array jest posortowane. Ponieważ nasza kolumna Customer ID nie jest posortowana — idzie C100, C101, C100, C102, C100 — wyszukiwanie binarne odrzuca złą połowę i zwraca błędne dopasowanie. 

Zobaczmy teraz, co się dzieje, gdy dane są rzeczywiście posortowane. 

Posortuj ten sam rejestr po Customer ID rosnąco (wszystkie trzy C100 na początku) i uruchom ponownie tę samą formułę. 

Tryb wyszukiwania binarnego działa dla posortowanych danych. Obraz: autor.

Tym razem E2 zwraca 120, zgodnie z tym, co dałoby zwykłe wyszukiwanie. Na tym polega różnica. Wyszukiwanie binarne pomija tylko dane, które może bezpiecznie wykluczyć, a to działa wyłącznie wtedy, gdy dane na starcie są posortowane. 

Zagnieżdżone formuły XLOOKUP()

Zagnieżdżaj jedną XLOOKUP() w drugiej, gdy wynik wyszukiwania ma zasilić drugie wyszukiwanie, zamiast stałego zakresu.

Załóżmy, że chcesz znaleźć cenę, ale to, z której kolumny ją pobierzesz, zależy od osobnego wyszukiwania, np. poziomu cenowego klienta.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

W tej formule wewnętrzna XLOOKUP() najpierw ustala, z jakiego zakresu kolumn pobrać dane na podstawie D9, a następnie zewnętrzna XLOOKUP() używa tego wyniku jako return_array. 

Zagnieżdżony XLOOKUP(). Obraz: autor.

XLOOKUP() vs. VLOOKUP()

Szczegółowo omówiliśmy, jak działa XLOOKUP(). Oto jego porównanie z VLOOKUP().

Warto szybko odnotować dwie rzeczy: 

• VLOOKUP() domyślnie używa dopasowania przybliżonego. Jeśli zapomnisz czwartego argumentu, nie pokaże błędu; po prostu po cichu zwróci złą wartość.

• Jeśli wstawisz kolumnę do arkusza z VLOOKUP(), formuły po cichu zaczną pobierać złą wartość. XLOOKUP() unika tego, bo odwołuje się bezpośrednio do zakresów zamiast liczyć kolumny.

To nie czyni VLOOKUP() przestarzałym. Nadal dobrze działa dla prostych, statycznych arkuszy. Ale do wszystkiego tworzonego od teraz, XLOOKUP() jest bezpieczniejszym domyślnym wyborem.

XLOOKUP() vs. INDEX() i MATCH()

Zanim pojawił się XLOOKUP(), połączenie INDEX() i MATCH() było standardowym sposobem obejścia ograniczeń VLOOKUP(). Używa dwóch funkcji zamiast jednej. 

• MATCH() znajduje pozycję twojej wartości wyszukiwanej. 

• INDEX() następnie używa tej pozycji, aby pobrać wynik skądkolwiek wskażesz: w lewo czy w prawo, bez znaczenia.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Porównaj to z wersją XLOOKUP(), której używaliśmy w całym artykule:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() działa tylko w Microsoft 365 i Excel 2021 wzwyż, jak wspomnieliśmy wcześniej. Jeśli twój skoroszyt ma otwierać się w Excel 2019 lub 2016, albo udostępniasz go osobom na starszych wersjach, INDEX() i MATCH() nadal działają wszędzie.

Jest też mniejszy przypadek. 

INDEX() i MATCH() rozdzielają „znajdź pozycję” od „zwróć wartość”, co pomaga w niektórych złożonych zagnieżdżonych formułach. Zdarza się to rzadko, więc większość czytelników tego nie potrzebuje.

Poza tymi dwoma przypadkami XLOOKUP() jest lepszym wyborem do nowego skoroszytu. 

Częste błędy XLOOKUP() i jak je naprawić

Oto szybka ściąga najczęstszych problemów i sposobów ich naprawy.

Błędy #N/A

To domyślna odpowiedź XLOOKUP(), gdy nie znajdzie dopasowania. Miałem to wcześniej, gdy mój lookup_value i lookup_array nie były zgrane, i XLOOKUP() nie miał do czego dopasować w kolumnie A.

Aby to naprawić: sprawdź, czy twój lookup_value rzeczywiście istnieje w lookup_array, zapisany i sformatowany tak samo. Jeśli brakujące dopasowania są spodziewane i normalne, użyj argumentu if_not_found, aby zastąpić #N/A czymś bardziej użytecznym, np. „Not Found” albo pustą komórką, jak omawialiśmy wcześniej. 

Nieprawidłowy zakres wyszukiwania

Dzieje się tak, gdy lookup_array i return_array nie są poprawnie wyrównane: złe wiersze, złe kolumny lub zakresy różnej wielkości. 

W zależności od wersji Excela lub Arkuszy może to się pojawić jako błąd #VALUE!, albo czasem jako #N/A z osobnym komunikatem wyjaśniającym prawdziwy problem. Nie daj się zwieść #N/A i nie zakładaj, że to brak dopasowania. 

Aby to naprawić: upewnij się, że lookup_array i return_array obejmują tę samą liczbę wierszy lub kolumn. Jeśli lookup_array to A2:A5, return_array też musi obejmować wiersze od 2 do 5, a nie 4 do 5 ani inny niepasujący zakres.

Niezgodności typów danych

VLOOKUP() może wyrzucić błąd, bo spodziewał się liczby, a dostał tekst. XLOOKUP() może trafić na podobny problem. Jeśli twój lookup_value jest liczbą, ale lookup_array przechowuje te same wartości jako tekst, albo odwrotnie, XLOOKUP() nie znajdzie dopasowania, choć na ekranie wartości wyglądają identycznie.

Aby to naprawić: sprawdź, czy lookup_value i lookup_array używają tego samego typu danych. Liczby zapisane jako tekst zwykle są wyrównane do lewej, a nie do prawej. Owiń swój lookup_value w VALUE(), by przekonwertować tekst na liczbę, albo użyj TEXT(), by zrobić odwrotnie — w zależności od kierunku niezgodności.

Błędy rozlewu (spill)

To się dzieje, gdy return_array obejmuje więcej niż jedną kolumnę, ale komórki potrzebne XLOOKUP() nie są puste. Excel potrzebuje wolnego miejsca obok komórki z formułą, by rozlać wyniki. Jeśli coś już tam jest, cała formula się nie powiedzie.

Aby to naprawić: wyczyść komórki, w których mają pojawić się wyniki, a potem wpisz formułę. Jeśli coś musi w tych komórkach pozostać, przenieś formułę XLOOKUP() w miejsce z wolną przestrzenią obok.

Na koniec 

Wiesz już, jak używać XLOOKUP() do pojedynczych wartości, wielu kolumn, brakujących danych, a nawet zagnieżdżonych wyszukiwań na dwóch zakresach. To sporo materiału.

Na przyszłość wybieraj XLOOKUP() jako domyślne w nowych skoroszytach. Jest bardziej elastyczne niż VLOOKUP(), czytelniejsze niż INDEX/MATCH() i oszczędza ci owijania każdej formuły w IFERROR().

Jeśli chcesz wejść głębiej, warto też poznać osobno INDEX() i MATCH().