M365Con 2027 – bezpłatna konferencja online · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

XLOOKUP w aplikacji Excel: składnia, przykłady i przewaga nad VLOOKUP

By · · Updated

XLOOKUP w aplikacji Excel krok po kroku: składnia, każdy argument, przykłady dopasowania dokładnego, symboli wieloznacznych i ostatniego dopasowania, wiele kolumn oraz różnice w porównaniu z funkcją VLOOKUP.

Automatically translated with AI from the original post. Read the English original

Funkcja XLOOKUP w aplikacji Excel znajduje wartość w jednym zakresie i zwraca pasujący element z innego zakresu: =XLOOKUP(lookup_value, lookup_array, return_array). Wpisz identyfikator pracownika, wskaż kolumnę z identyfikatorami, wskaż kolumnę z nazwiskami, a otrzymasz nazwisko. Ten artykuł jest przeznaczony dla użytkowników aplikacji Excel, którzy z przyzwyczajenia nadal piszą VLOOKUP, albo właśnie zobaczyli XLOOKUP w pliku kolegi. Znajdziesz tu składnię, sześć argumentów, przykłady z typowych sytuacji w pracy, porównanie z VLOOKUP oraz listę błędów, na które możesz natrafić.

Najważniejsze wnioski

  • XLOOKUP przyjmuje wartość wyszukiwania, zakres wyszukiwania i zakres zwracania: =XLOOKUP(lookup_value, lookup_array, return_array). Znajduje pierwsze dopasowanie i zwraca element z zakresu zwracania.
  • Może szukać po lewej stronie kolumny wyszukiwania i domyślnie szuka dopasowania dokładnego. Dwóch rzeczy, których funkcja VLOOKUP nie potrafi, albo nie robi domyślnie.
  • Czwarty argument, if_not_found, zastępuje błąd #N/A tekstem wybranym przez Ciebie. Bez niego nieudane wyszukiwanie zwraca #N/A.
  • match_mode 2 włącza symbole wieloznaczne, -1 i 1 znajdują następny mniejszy lub większy element, a search_mode -1 przeszukuje od ostatniego elementu do pierwszego.
  • Strona Microsoftu wymienia XLOOKUP dla Microsoft 365, 2021 i 2024, a jej uwagi dotyczące Excela 2016 i 2019 są sprzeczne, więc przed poleganiem na tej funkcji w udostępnianych plikach warto ją przetestować.

Składnia i opcje poniżej opierają się na stronie Microsoftu dotyczącej funkcji XLOOKUP. Tam, gdzie wzorca nie ma na tej stronie, tekst to zaznacza. To funkcja aplikacji Excel i ta, którą Microsoft sam proponuje jako lepszy wybór, gdy otworzysz stronę pomocy funkcji VLOOKUP.

Składnia i sześć argumentów

Pełna formuła wygląda tak:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Trzy pierwsze argumenty są wymagane. Trzy ostatnie są opcjonalne.

  • lookup_value to wartość, której szukasz, na przykład identyfikator w komórce D2. Jeśli ją pominiesz, XLOOKUP zwróci puste komórki, które znajdzie w tablicy wyszukiwania.
  • lookup_array to zakres lub tablica do przeszukania, na przykład A2:A100.
  • return_array to zakres lub tablica, z której zwracane są wyniki, na przykład B2:B100.
  • if_not_found to tekst wyświetlany, gdy brakuje prawidłowego dopasowania. Jeśli go pominiesz, zobaczysz #N/A.
  • match_mode określa sposób dopasowania. 0 to dopasowanie dokładne i wartość domyślna. -1 oznacza dopasowanie dokładne, a gdy go brak, następny mniejszy element. 1 oznacza dopasowanie dokładne lub następny większy element. 2 to dopasowanie z symbolami wieloznacznymi, gdzie *, ? i ~ mają specjalne znaczenie.
  • search_mode określa kierunek wyszukiwania. 1 przeszukuje od pierwszego elementu do ostatniego i jest wartością domyślną. -1 przeszukuje od ostatniego do pierwszego. 2 i -2 to wyszukiwanie binarne danych posortowanych rosnąco lub malejąco. Microsoft ostrzega, że nieposortowane dane w trybach binarnych zwracają nieprawidłowe wyniki.

Utrzymuj tę samą wielkość tablicy wyszukiwania i tablicy zwracanej. Jeśli tablica wyszukiwania obejmuje wiersze od 2 do 100, tablica zwracana też powinna obejmować wiersze od 2 do 100. Strona Microsoftu nie formułuje tego jako wyraźnej zasady, ale niedopasowane zakresy są najczęstszą przyczyną, dla której wyszukiwanie zwraca niewłaściwy wiersz lub błąd.

Podstawowe wyszukiwania i argument dla braku wyniku

Załóżmy, że identyfikatory pracowników znajdują się w A2:A100, a nazwiska w B2:B100. W komórce D2 wpisujesz identyfikator, który chcesz znaleźć.

  1. Kliknij komórkę, w której ma się pojawić wynik, na przykład E2.
  2. Wpisz =XLOOKUP(D2,A2:A100,B2:B100).
  3. Naciśnij Enter.

Otrzymasz nazwisko przypisane do identyfikatora. XLOOKUP zwraca element dla pierwszego znalezionego dopasowania. Jeśli identyfikatora nie ma na liście, zobaczysz #N/A.

Aby wyświetlić bardziej przyjazny komunikat, dodaj czwarty argument:

=XLOOKUP(D2,A2:A100,B2:B100,"No record found")

Teraz nieistniejący identyfikator wyświetla komunikat „No record found”. Stosuj to w arkuszach, które czytają inni. Jedno zastrzeżenie: taki komunikat może ukrywać prawdziwy problem. Jeśli każdy wiersz pokazuje „No record found”, błąd leży w zakresach, a nie w danych, więc najpierw sprawdź znany identyfikator.

Wyszukiwanie w lewo

VLOOKUP nie potrafi zwrócić wartości z kolumny znajdującej się po lewej stronie kolumny wyszukiwania. XLOOKUP potrafi, ponieważ zakresy wyszukiwania i zwracania są od siebie niezależne. Jeśli nazwiska znajdują się w kolumnie C, a szukany identyfikator w kolumnie A:

=XLOOKUP(F2,C2:C100,A2:A100)

Strona Microsoftu mówi to wprost: kolumna wyszukiwania może znajdować się po obu stronach kolumny zwracanej.

Tryby dopasowania: symbole wieloznaczne i wartości najbliższe

Dopasowania z symbolami wieloznacznymi

Ustaw match_mode na 2, aby używać symboli wieloznacznych. Gwiazdka oznacza dowolną liczbę znaków, a znak zapytania dokładnie jeden. Znak tyldy pozwala wyszukać dosłowną gwiazdkę lub znak zapytania.

=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)

Ta formuła zwraca pierwszy wpis zaczynający się od „Jan”. Jeśli pominiesz 2, Excel będzie szukał dosłownego tekstu „Jan*” i zwróci komunikat o braku wyniku. To najczęstszy błąd przy symbolach wieloznacznych. Formułuj wzorce możliwie precyzyjnie, ponieważ XLOOKUP zwraca tylko pierwsze dopasowanie, a zbyt ogólny wzorzec może trafić na coś, czego nie zamierzałeś.

Następny mniejszy lub większy element

Tryby -1 i 1 służą do skal: stawek podatkowych, progów prowizji, przedziałów cenowych. Progi umieść w A2:A6, stawki w B2:B6, a dochód wpisz w E2:

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)

Przy -1 Excel zwraca dokładne dopasowanie, a jeśli go nie ma, następny mniejszy próg. Puste miejsce przed -1 pomija argument dla braku wyniku. Przykład Microsoftu dla dopasowania przybliżonego używa 1, aby zwrócić stawkę podatku dla następnej większej wartości dochodu, więc wybierz kierunek pasujący do Twojej tabeli. W przeciwieństwie do dawnego przybliżonego VLOOKUP progów w tych trybach nie trzeba sortować. Posortowanych danych wymagają dopiero tryby wyszukiwania binarnego.

Wyszukiwanie od końca: ostatnie dopasowanie

Gdy wartość występuje wielokrotnie, XLOOKUP zwraca pierwsze napotkane dopasowanie. Aby uzyskać ostatnie, ustaw search_mode na -1, czyli szukaj od końca:

=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)

Jeśli arkusz zawiera zamówienia uporządkowane według daty, ta formuła zwróci najnowszy wpis dla klienta. Argument 0 zapewnia dokładne dopasowanie. To schemat dla pytań typu „najnowsza cena”, „ostatni kontakt” i podobnych, które przy VLOOKUP sprawiały kłopot.

Zwracanie kilku kolumn naraz

Strona Microsoftu zauważa, że w przeciwieństwie do VLOOKUP funkcja XLOOKUP może zwrócić wiele kolumn jako jedną tablicę. Poszerz zakres zwracania:

=XLOOKUP(D2,A2:A100,B2:D100)

Formuła umieszcza dopasowanie z kolumny B w komórce, a wartości z kolumn C i D rozlewa w komórkach po prawej stronie. Zostaw dla nich miejsce. Jeśli komórki po prawej stronie zawierają już dane, Excel pokaże błąd rozlewania zamiast je nadpisać. Przykład Microsoftu zwraca nazwisko pracownika i dział jedną formułą.

Wyszukiwania dwukierunkowe

Microsoft pokazuje, jak zagnieździć dwie funkcje XLOOKUP, aby znaleźć wartość na przecięciu wiersza i kolumny. Schemat ze strony Microsoftu wygląda tak:

=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

Funkcja wewnętrzna wybiera kolumnę zgodną z nagłówkiem w komórce C3. Funkcja zewnętrzna szuka następnie wartości D2 w etykietach wierszy i zwraca wartość z tej kolumny. Microsoft porównuje to do połączenia funkcji INDEX i MATCH. Jeśli masz raport z produktami w pionie i miesiącami w poziomie, to zamiennik dla bardziej skomplikowanej formuły.

Wiele kryteriów

Funkcja XLOOKUP przyjmuje jedną wartość wyszukiwania, a strona Microsoftu nie zawiera przykładu z wieloma kryteriami. Popularnym rozwiązaniem jest pozwolenie programowi Excel na jednoczesne sprawdzenie dwóch warunków. Załóżmy, że kategoria znajduje się w kolumnie A, status w kolumnie B, a cena w kolumnie C. Aby znaleźć cenę dla kategorii z komórki F2 i statusu z komórki G2:

=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")

Każde porównanie zwraca wartość PRAWDA lub FAŁSZ. Ich pomnożenie daje 1 tylko w wierszach, w których oba warunki są spełnione, a funkcja XLOOKUP szuka pierwszej jedynki. Ten wzorzec działa w aktualnych wersjach Microsoft 365, ale nie ma go na stronie Microsoftu poświęconej funkcjom, więc przetestuj go na małej próbce. Starszą alternatywą jest kolumna pomocnicza łącząca oba pola. Między wartościami umieść separator, na przykład =A2&"|"&B2, i szukaj tak połączonego tekstu.

XLOOKUP i VLOOKUP obok siebie

Strona Microsoftu poświęcona funkcji VLOOKUP podaje składnię VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) i informuje, że wartość wyszukiwania musi znajdować się w pierwszej kolumnie zakresu. Jeśli pominiesz argument range_lookup, funkcja VLOOKUP wykona dopasowanie przybliżone, które zakłada, że pierwsza kolumna jest posortowana. Ustaw go na FAŁSZ, aby uzyskać dopasowanie dokładne. Ta sama strona sugeruje wypróbowanie funkcji XLOOKUP i podaje, że działa ona w dowolnym kierunku i domyślnie zwraca dopasowania dokładne.

To samo zadanie w obu funkcjach, z identyfikatorami w kolumnie A i nazwami w kolumnie B:

  • =VLOOKUP(D2,A2:B100,2,FALSE)
  • =XLOOKUP(D2,A2:A100,B2:B100)

Różnice, które mają znaczenie w codziennej pracy:

  • Funkcja VLOOKUP odwołuje się do wyniku po numerze kolumny, więc wstawienie kolumny do tabeli zmienia znaczenie tego numeru. Funkcja XLOOKUP odwołuje się bezpośrednio do zakresu zwracanego.
  • Funkcja VLOOKUP nie potrafi szukać w lewo. Funkcja XLOOKUP potrafi.
  • Funkcja VLOOKUP domyślnie wykonuje dopasowanie przybliżone. Funkcja XLOOKUP domyślnie wykonuje dopasowanie dokładne.
  • Funkcja XLOOKUP ma wbudowany argument wartości zwracanej, gdy nie znaleziono dopasowania, oraz kierunek wyszukiwania.

Nie musisz przepisywać starych skoroszytów. Jeśli VLOOKUP działa, zostaw go. Używaj XLOOKUP w nowych formułach oraz tam, gdzie potrzebujesz wyszukiwania w lewo, ostatniego dopasowania lub kilku kolumn wynikowych.

Które wersje programu Excel obsługują XLOOKUP

Tutaj trzeba uważać. Strona Microsoftu wymienia funkcję XLOOKUP w programie Excel dla Microsoft 365 w systemach Windows i Mac, w programie Excel 2024, Excel 2021 oraz w aplikacjach na iPada, iPhone'a i Androida. Uwaga na tej stronie mówi, że funkcja nie jest dostępna w programach Excel 2016 i 2019, ale może pojawić się w skoroszytach utworzonych w nowszych wersjach. Jednocześnie w wierszu „dotyczy” wymieniono wersje 2016 i 2019. Strona jest więc sprzeczna sama ze sobą, dlatego nie traktuj programów Excel 2016 i 2019 jako bezpiecznych. Na tej stronie nie ma też programu Excel dla sieci Web.

Jeśli wyślesz skoroszyt komuś ze starszą wersją, formuła może pokazać błąd zamiast wartości. Zapytaj, z jakiej wersji korzystają odbiorcy, a w przypadku plików wysyłanych do wielu osób przetestuj je w najstarszej używanej wersji. Jeśli nie możesz tego sprawdzić, użyj w takim pliku klasycznej funkcji wyszukiwania.

Typowe błędy i problemy

  • Zapomnienie o argumencie match_mode o wartości 2 przy użyciu symboli wieloznacznych. Wyszukiwanie będzie wtedy szukać dosłownych znaków.
  • Użycie binarnego search_mode (2 lub -2) na nieposortowanych danych. Microsoft podaje, że wyniki są wtedy nieprawidłowe.
  • Zakresy wyszukiwania i zwracania o różnej długości. Upewnij się, że mają tę samą długość.
  • Ukryte spacje i liczby zapisane jako tekst. Wartości „100” i 100 są różne, więc wyszukiwanie kończy się niepowodzeniem i widzisz #N/A. Oczyść dane funkcją TRIM lub przekonwertuj liczby.
  • Ukrywanie problemów za przyjaznym komunikatem o braku wyniku. Przed poleganiem na formule przetestuj jedną znaną wartość.
  • Kierowanie tablicy zwracanej do komórek, w które wynik ma się rozlać, i pozostawianie tych komórek zajętych.
  • Odwołania do całych kolumn w bardzo dużych arkuszach. Użyj ograniczonego zakresu lub tabeli programu Excel, aby program przetwarzał mniej komórek.

Powiązane tematy do zgłębienia

  • XLOOKUP a INDEX i MATCH. Obie te kombinacje wykonują wyszukiwanie w lewo, a para INDEX i MATCH działa także w starszych wersjach programu Excel. Przeczytaj o tym, jeśli udostępniasz pliki osobom korzystającym z programu Excel 2016 lub 2019.
  • Wiele wyników. Funkcja XLOOKUP zwraca tylko pierwsze dopasowanie. Aby uzyskać wszystkie dopasowania, zamiast tego użyj funkcji FILTER.
  • XLOOKUP z tabelami programu Excel. Odwołania strukturalne sprawiają, że zakresy rosną wraz z danymi, a formuły są przez to czytelniejsze.
  • Dane z Power Query. Po załadowaniu danych do tabeli funkcja XLOOKUP może wyszukiwać w niej tak samo jak w każdej innej tabeli. Najpierw odśwież zapytanie, a potem sprawdź wyniki.
  • Excel dla sieci Web. Strona Microsoft dotycząca funkcji XLOOKUP nie wymienia tej aplikacji, dlatego przed udostępnieniem pliku sprawdź we własnym środowisku, czy funkcja działa u Twoich współpracowników.

Następny krok

Weź jedną funkcję VLOOKUP z bieżącego skoroszytu i przepisz ją jako XLOOKUP. Dodaj komunikat dla przypadku braku wyniku i porównaj oba wyniki dla kilku znanych Ci identyfikatorów. Jeśli wyniki są zgodne, masz wzorzec, który możesz powtórnie wykorzystać przy każdej nowej funkcji XLOOKUP w programie Excel.

Źródła

Entities in this article

Microsoft Excel Excel for the web

Related articles

RSS